‘Rekenen’ met datums in Power BI met DAX

De dimensie ‘datum‘ neemt een speciale plaats in bij het bouwen van dashboards. Nagenoeg elke visualisatie heeft wel met een bepaald datumbereik te maken. Waardevolle informatie ontstaat als de gegevens kunnen worden vergeleken met vergelijkbare periodes, zoals vorige maand of vorig jaar. Power BI beschikt over een aantal intelligente DAX functies waarmee heel gemakkelijk berekeningen kunnen worden uitgevoerd die rekening houden met een geselecteerd datum bereik.

In deze blog wordt een aantal van deze functies nader bekeken.

Aanmaken Datum dimensie

Om te beginnen hebben we een datum tabel nodig waarin ‘alle’ datums zijn opgenomen en die we als een dimensie kunnen gebruiken.

Maak in Power BI een nieuwe Table aan door in de tab Modeling te klikken op New Table.

Plak de volgende code in de werkbalk:

[code language=”csharp”]
Date =

ADDCOLUMNS (

CALENDAR (DATE(2000,;1,;1); DATE(2025,;12,;31));

“DateAsInteger”; FORMAT ( [Date]; “YYYYMMDD” );

“Year”; YEAR ( [Date] ); “Month”; MONTH([Date]); “Week”; WEEKNUM([Date]);

“Monthnumber”; FORMAT ( [Date]; “MM” );

“YearMonthnumber”; FORMAT ( [Date]; “YYYY/MM” );

“YearMonthShort”; FORMAT ( [Date]; “YYYY/mmm” );

“MonthNameShort”; FORMAT ( [Date]; “mmm” );

“MonthNameLong”; FORMAT ( [Date]; “mmmm” );

“DayOfWeekNumber”; WEEKDAY ( [Date] );

“DayOfWeek”; FORMAT ( [Date]; “dddd” );

“DayOfWeekShort”; FORMAT ( [Date]; “dddd” );

“Quarter”; “Q” & FORMAT ( [Date]; “Q” );

“YearQuarter”; FORMAT ( [Date]; “YYYY” ) & “/Q” & FORMAT ( [Date]; “Q” )

)

[/dm_code_snippet]

(Let op: afhankelijk van de power bi versie moet de ; vervangen worden door een ,)

Deze code maakt een tabel aan met alle datums tussen 1-1-2000 en 31-12-2025.

Inlezen demo data

Als demo data gebruiken we de gegevens die in de volgende txt te downloaden zijn: demodata
Het betreffen verkoopbedragen per medewerker met een timestamp.

Lees de CSV in via Get Data en vervolgens te kiezen voor Tekst/CSV.

Selecteer het gedownloade csv bestand.

In het volgende scherm kun je vaststellen of de gegevens goed worden ingelezen en klik je vervolgens op Load:

Koppelen tabellen

Tussen de tabellen moeten vervolgens een relatie gelegd worden.

Klik in de tab Modeling op Manage relationships

Klik op New om een nieuwe relatie aan te maken

Selecteer de Date tabel en de demo data tabel.

Selecteer in de Date tabel de kolom ‘Date ‘ en in de demo data de kolom ‘PeriodeDatum’ kolom om de juiste relatie aan te geven.

Zorg dat de Cardinality op One to Many staat en de Cross Filter Direction op Single.

In bovenstaande afbeelding zie je al een klein issue die we moeten oplossen. In de kolom PeriodeDatum staat de datum vermeld met een TimeStamp erachter. De datum in de Date tabel heeft deze timestamp niet.

Power BI zal de relatie hierdoor niet goed kunnen leggen omdat gekeken wordt naar een 100% match van de gegevens.

De opmaak van beide kolommen moet exact hetzelfde zijn.

Klik op OK en vervolgens op Close om de Relatie aan te maken.

Ga vervolgens naar het Data overzicht door op het Tabel pictogram aan de linkerkant te klikken:

Selecteer de tabel demo data in het Fields overzicht.

Selecteer nu de kolom periodeDatum en pas onder de tab Modeling het Format van de tabel aan naar Data type: Date/Time en Format 14-3-2001 (d-M-yyyy)

Stel vast dat het format van de kolom Date in de tabel Date precies hetzelfde Format heeft:

DAX Berekeningen

Schakel terug naar de Report weergave:

We maken nu eerst een tabel aan met de totalen per jaar.

Klik op het Visualisatie object Table.

Selecteer vanuit de tabel Date het veld Year en vanuit de tabel demo data het veld Verkoopbedrag.

We zien nu een tabel met de totalen per jaar.

Bedrag vorig jaar

Klik met de rechterkmuisknop op de tabel demo data en kies voor New Measure:

Voer in de werkbalk de volgende DAX formule in:

BedragVorigJaar = CALCULATE(sum(‘demo data'[Verkoopbedrag]);PREVIOUSYEAR(‘Date'[Date]))

Selecteer nu weer de zojuist aangemaakte tabel en voeg de Measure BedragVorigJaar toe aan de tabel:

In de tabel wordt nu naast het bedrag van het jaar ook het bedrag van het vorige jaar getoond!

Bedrag zelfde periode vorig jaar

Je kunt ook het bedrag over dezelfde periode vorig jaar berekenen.

Maak een nieuwe tabel aan en selecteer uit de tabel Dat de kolom YearMonthnumber en uit de tabel demo data het veld Verkoopbedrag.

In deze tabel zijn de bedragen per maand opgenomen vanaf begin 2016.

Klik met de rechtermuisknop weer op de tabel demo data en maak een nieuwe measure aan.

Voer in de werkbalk de volgende DAX formule in:

BedragZelfdePeriodeVorigJaar = CALCULATE(sum(‘demo data'[Verkoopbedrag]); SAMEPERIODLASTYEAR(‘Date'[Date]))

Selecteer de tabel per maand en voeg de zojuist aangemaakte measure BedragZelfdePeriodeVorigJaar toe aan de tabel.

De bedragen van dezelfde periode vorig jaar worden perfect uitgerekend!

Andere Time Intelligence functies

DAX en Power BI kennen nog veel meer intelligente datum functie zoals:

• TOTALYTD
• NEXTMONTH
• PARALLELPERIOD

Een complete lijst met functies vind je via de volgende link: https://msdn.microsoft.com/en-us/library/ee634763?ui=en-US&rs=en-US&ad=US

Afsluiting

De dimensie Datum ontbreekt in geen enkel dashboard. Met behulp van de intelligente DAX functies wordt het rekenen in Power BI over allerlei verschillende datum bereiken heel makkelijk gemaakt.