Tijdintelligentie, YTD-analyses en datumtabel
Je begrijpt time intelligence functies en bouwt een datumtabel.
In de video maakt Frank de datumtabel met een lege query in Power Query. Hoe je de code gebruikt en hoe het ook met DAX kan, lees je hieronder.
Je kunt YTD-, MTD- en vergelijkingen met vorig jaar maken met behulp van een datumtabel in Power BI.
Stel: je wilt weten hoe je omzet zich ontwikkelt in de loop van het jaar. Of hoeveel beter je presteert dan vorig jaar. Klinkt eenvoudig, maar zonder een goede datumtabel lukt het je niet. En wat je dan krijgt, is dit:
- Je YTD lijkt goed… maar stopt op 30 juni
- Je omzet “vorig jaar” telt alles van januari t/m december, ook als je nu filtert op april
- Je maandvergelijking geeft nul terug omdat de datum niet gevonden wordt
Dit gebeurt verrassend vaak. Tijdintelligentie in Power BI werkt namelijk alleen als je model aan een paar strenge eisen voldoet.
Waarom een datumtabel belangrijk is
Power BI heeft tijdsfuncties als TOTALYTD(), SAMEPERIODLASTYEAR().Maar die werken alleen als je:
- Een aparte datumtabel hebt
datumtabel
- Een relatie legt tussen die datumtabel en je feitentabel (bijv. Verkoopregels[Datum])
- Alleen datums uit de datumtabel gebruikt in visuals en slicers
Gebruik je de datumkolom uit je transactiedata? Dan werken die functies niet goed. En krijg je verkeerde totalen of lege visuals.
niet goed
Waarom kun je niet gewoon de datumkolom uit je feitentabel gebruiken voor tijdintelligentie?
Hoe maak je een datumtabel
Gebruik net als in de video een lege query in Power Query met [datumtabel-powerquery.txt](/resources/datumtabel-powerquery.txt).
- 1Kies in Power BI Desktop Gegevens ophalen > Lege query.
- 2Open de Geavanceerde editor en plak de code uit het bestand.
- 3Geef de query de naam Kalender en kies Sluiten en toepassen.
De code bevat al kolommen voor jaar, maand en kwartaal. De datumkolom heet Date; hernoem die naar Datum voor de voorbeelden hieronder.
Als alternatief kun je de datumtabel met DAX maken, met CALENDAR of CALENDARAUTO:
Datum = CALENDAR( DATE(2020, 1, 1), DATE(2025, 12, 31) )Of laat Power BI het bereik automatisch bepalen op basis van je data:
Datum = CALENDARAUTO()Voeg daarna kolommen toe voor jaar, maand, kwartaal etc.:
Jaar = YEAR( Datum[Date] )
Maandnummer = MONTH( Datum[Date] )
Maandnaam = FORMAT( Datum[Date], "MMMM" )
Kwartaal = "Q" & FORMAT( Datum[Date], "Q" )CALENDARAUTO() scant alle datumkolommen in je model en maakt een tabel van 1 januari van het vroegste jaar tot 31 december van het laatste jaar. Handig, maar controleer altijd of het bereik klopt.
Een datumtabel handmatig in Excel bouwen is foutgevoelig en beperkt in schaalbaarheid. Gebruik de Power Query-code hierboven of kies de DAX-route.
Tijdintelligente measures maken
Voor deze voorbeelden heb je een datumtabel nodig genaamd "Kalender" met kolom Datum, die gekoppeld is aan Verkoopregels[Datum].
Verder lezen
Laat je naam en mailadres achter, dan lees je de rest
Je krijgt meteen de hele les, en alle andere lessen blijven daarna ook open. Geen wachtwoord, geen bevestigingsmail.
Year-To-Date (YTD)
OmzetYTD = TOTALYTD( [TotaleOmzet], Kalender[Datum])
Laat de omzet zien vanaf 1 januari tot het huidige filtermoment.
Last Year (vorig jaar, zelfde periode)
Gebruik dit om prestaties over jaren heen te vergelijken.
Groei t.o.v. vorig jaar
- OmzetGroei = [OmzetYTD] - [OmzetVorigJaar]
- OmzetGroeiProcent = DIVIDE([OmzetGroei], [OmzetVorigJaar])
Zo zie je de absolute én procentuele groei.
Month-To-Date / Quarter-To-Date
- OmzetMTD = TOTALMTD([TotaleOmzet], Kalender[Datum])
- OmzetQTD = TOTALQTD([TotaleOmzet], Kalender[Datum])
Gebruik je voor maand- of kwartaalrapportages.
Je wilt de omzet Year-to-Date berekenen. Welke DAX-functie gebruik je?
Je hebt de measure OmzetVorigJaar = CALCULATE( [TotaleOmzet], SAMEPERIODLASTYEAR( Datum[Datum] ) ). In januari 2024 filter je op maart. Welke periode berekent deze measure?
DATEADD, het flexibele alternatief
SAMEPERIODLASTYEAR verschuift altijd precies één jaar. Maar wat als je per kwartaal of per maand wilt vergelijken? Daarvoor gebruik je DATEADD:
OmzetVorigKwartaal =
CALCULATE( [TotaleOmzet], DATEADD( Datum[Datum], -1, QUARTER ) )OmzetVorigeMaand =
CALCULATE( [TotaleOmzet], DATEADD( Datum[Datum], -1, MONTH ) )DATEADD accepteert drie parameters: de datumkolom, het aantal periodes (negatief = terug in de tijd), en het type (DAY, MONTH, QUARTER, YEAR).
SAMEPERIODLASTYEAR(Datum[Datum]) is equivalent aan DATEADD(Datum[Datum], -1, YEAR). DATEADD is flexibeler, maar SAMEPERIODLASTYEAR is leesbaarder voor het meestgebruikte scenario.
Wat gaat hier vaak mis?
Mensen denken dat als ze gewoon een datumkolom in hun transactietabel hebben, alles werkt. Maar dat is niet zo. Tijdfuncties zoals SAMEPERIODLASTYEAR() hebben een complete, doorlopende kalender nodig zonder gaten (ontbrekende dagen).
Als je alleen datums hebt waarop iets verkocht is, en je verkoopt niet elke dag, ontbreken er datums. En dan werken je tijdsanalyses niet.
ontbreken er datums
Mini-verdieping: Mark as Date Table
Als je een datumtabel hebt gemaakt, markeer die dan ook als zodanig:
markeer
- Selecteer je datumtabel in Power BI
- Ga naar Modellering > Mark as date table
- Kies de kolom met volledige datums (bijv. Datum)
Dit vertelt Power BI: “Gebruik deze tabel voor tijdsanalyses”
Tips & checks uit de praktijk:
- Gebruik altijd een aparte datumtabel
- Leg één duidelijke relatie tussen die tabel en je feiten (bijv. Verkoopregels[Datum])
- Voeg Jaar, Maand, Kwartaal toe als kolommen (niet als measures)
- Gebruik DIVIDE() voor procentuele groei
- Test je measures met slicers op datum, jaar en maand
Praktijkvoorbeeld:
Je hebt een rapport met verkoopgegevens. Je wilt:
- Omzet YTD tonen
- Omzet vorig jaar tonen (zelfde periode)
- Verschil en % groei tonen
- In de slicer filteren op Jaar en Maand
Je maakt de measures:
- OmzetYTD = TOTALYTD([TotaleOmzet], Kalender[Datum])
- OmzetVorigJaar = CALCULATE([TotaleOmzet], SAMEPERIODLASTYEAR(Kalender[Datum]))
In je slicers gebruik je Kalender[Jaar] en Kalender[Maand]. Alles werkt, automatisch.
Veelgemaakte fout:
Tijdanalyses maken zonder datumtabel. Dan krijg je lege visuals of verkeerde vergelijkingen. Power BI waarschuwt niet, maar je cijfers zijn onbetrouwbaar.
Hoe je het voorkomt:
Maak altijd een complete datumtabel. Koppel die aan je feiten. En gebruik alleen die tabel in je tijdsmetingen en filters.
Opdracht:
- Maak een datumtabel met de Power Query Code in de bijlageMarkeer de tabel als “Date Table”Leg een relatie met Verkoopregels[Verkoopdatum]
Maak een datumtabel met de Power Query Code in de bijlage
- Markeer de tabel als “Date Table”
- Leg een relatie met Verkoopregels[Verkoopdatum]
- Bouw deze DAX-measures:OmzetYTDOmzet vorig jaarGroei t.o.v. vorig jaar (absoluut en procentueel)
Bouw deze DAX-measures:
- OmzetYTD
- Omzet vorig jaar
- Groei t.o.v. vorig jaar (absoluut en procentueel)
- Test ze in een visual en voeg slicers toe op Jaar en Maand
Reflectievraag:
Gebruik jij nog dashboards zonder een echte datumtabel? Hoe weet je dan zeker dat je tijdsanalyses kloppen?
Wil je je voortgang bewaren, de oefenbestanden erbij en je inschrijven voor de gratis avond, maak dan een gratis account aan. Je hoeft alleen op een link in je mail te klikken.
Maak een gratis account