Omgaan met begin‑ en einddatums in Power BI

Een onderwerp waar veel Power BI-gebruikers tegenaan lopen in Power BI is het werken met begin‑ en einddatums in een tabel. Dit komt vaak voor bij projecten, contracten of bij de “datum in dienst” en “datum uit dienst” van personeelsleden.

Waarom is dit een uitdaging?

Omdat Power BI slechts één actieve relatie kan hebben tussen de datumdimensie en een feitentabel.

Wanneer je een berekening wilt uitvoeren in Power BI die alleen de records omvat die zich tussen de begin‑ en einddatum bevinden, mag die berekening niet worden gefilterd door een relatie met één van de datumvelden. Met andere woorden, de filtercontext moet de twee datumvelden omzeilen zodat alle relevante rijen behouden blijven.

Voorbeeld: Medewerkers die in of uit dienst zijn gegaan

Stel je voor dat je een fact‑tabel Medewerkers hebt. Voor iedere medewerker staan twee datumvelden:

DatumInDienst – de eerste werkdag
DatumUitDienst – de laatste werkdag (of leeg voor lopende contracten)

Je wilt nu een rapport maken waarin je kunt zien hoeveel medewerkers op een willekeurige datum in dienst waren. Op het eerste gezicht lijkt dat simpel, maar Power BI’s standaardgedrag maakt het lastiger.

De automatische datumfilter: vriend of vijand?

Zodra je één van die datumkolommen koppelt aan een kalender‑tabel, legt Power BI automatisch een filter op. Dit filter zorgt ervoor dat elke visualisatie of maatregel alleen de rijen ziet die overeenkomen met de datum die de gebruiker in de kalender heeft geselecteerd. Voor jaarrapportages, maand‑overzichten en kwartaal‑cijfers is dit precies wat je wilt.

Maar bij een “tussen‑datums”‑vraag moet je eerst alle rijen van de feitentabel kunnen bekijken en daarna zelf bepalen of de gekozen datum tussen de twee grenzen ligt. De automatische filter blokkeert dit op twee manieren:

Enkelvoudige filterrichting
- De relatie filtert alleen vanuit de datumdimensie naar de feitentabel, waardoor de tabel al vóór je DAX‑expressie wordt beperkt tot één kant (bijv. alleen de startdatum).
Verlies van relevante rijen
- Een medewerker die op 1 jan begon en op 31 dec stopte, kan worden uitgesloten wanneer je een slicer op 15 mei gebruikt, ook al valt die datum duidelijk tussen de twee grenzen.

Het gevolg: tellingen, sommen of gemiddelden komen te laag of helemaal fout.

Eén actieve relatie = de kern van het probleem

Power BI kan dus maar één actieve relatie hebben tussen de datumdimensie en een feitentabel. Dus als één van de twee datumkolommen (bijv. DatumInDienst) als actieve relatie moet definiëren; de andere (DatumUitDienst) inactief blijft.

Omdat alleen de actieve relatie automatisch wordt gebruikt, ziet Power BI bij een gewone visualisatie alleen de kolom die via die relatie is gekoppeld. De inactieve kolom wordt genegeerd en dit is precies het gedrag dat de “tussen‑datums”‑analyse saboteert.

De oplossing: `CALCULATE` + `USERELATIONSHIP`

Met de DAX‑functie CALCULATE kun je tijdelijk een inactieve relatie activeren. Zo kun je beide datumkolommen afzonderlijk benutten zonder dat de automatische filter je resultaten vervormt.

In onderstaande video laten we stap voor stap zien hoe je dergelijke berekeningen toch kunt realiseren met DAX. In de video wordt de volgende meting gemaakt: