Reporting & Visualisation

Datumselectie via een keuzemenu in Power BI

Power BI wordt mainstream

Met de groeiende populariteit van Power BI is het Power platform nu bezig aan een opmars om een bedrijfsbreed rapportage platform te worden. Waar een tijd geleden voornamelijk de early adaptors gebruik maken van Power BI-rapportages voor self-sevice BI schuift dit nu langzaam door naar een grotere groep die vaak ook minder bekend is met de mogelijkheden en de gebruikersinterface van Power BI.
Met name voor deze groep is de onderstaande oplossing voor datumselecties een mooie oplossing.

Het doel is om op eenvoudige wijze uit een aantal datum periodes te kunnen selecteren via een drop-down of ander keuzemenu. Dit stelt ook minder ervaren gebruikers in staat om eenvoudig te schakelen tussen deze datumselecties. Daarnaast is het ook mogelijk om met andere rapportagetools de modellen te benaderen. Ook in deze tools zijn dezelfde datumselecties beschikbaar.

Model voorbereiden

Voordat je kunt starten met de datum slicers dien je eerst een aantal zaken te regelen in Power BI. Als eerste dien je het gebruik van automatische datum hiërarchieën uit te zetten. Dit moet je doen voordat je data gaat inlezen. Kies uit het menu: File -> Options and Settings -> Options. Bij Current File -> Data Load. Verwijder het vinkje bij Time intelligence -> Auto date/time.

Als tweede dient het model een eigen datum tabel te hebben. Merk deze tabel als ‘Mark as Date Table’. Kies hiervoor uit het ‘Modeling’ menu de optie ‘Mark as Date Table’. In het vervolg scherm geef je aan welk veld het datum veld is uit je datum tabel.

Mark as Date Table

Voor het correct werken van Time intelligence functies moet de tabel een aansluitende rij van datums zijn die begint op een 1 januari van het eerst jaar tot aan 31 december van het laatste jaar. De datum tabel kun je uit de bron lezen of opzetten als een DAX-expressie. Voor meer informatie over datum dimensie kun je hier terecht: https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax

Datum Filter tabel

De datumselectie wordt mogelijk gemaakt door een tabel waarin een aantal vaste datumselecties zijn beschreven. Iedere regel is een eigen datumselectie. Deze tabel is opgenomen als een Power Query script. In deze tabel staat een korte omschrijving van de periode die je kunt kiezen en de bijbehorende start- en einddatum. Voor deze datums is steeds een andere afleiding nodig en dat maakt dat Power Query een erg geschikte scripttaal is voor deze tabel.

Advanced Editor
Advanced Editor voor Power Query

Open het menu ‘Get Data’ en kies voor ‘Blank Query’. Wijzig de query de naam in [Date Filter]. Om onderstaande script in een query te kunnen plannen dien je de Advanced Editor te openen. Deze is te vinden via de menubalk ‘Home’ en vervolgens onder ‘Query’ staat ‘Advanced Editor’. Verwijder de code die er al staat en knip en plak onderstaand script:

let
    // Store value of today in variable to make the formulas more readable.
    Today =  DateTime.Date(DateTime.LocalNow()),

    // For each selection determine the StartDate and EndDate for the selected time period. 
    // Create a row object for each filter you require. The rows will be joined later.

    // Row 1 refers to the data table in the model to find the first and last date. 
    // Adjust the Table and Field name if necessary or give a fixed date as StartDate and
    // EndDate like: StartDate = DateTime.Date(#datetime(2016,1,1,0,0,0)) 

    Row1 =  [ DateFilterID = 1,  Sort = 0, Group = "All", Date Filter = "All", 
                StartDate = Record.Field(Table.Min(#"Date_Table", "Date_Key"), "Date_Key"),
                EndDate   = Record.Field(Table.Max(#"Date_Table", "Date_Key"), "Date_Key") 
            ],
    Row2 =  [ DateFilterID = 2,  Sort = 20, Group = "Day", Date Filter = "ToDay", 
                StartDate = Today,
                EndDate   = Today              ],    
    Row3 =  [ DateFilterID = 3,  Sort = 30, Group = "Week", Date Filter = "This Week", 
                StartDate = Date.StartOfWeek(Today, 1), 
                EndDate   = Date.EndOfWeek(Today, 1)
            ],
    Row4 =  [ DateFilterID = 4,  Sort = 40, Group = "Month", Date Filter = "This Month", 
                StartDate = Date.StartOfMonth(Today), 
                EndDate   = Date.EndOfMonth(Today)
            ],
    Row5 =  [ DateFilterID = 5,  Sort = 50, Group = "Quarter", Date Filter = "This Quarter", 
                StartDate = Date.StartOfQuarter(Today), 
                EndDate   = Date.EndOfQuarter(Today)
            ],
    Row6 =  [ DateFilterID = 6,  Sort = 60, Group = "Year", Date Filter = "This Year", 
                StartDate = Date.StartOfYear(Today), 
                EndDate   = Date.EndOfYear(Today)
            ],            
    Row7 =  [ DateFilterID = 7,  Sort = 70, Group = "Day", Date Filter = "Yesterday", 
                StartDate = Date.AddDays(Today, -1), 
                EndDate   = Date.AddDays(Today, -1)
            ],
    // When working with week functions, the startday of the need to be specified (1 = Monday)
    Row8 =  [ DateFilterID = 8,  Sort = 80, Group = "Week", Date Filter = "Last Week", 
                StartDate = Date.StartOfWeek(Date.AddWeeks(Today, -1), 1), 
                EndDate   = Date.EndOfWeek(Date.AddWeeks(Today, -1), 1)
            ],              
    Row9 =  [ DateFilterID = 9,  Sort = 90, Group = "Month", Date Filter = "Last Month", 
                StartDate = Date.StartOfMonth(Date.AddMonths(Today, -1)), 
                EndDate   = Date.EndOfMonth(Date.AddMonths(Today, -1))
            ],  
    Row10 =  [ DateFilterID = 10,  Sort = 100, Group = "Quarter", Date Filter = "Last Quarter", 
                StartDate = Date.StartOfQuarter(Date.AddQuarters(Today, -1)), 
                EndDate   = Date.EndOfQuarter(Date.AddQuarters(Today, -1))
            ], 
    Row11 = [ DateFilterID = 11,  Sort = 110, Group = "Year", Date Filter = "Last Year", 
                StartDate = Date.StartOfYear(Date.AddYears(Today, -1)), 
                EndDate   = Date.EndOfYear(Date.AddYears(Today, -1))
            ],  
    Row12 = [ DateFilterID = 12,  Sort = 120, Group = "Day", Date Filter = "Last 10 days", 
                StartDate = Date.AddDays(Today, -9), 
                EndDate   = Today
            ],
    Row13 = [ DateFilterID = 13,  Sort = 130, Group = "Week", Date Filter = "Last 5 Week", 
                StartDate = Date.StartOfWeek(Date.AddWeeks(Today, -4), 1), 
                EndDate   = Date.EndOfWeek(Today, 1)
            ],              
    Row14 = [ DateFilterID = 14,  Sort = 140, Group = "Month", Date Filter = "Last 2 Month", 
                StartDate = Date.StartOfMonth(Date.AddMonths(Today, -1)), 
                EndDate   = Date.EndOfMonth(Today)
            ],  
    Row15 = [ DateFilterID = 15,  Sort = 150, Group = "Quarter", Date Filter = "Last 2 Quarter", 
                StartDate = Date.StartOfQuarter(Date.AddQuarters(Today, -1)), 
                EndDate   = Date.EndOfQuarter(Today)
            ], 
    Row16 = [ DateFilterID = 16,  Sort = 160, Group = "Year", Date Filter = "Last 2 Year", 
                StartDate = Date.StartOfYear(Date.AddYears(Today, -1)), 
                EndDate   = Date.EndOfYear(Today)
            ],              

    //Joining all rows into a table and assign the appropriate field types
    #"Empty Table" = Table.FromRecords(  {Row1, Row2, Row3, Row4, Row5, Row6, Row7, Row8, Row9, Row10, Row11, Row12, Row13, Row14, Row15, Row16} , {"DateFilterID", "Sort", "Group", "Date Filter", "StartDate", "EndDate"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Empty Table",{{"DateFilterID", Int64.Type}, {"Sort", Int64.Type}, {"Group", type text}, {"Date Filter", type text}, {"StartDate", type date}, {"EndDate", type date}})    
in
    #"Changed Type"

Indien nodig pas aan de start en eind datum voor Row1. Deze verwijst naar de bestaande datum tabel. Klik op ‘Close and apply’. Ga naar de Data weergave van de ‘Date Filter’ tabel. Alleen het veld ‘Date Filter’ mag zichtbaar zijn in de rapportage. Selecteer voor alle andere velden de optie ‘Hide in report view’. Voor het veld Date Filter kies voor ‘Sort by Column’ voor de kolom ‘Sort’.

Per filter optie wordt er een rij object gemaakt. Voor de optie [All] wordt de datum tabel zelf geraadpleegd. De eerste en laatste datum uit de datum tabel worden opgehaald. In het bovenstaande script zijn veelgebruikte datumselecties opgenomen die niet allemaal even zinvol zijn maar wel handig om naar eigen wens aan te passen voor je rapport.

Let op als je datumbewerkingen doet op basis van weken. Je moet expliciet opgeven wat de eerste dag van de week is. In Power Query is 0 = Zondag en 1 = Maandag. In Europa is het gebruikelijk dat de week op maandag begint, in tegenstelling tot de VS waar zondag als eerste dag van de week wordt beschouwd.

Bij bewerkingen op maanden moet je er op letten dat de functie EndOfMonth() pas uitvoert nadat je het aantal gewenste maanden bent opgeschoven. Niet alle maanden zijn even lang en je kunt er dan wel eens een dag naast zitten.

Koppel tabel

Om op basis van deze tabel te kunnen filteren is er een koppeling nodig met de datumtabel. Een datum kan in meerder datumselecties zitten en een datumselectie omvat meerdere datums. Dit resulteert in een veel-op-veel relatie tussen deze tabellen. Alle combinaties van de datumtabel en datumselecties worden in deze koppeltabel opgenomen.

Open het menu ‘Get Data’ en kies voor ‘Blank Query’. Wijzig de query de naam in [Date Filter Bridge]. Om onderstaande script in een query te kunnen plannen dien je weer de Advanced Editor te openen.

let
    // This script generates a bridge table between the [Date] table and the [Date Filter] table.
    // The following steps are performed:
    // Copy the [Date Filter] table 
    // Calculate the difference in days between StartDate and EndDate. 
    // Add all the days between StartDate and EndDate as a list
    // Expand that list to a column
    // Tidy up the table by removing columns and setting the appropriate datatypes    
    Source = #"Date Filter",
    #"Inserted Date Subtraction" = Table.AddColumn(Source, "Subtraction", each Duration.Days([EndDate] - [StartDate]) + 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Inserted Date Subtraction", "Date", each List.Dates([StartDate], [Subtraction], #duration(1,0,0,0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Date"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom", {"Group", "Sort", "Date Filter", "StartDate", "EndDate", "Subtraction"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Date", type date}})
in
    #"Changed Type"

Dit script heeft geen verdere aanpassingen nodig. Klik op ‘Close and apply’. Ga naar de Data weergave van de ‘Date Filter’ tabel. Deze tabel is een hulptabel en hoeft niet zichtbaar te zijn in de rapportage. Rechter muisklik op de tabel en kies voer ‘Hide in report view’.

Tabellen koppelen

De volgende stap is het koppelen van de tabellen. Kies voor de weergave ‘Model’. Indien deze nog niet bestaat, maak een koppeling tussen de volgende velden:

  • ‘Date’[Date] -> ‘Date Filter Bridge’[Date] met ‘Cross filter Direction’: Both
  • ‘Date Filter’[ID] -> ‘Date Filter Bridge’[ID] met ‘Cross filter Direction’: Single

Let op dat ‘Cross filter Direction’ correct is ingesteld omdat het datumfilter anders niet werkt.

De relaties moeten er dan uit zien as volgt:

Relaties tussen de tabellen

Rapport

Nu kun je de nieuwe tabel gebruiken als slicer in je rapport.

Het model moet bij voorkeur dagelijks worden ververst omdat StartDatum en EindDatum alleen bij het verversen van het model worden berekend. Als je drie dagen niet hebt ververst zal de keuze ‘Vandaag’ 3 dagen geleden zijn.

Voeg niet te veel keuzes toe. Als de lijst erg lang wordt ben je het voordeel van de eenvoudige selectie weer kwijt.
De oplossing is ook te gebruiken in SSAS 2017 omdat deze power query ondersteund. Wil je deze oplossing in oudere versies gebruiken zul je de queries moeten omschrijven naar T-SQL.

Een gedachte over “Datumselectie via een keuzemenu in Power BI

  1. Bedankt voor de query. De slicer die ik er mee gemaakt heb lijkt goed te werken, tot ik cumulatieve gegevens toevoeg aan een tabel. De cumulatieve gegevens zijn hetzelfde als de gegevens per week.

    Op het moment dat ik een slicer op basis van onderstaande formule toevoeg (nieuwe kolom in datumtabel) worden de gegevens wel cumulatief getoond.

    IsInCurrentWeekISO = IF([isInCurrentYearIso] && WEEKNUM(NOW())=[WeekISO]; “Huidige week”; “Niet in huidige week”)

Geef een reactie

Het e-mailadres wordt niet gepubliceerd. Vereiste velden zijn gemarkeerd met *