Blog
Hoe maak je een datumtabel in Power BI met DAX?
- 23 februari 2023
- Frederik Vandeputte - Founder RedMojo
Als je met Power BI aan de slag gaat, zal je merken dat je al snel wilt kunnen rapporteren over tijdsperiodes heen. Zo wil je misschien het aantal ziektedagen vergelijken met dezelfde periode vorig jaar. Of een Year-To-Date cijfer berekenen van het aantal starters en dit vergelijken met vorig jaar.
Om bovenstaande vragen te kunnen beantwoorden, heb je een datumtabel nodig. In deze blogpost toon ik je graag hoe je met slechts 3 eenvoudige stappen een datumtabel maakt in Power BI met DAX.
Bij RedMojo werken we liefst met Power BI in het Engels. Dit betekent onder andere dat de ribbon, menu’s, foutmeldingen, … allemaal in het Engels worden weergegeven, wat hulp zoeken op internet een stuk gemakkelijker maakt.
Stap 1 – Datumtabel maken
In Power BI kan je je eigen datumtabel (of kalenderdimensie) maken. Eén van de gemakkelijkste manieren is om hiervoor een calculated table te maken. In de Modeling ribbon kies je voor “New Table”.
De eerste functie die we gebruiken is de CALENDAR() functie. CALENDAR() neemt 2 parameters aan, een startdatum en een einddatum. De tweede functie die we hier gebruiken is de DATE() functie. DATE () vraagt 3 parameters: een jaar, een maand en een dag. Wil je 1 januari 2000 terugkrijgen als datum, dan schrijf je het volgende in DAX:
DATE(2000,1,1)
Met de volgende DAX expressie creëer je een tabel met 1 kolom en 1 rij voor elke datum tussen 1 januari 2000 en 31 december 2050. Uiteraard kan je de data aanpassen aan je eigen noden.
CALENDAR ( DATE ( 2000, 1, 1 ), DATE ( 2050, 12, 31 ) )
Standaard heeft de kolom altijd de naam ‘Date’. We kunnen deze hernoemen door de SELECTCOLUMNS() functie te gebruiken:
SELECTCOLUMNS (
CALENDAR ( DATE ( 2000, 1, 1 ), DATE ( 2050, 12, 31 ) ),
"Datum", [Date]
)
Met de functie ADDCOLUMNS kunnen we gemakkelijk nieuwe kolommen toevoegen zoals Maand, MaandNummer, DagVanDeWeek, … We gebruiken de FORMAT() functie om de kolommen te formatteren.
ADDCOLUMNS (
SELECTCOLUMNS (
CALENDAR ( DATE ( 2000, 1, 1 ), DATE ( 2050, 12, 31 ) ),
"Datum", [Date]
),
"DatumAlsInteger", FORMAT ( [Datum], "YYYYMMDD" ),
"Jaar", YEAR ( [Datum] ),
"Maandnummer", FORMAT ( [Datum], "MM" ),
"JaarMaandNummer", FORMAT ( [Datum], "YYYY/MM" ),
"JaarMaandKort", FORMAT ( [Datum], "YYYY/mmm", "nl-be" ),
"MaandNaamKort", FORMAT ( [Datum], "mmm", "nl-be" ),
"MaandNaamLang", FORMAT ( [Datum], "mmmm", "nl-be" ),
"DagVanWeekNummer", WEEKDAY ( [Datum], 2 ),
"DagVanWeek", FORMAT ( [Datum], "dddd", "nl-be" ),
"DagVanWeekKort", FORMAT ( [Datum], "ddd", "nl-be" ),
"Kwartaal", "Q" & FORMAT ( [Datum], "Q" ),
"JaarKwartaal",
FORMAT ( [Datum], "YYYY" ) & "/Q"
& FORMAT ( [Datum], "Q" )
)
Hieronder vind je dezelfde formule voor een Engelstalige datumtabel. Als derde parameter van FORMAT() kunnen we de locale_name meegeven. Hierboven gebruikten we “nl-be” om de namen van dagen en maanden in het Nederlands te krijgen. Hieronder gebruiken we “en-us” om de Engelse naamgeving te verkrijgen. Je kan dit script eenvoudig aanpassen om de datumtabel in een andere taal te maken.
ADDCOLUMNS (
CALENDAR ( DATE ( 2000, 1, 1 ), DATE ( 2050, 12, 31 ) ),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm", "en-us" ),
"MonthNameShort", FORMAT ( [Date], "mmm", "en-us" ),
"MonthNameLong", FORMAT ( [Date], "mmmm", "en-us" ),
"DayOfWeekNumber", WEEKDAY ( [Date], 2 ),
"DayOfWeek", FORMAT ( [Date], "dddd", "en-us" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd", "en-us" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter",
FORMAT ( [Date], "YYYY" ) & "/Q"
& FORMAT ( [Date], "Q" )
)
Stap 2 – Kolommen sorteren
In Power BI visuals worden waarden in kolommen standaard alfabetisch (voor alfanumerieke kolommen) of van klein naar groot getoond (voor numerieke kolommen). De namen van dagen en maanden willen we natuurlijk niet alfabetisch zien, maar wel chronologisch. Hiervoor kunnen we de “Sort by Column” feature gebruiken.
Ga hiervoor eerst naar de Data View en selecteer de kolom die je wil sorteren (bijv. DagVanWeek) en kies “Sort by Column” uit de ribbon. In de dropdown box kies je een andere kolom waarop je wil sorteren (bijv. DagVanWeekNummer).
Stap 3 – Mark as a Date Table
Als laatste stap moeten we onze kalender markeren als datumtabel. Dit kan via “Mark as date table” in de Table tools ribbon.
In een volgend scherm vraagt men nog eens expliciet aan te duiden wat onze datumkolom is.
Op dit moment gaat Power BI ook volgende controles doen op de gekozen kolom:
- Bevat unieke waarden.
- Bevat geen null waarden.
- Bevat aaneengesloten datumwaarden (van begin tot eind).
- Als de kolom van het gegevenstype Datum/Tijd is, heeft het dezelfde tijd voor elke waarde.
Zo simpel is het dus, met behulp van deze 3 eenvoudige stappen bouw je zelf een datumtabel in Power BI.
