In bijna iedere (online) ontwerper training Power BI komt deze vraag wel aan bod: waarom gebruik je een datumtabel in Power BI? Hele terechte en begrijpelijke vraag voor iedere data analist die goed in Excel is en de stap naar Power BI maakt.
Waarom een datumtabel?
Het simpele antwoord op de vraag is: In PowerBI is het niet vereist om een datumtabel te gebruiken, maar is het wel een zeer belangrijke best practise om wel te gebruiken. Zelf gebruik ik in ieder Power BI model een datumtabel. Hieronder licht ik dit verder toe aan de hand van een aantal vragen en antwoorden.
Noot: In dit artikel bedoel ik met Power BI altijd Power BI Desktop.
Hoe kan een datumtabel in Power BI er uit zien?
Dit is een voorbeeld dat is gemaakt met de DAX taal in Power BI:
Je ziet een tabel waar de eerste kolom Datum iedere unieke datum bevat (startende vanaf 1-1-2019 tot vandaag + 365 dagen, heerlijk dynamisch). In de kolommen rechts van Datum vind je alle kenmerken van die specifieke datum, zoals het bijbehorende Jaar / Maand maar ook het weeknummer en of het een weekdag/weekenddag is.
Wat gaat er dan mis wanneer ik in Power BI geen datumtabel gebruik?
Wanneer je geen datumtabel in Power BI gebruikt, dan gebruik je veelal een kolom uit een tabel waar datums in staan (bijv. verkoopdatum). In hele simpele PowerBI modellen zal dat prima werken, omdat Power BI jouw gegevens netjes op verkoopdatum zal verdelen (bijv. per jaar of week). Let er wel op dat Power BI deze kolom verkoopdatum wel als het datatype Datum ziet, wanneer dit bijv. het datatype tekst is gaat dit natuurlijk fout.
Echter, vaak wil je meer dan dat. Zoals Year-To-Date of gegevens vergelijken met vorige maand of jaar. Juist dan heb je een datumtabel nodig.
Wat zijn dan de voordelen van een datumtabel in Power BI?
Dit zijn de belangrijkste voordelen:
- Tijd intelligentie functies gebruiken (MTD, QTD, YTD en vorige week/maand/jaar etc.)
Wanneer je een dag geen transacties hebt (zoals weekend of Kerst) dan is het voor Power BI lastig om cumulatief goed op te tellen. Door gebruik van een datumtabel en een bijbehorende YTD DAX functie los je dit goed op.
2. Filteren van meerdere tabellen tegelijk
Wanneer je meerdere tabellen in Power BI hebt waar datums in voorkomen wil je die vaak liever tegelijk kunnen filteren op datum (jaar/maand etc.). Tevens wil je deze informatie in één visualisatie zien. Goed voorbeeld hiervan is Werkelijk en Budget data. Enkel door beide actieve tabellen Werkelijk en Budget via een relatieve datumtabel te verbinden kan je in één visualisatie beide getallen naast elkaar zetten
3. Consistentie van je Power BI model
In Power BI werk je met relatieve en actieve tabellen. Je relatieve tabellen zijn je filters in je Power BI model (zoals Klant, Product, Regio etc.). Datum is daar natuurlijk net zo goed een voorbeeld van, en ook daarom maak je een aparte datumtabel om zo je actieve tabellen goed te kunnen filteren.
4. Additionele informatie (zoals weekdag/weekenddag etc.)
In een datumtabel kan je alle varianten van een datum aanmaken, zoals Q4-19 of Q4-2019.
Hoe maak ik zelf een datumtabel in Power BI?
Er zijn grofweg vier opties om een datumtabel in Power BI te maken. Optie 1 (Excel) is de simpelste maar wel het meest statisch. Optie 3 (DWH) is het meest stabiel/volwassen.
- Maak in Excel een datumtabel en importeer deze Exceltabel (geen Excel celbereik) in Power BI.
Hiermee kan je elke variatie van een datum in Excel maken, maar loop je wel het risico dat Power BI niet meer update wanneer de link naar het Excel bestand verbroken is.
2. Maak in Power BI een nieuwe DAX tabel aan (zie gele markering in bovenstaande afbeelding) en maak direct de eerste kolom aan:
Datumtabel = CALENDAR(DATE(2019;1;1);TODAY()+365)
Gebruik nu de volgende DAX functies één voor één voor iedere losse kolom:
Jaar = YEAR(Datumtabel[Datum])
Maand = MONTH(Datumtabel[Datum])
Dag = DAY(Datumtabel[Datum])
Kwartaal = “Q”&FORMAT(Datumtabel[Datum];”Q”)
Dag van de week = WEEKDAY(Datumtabel[Datum];2)
Weeknummer = WEEKNUM(Datumtabel[Datum];2)
Een nieuwe DAX tabel maak je aan via tabblad Modelling à New table.
Hiermee maak je in DAX taal efficiënt en snel een datumtabel. Tevens is je tabel dynamisch omdat de tabel tot vandaag + 365 dagen loopt (zie regel 1 met de DAX functie CALENDER).
3. Datumtabel vanuit het DataWareHouse: Indien je een DataWareHouse hebt, dan is vermoedelijk daar al een datumtabel beschikbaar. Dan heeft deze optie de voorkeur boven de andere. Deze tabel wordt doorgaans door IT onderhouden, en bevat al veel relevante kenmerken van datums (week/kwartaal etc.).
4. Maak in de Query editor een datumtabel aan. Online (YouTube) kan je hier een aantal varianten van vinden hoe je dit doet.
Zelf maak ik zo veel mogelijk gebruik van optie 2 (DAX datumtabel). Dat doe ik omdat deze relatief makkelijk is aan te passen, en weinig performance vraagt.
Alternatief: Ik werk met een fabriekskalender (4-4-5) of periode (13 perioden), hoe ga ik te werk?
Indien mogelijk laat je een tabel op het DataWareHouse toevoegen met de juiste indeling. Is dit niet mogelijk/haalbaar dan maak je deze tabel statisch in Excel en importeer je deze tabel in Power BI.
Heb je vragen over dit artikel, stuur me dan een e-mail. Heb jij ook een vraag over PowerBI? Stel hem door mij een mail te sturen: F.vandenHeuvel@FiNcompany.nl .