arrow_drop_up arrow_drop_down
4 juli 2020 

Recept voor datumweergave in Power BI

Het tonen van datumreeksen in Power BI, die gebaseerd is op een reeds geselecteerde periode, is niet eenvoudig. Veel rapporten worden gefilterd op een bepaalde datum. Bijvoorbeeld een maand. Als je dan een grafiek wilt tonen met een reeks aan periodes die buiten de geselecteerde periode ligt, heb je bepaalde DAX formules nodig of je kunt mogelijk een eind komen met het aanpassen van de interacties tussen visuals of het relatieve datum filter. Echter loop je bij alle varianten al snel tegen aantal een beperkingen aan. Deze zijn op te lossen met behulp van de volgende methode. Bovendien krijg je er een aantal extra’s bij, die je met de standaard in ieder geval niet krijgt.

Extra items op de x-as

De uitkomst is te zien in onderstaande video. Opvallend zijn hier de extra’s op de x-as, namelijk ‘Average’ en ‘Difference’. Dit zijn extra’s die je kan toevoegen en zelf kunt uitbreiden met nog meer varianten. In het filmpje wordt duidelijk wat mogelijk is.

Benodigdheden

  • Een datum tabel
  • Een tally table met cijfers van -1000 t/m 1000, inclusief 0
  • Een tabel met labels voor bepaalde periodes
  • Een tabel met de frequenties

Datamodel

Deze methode kan eenvoudig toegevoegd worden aan bestaande modellen. De onderdelen worden namelijk ‘los’ toegevoegd. Je huidige model wordt uitgebreid als volgt:

Alle onderdelen aan de rechterzijde toevoegen. Gebruik je een database, dan vindt je de scripts als bijlage. Normaliter verbind ik met een tabular model, maar voor de eenvoud van downloaden, heb ik nu alles geïmporteerd in Power BI.

De tabellen

Axis Variants

Deze tabel bevat alle datums die ook voorkomen in de datum tabel, maar dan meerdere keren. Je krijgt per gewenste as-label een datum record. Hoe meer as-varianten, hoe meer records in deze tabel. Door de opzet en de snelheid van DAX heeft dit weinig impact op de performance in de praktijk.

Frequency

Deze tabel bevat simpelweg dag, week, maand, kwartaal en jaar. Kan eventueel nog uitgebreid worden naar mijn inzicht. Denk aan half jaar, financieel jaar (handig in het geval van een gebroken boekjaar bijvoorbeeld).

Tally table

Dit is een simpele tabel met enkel nummers. Deze kan je op meerdere manieren inzetten, om gebruikers een getal te laten selecteren. Standaard vul ik deze met getallen van -1000 t/m 1000. Inclusief de 0. Meer informatie over de tally table, hier.

DAX

Naast de tabellen is er ook DAX nodig, om alles te laten functioneren. De belangrijkste DAX formule is de ‘Axis Filter’ measure. Deze ziet er als volgt uit:

Deze formule kan op meerdere manieren gebruikt worden. Om waarden te tonen in een visual heb je een measure nodig die gebruik maakt van een filter op basis van de Axis Filter measure. Dat kan via het filter op de visual of in via de measure. Stel u heeft een measure ‘Revenue’, dan zal deze standaard alleen de waarde tonen voor de geselecteerde periode. Wilt u ook andere periodes tonen, dan moet u iets doen met filtering.

Uitleg van de formule

De uitkomst van de measure is een 1 of BLANK per record in de Axis variants tabel. Door te filteren op de 1, wordt de Axis variants tabel gefilterd. De measure heeft als input een nummer (tally table), een referentie datum (huidige geselecteerde datum) en een frequency nodig. Deze moeten alle drie op de één of andere manier in de context beschikbaar zijn.

Voor de frequencycode gaan we uit van 1 selectie, zo niet, dan werkt deze measure niet. Voor de de datum kiezen we de meest recente (max) en voor de tally table willen we het laagste getal terug.

Naast de huidige datum, is ook een tweede datum nodig om de range te bepalen. Deze wordt uitgerekend met behulp van de EOMONTH functie. Deze biedt namelijk de mogelijkheid om een aantal maanden achteruit of vooruit te gaan. De tally table voorziet het getal om dit te doen.

Vervolgens rekenen we een start en enddate uit, deze wordt weer gebruikt om de range aan gewenste datums uit de Axis Variants tabel op te halen. Zoals eerder aangegeven biedt de Axis Variants tabel meerdere datums per variant. Daarom is ook een filter op frequency code nodig. De alternatieven as-labels zijn geplaatst onder het jaartal 0. Die worden ook meegenomen en kunnen per label optioneel uitgefilterd worden via het veld ‘column type’ uit de Axis Variants tabel.

Measures naar gebruik

Deze Axis filter kan je toevoegen aan je filter op de visual of in een measure. De measures die je wilt tonen in de visual moeten wel een extra filter opnemen, anders krijg je niet de juiste waarden te zien. Deze ziet er als volgt uit:

Omdat we deze aan een bar chart toevoegen noem ik deze measure Bar_Revenue. Voor de speciale waarden (Average, Difference, Mediaan of wat jij wilt) kan je specifiek verwijzen naar andere measures. De rest is voor periodes. Het belangrijkste is om bij de calculate, het deel bij intersect toe te voegen. Die zorgt namelijk dat het bestaande filter van de datum tabel wordt afgehaald en gecombineerd met alle datums die beschikbaar zijn in de Axis Variants. De Axis variants is weer gefilterd door het filter op de measure ‘Axis Filter’ = 1.

Je kan Axis Filter ook opnemen in de measure zelf: Calculate(<measure>, ISBLANK(‘Axis Variants'[Axis Filter]) = False(), Intersect….)

 

Technische uitleg: Het hoe en wat in een bestaand Power BI model

Demo files beschikbaar via GitHub.

Als je vragen hebt of verbeteringen ziet. Ik hoor het graag. Benieuwd waar je staat met BI? Doe de quiz.

 

Over de schrijver
Met mijn 20+ jaar ervaring herken ik data organisatie problemen als geen ander en kan ik u helpen bij het beter omgaan met data. Daardoor wordt rapporteren en analyseren eenvoudiger en haalt u de meeste waarde uit uw data. Samen met u kijk ik naar de huidige situatie en maak ik een plan om een stabiele data organisatie op te zetten. Dit zorgt ervoor dat u beschikt over betrouwbare data in uw rapportages. Om te zorgen dat u er geen omkijken naar heeft, geef ik garantie op de werkzaamheden.
Reactie plaatsen