Een datumtabel maken in Power BI: waarom, hoe en best practices

Een datumtabel is één van de meest essentiële bouwstenen van een goed Power BI-model. Toch zien we regelmatig modellen waar deze ontbreekt, of waar de datumtabel niet goed is ingericht. Het resultaat: Time Intelligence-functies werken niet, je kunt geen periodevergleking maken, en je DAX wordt onnodig complex. In dit artikel leggen we uit waarom een datumtabel cruciaal is en tonen we drie praktische methoden om er één op te zetten.

Waarom is een datumtabel essentieel?

De kern: veel geavanceerde DAX-functies werken niet zonder een goed ingericht datumtabel. Denk aan Time Intelligence-functies zoals DATEADD, SAMEPERIODLASTYEAR, YTD, QTD en MTD. Deze functies vertrouwen erop dat je datumtabel:

• Een ononderbroken reeks datums bevat (geen gaten)
• Een 1:n-relatie heeft met je feittabel(len)
• In het model is gemarkeerd als datumtabel

Zonder datumtabel kun je ook geen schone filters op datum toepassen in slicers, en je filtercontext is onpredictabel. Een goed datumtabel is dus niet luxe, maar noodzaak.

Methode 1: CALENDAR() in DAX

Dit is de klassieke aanpak: je maakt een lege tabel in je model en vult deze met DAX. Het voordeel: volledige controle over de datum-range.

Datumtabel = CALENDAR(DATE(2020,1,1), DATE(2026,12,31))

Deze enkele regel maakt een tabel met alle datums van 1 januari 2020 tot en met 31 december 2026. De kolom heet standaard "Date".

Dit is snel en eenvoudig, maar je kunt nog geen extra kolommen toevoegen (jaar, kwartaal, maandnaam, enz.) door hier gewoon meer code toe te voegen. Je kunt ze later toevoegen als calculated columns, maar beter is Methode 3 (Power Query).

Methode 2: CALENDARAUTO() in DAX

Als je wilt dat Power BI automatisch de juiste datum-range bepaalt op basis van je data, gebruik je CALENDARAUTO():

Datumtabel = CALENDARAUTO()

Power BI scant automatisch alle datum-kolommen in je model en maakt een tabel van de eerste tot de laatste datum. Handig, maar je hebt minder controle. Als je bijvoorbeeld wilt dat je datumtabel al in 2010 begint (terwijl je data pas in 2020 start), kun je dat niet instellen.

Tip: CALENDARAUTO(12) bepaalt dat het jaar eindigt in december (nuttig als je een fiscaal jaar anders loopt).

Methode 3: Power Query (M) — de professionele aanpak

Dit is hoe je het in een echte productieomgeving doet. In Power Query kun je een volledige, schone datumtabel bouwen met alle benodigde kolommen:

let
  StartDate = #date(2020, 1, 1),
  EndDate = #date(2026, 12, 31),
  DateCount = Duration.Days(EndDate - StartDate) + 1,
  Dates = List.Dates(StartDate, DateCount, #duration(1, 0, 0, 0)),
  DateTable = Table.FromList(Dates, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
  DateColumnType = Table.TransformColumnTypes(DateTable, {{"Date", type date}}),
  AddYear = Table.AddColumn(DateColumnType, "Year", each Date.Year([Date]), type number),
  AddMonth = Table.AddColumn(AddYear, "Month", each Date.Month([Date]), type number),
  AddMonthName = Table.AddColumn(AddMonth, "MonthName", each Text.Proper(Date.MonthName([Date])), type text),
  AddQuarter = Table.AddColumn(AddMonthName, "Quarter", each "Q" & Text.From(Date.QuarterOfYear([Date])), type text),
  AddWeekNum = Table.AddColumn(AddQuarter, "WeekNum", each Date.WeekOfYear([Date]), type number),
  AddDayOfWeek = Table.AddColumn(AddWeekNum, "DayOfWeek", each Text.Proper(Date.DayOfWeekName([Date])), type text)
in
  AddDayOfWeek

Deze query maakt een schone datumtabel met kolommen voor jaar, maand, maandnaam, kwartaal, weeknummer en weekdagnaam. Dit is hoe professionals het doen: alles in Power Query, niet in DAX.

Welke kolommen moet je toevoegen?

Een goed datumtabel bevat minstens deze kolommen:

• Date: De datum zelf (uniek, geen duplicaten)
• Year: Het jaar (2020, 2021, enz.)
• Month: De maandnummer (1-12)
• MonthName: De maandnaam ("januari", "februari")
• Quarter: Het kwartaal ("Q1", "Q2", enz.)
• WeekNum: Het weeknummer (1-52/53)
• DayOfWeek: De weekdagnaam ("maandag", "dinsdag")

Optioneel voeg je ook toe:

• FiscalYear, FiscalMonth: Als je bedrijf een fiscaal jaar hanteert dat anders loopt dan het kalenderjaar
• DayOfYear: De dag van het jaar (1-365/366)
• IsWeekend: Of het weekend is (ja/nee)

De datumtabel markeren in Power BI

Dit stap is kritiek. Na het laden van je datumtabel:

1. Ga naar het Model-tabblad
2. Selecteer je datumtabel
3. Klik op Mark as date table
4. Selecteer de kolom met de datums

Nu weet Power BI dat dit je datumtabel is, en kunnen Time Intelligence-functies werken. Je ziet in het model ook dat de datumtabel een klein kalendericoontje krijgt.

Best practices

Een paar gouden regels:

• Geen gaten: Je datumtabel moet een ononderbroken reeks zijn. Geen gaten, geen weekenden overslaan (tenzij je bewust weekends uitsluit).
• Eén datumtabel per model: Gebruik één datumtabel als "master". Zorg dat alle feittabellen hun datekolom daaraan koppelen via een 1:n-relatie.
• Relatie naar feittabel: De relatie moet van Date (datumtabel) naar je transactiedatum (feittabel) gaan, niet omgekeerd.
• MonthName sorteren: Als je een kolom "MonthName" hebt, zorg dat deze op "Month" wordt gesorteerd, niet alfabetisch. Anders krijg je "april" voor "augustus".
• Gebruik Power Query: Voor kolommen zoals maandnaam, weekdagnaam enz., voeg je die best in Power Query toe, niet later als calculated column in DAX.

Time Intelligence en Measure Patronen

Met een goed datumtabel kun je geavanceerde Time Intelligence-maten bouwen. Patronen zoals YTD (Year-To-Date), QTD (Quarter-To-Date) en MTD (Month-To-Date) vereisen een datumtabel. Check onze Measure Patronen bibliotheek voor kant-en-klare voorbeelden van deze maten.

Samenvatting

Een datumtabel is geen luxe, maar noodzaak in Power BI. Kies voor Methode 1 (CALENDAR) voor snelheid, Methode 2 (CALENDARAUTO) voor automatisering, of Methode 3 (Power Query) voor professionele controle. Voeg de juiste kolommen toe, markeer de tabel in het model, en zorg voor een ononderbroken datumreeks. Daarmee heb je de basis voor krachtige Time Intelligence en schone, efficiënte DAX.