Warum (fast) jeder Power BI Report eine Datumstabelle benötig?

Es passiert zu oft, dass Power BI Berichte, aufgrund einer fehlenden Datumstabelle, die falschen Werte zeigen oder überhaupt nicht funktionieren. Dieser Artikel soll dem Abhilfe verschaffen. Da die Dimension "Zeit" bei der Analyse von Daten fast immer eine Rolle spielt, ist Untenstehendes für praktisch jeden Power BI Report anwendbar.


Einer der ersten Schritte beim Erstellen eines sauberen Datenmodells in Power BI ist die Integration einer Datumstabelle. Aber weshalb wird dieser "Kalender" eigentlich benötigt?


In Power BI gibt es sogenannte DAX Zeitintelligenzfunktionen, welche nur korrekt funktionieren, wenn im Power BI Datenset eine Tabelle vorhanden ist, in welcher jedes Datum aufgelistet ist, für welches Werte vorhanden sind.


Das Ganze lässt sich am besten anhand eines Beispiels veranschaulichen. Damit dies direkt in Power BI nachvollzogen werden kann gibt es hier die benötigten Dateien:


Download


Für unser Beispiel laden wir folgende 4 Tabellen aus Excel:

  • Product_Lookup: Stammdaten der Produkte

  • Sales_Person_Lookup: Stammdaten der Verkäufer

  • Region_Lookup: Stammdaten der Verkaufsregionen

  • Sales_Data: Transaktionsdaten aller Verkäufe


Diese Tabellen führen zu folgendem Datenmodell.


Nun erstellen wir die folgenden 4 Measures, um zu zeigen, wie man es eben nicht machen soll. Da diese Measures jeweils das Datum enthalten, wir aber nur in der Sales Tabelle ein Datum haben, wird dieses verwendet. Die korrekten Measures erstellen wir später:

  • Summe aller Verkäufe: Sales = SUM(Sales_Data[Amount])

  • Kumulierte Verkäufe seit Anfang Jahr (Year to date): Sales YTD (wrong) = CALCULATE([Sales]; DATESYTD(Sales_Data[Date]))

  • Summe aller Verkäufe des Vorjahres: Sales PY (wrong) = CALCULATE([Sales]; DATEADD(Sales_Data[Date];-1;YEAR))

  • Kumulierte Verkäufe des Vorjahres: Sales PY YTD (wrong) = CALCULATE([Sales PY (wrong)];DATESYTD(Sales_Data[Date]))


Als nächstes fügen wir eine Tabelle mit unseren neuen Measures ein.

In den Spalten mit den Vorjahreswerten wird klar, dass diese Measures nicht funktionieren wie gewünscht. Der Grund dafür ist, dass wir jeweils genau minus 1 Jahr rechnen, jedoch nicht für jeden Verkauf im Jahr 2020 auch ein Datum im Jahr 2019 in der Sales Tabelle existiert. Daher entspricht auch die Summe der 3 Werte aus der Spalte "Sales PY (wrong)" nicht dem Total.


Nun möchten wir sehen, wie das Ganze mit einer Datumstabelle aussieht. Datumstabellen können auf unterschiedliche Weise erstellt werden. Man kann eine Tabelle in Excel erstellen und laden, es gibt die DAX Funktion CALENDARAUTO oder man kann einen M Code im Power Query Editor verwenden. Wir haben uns in diesem Fall für die Variante Power Query Editor entschieden.


Als erstes fügen wir eine neue Datenquelle hinzu.


Als nächstes fügen wir den M Code für den zu erstellenden Kalender im Power Query Editor ein.


let
    Source = #date(2019, 1, 1),
    #"add dates until today" = List.Dates(Source, Number.From(DateTime.LocalNow())+0- Number.From(Source), #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(#"add dates until today", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Column Date" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type of Column Date" = Table.TransformColumnTypes(#"Renamed Column Date",{{"Date", type date}})
in
    #"Changed Type of Column Date"

Dieser Code erstellt eine Datumstabelle mit Startdatum 01.01.2019 und dem aktuellen Datum als Enddatum. Das rot markierte Startdatum kann individuell angepasst werden. Falls der Kalender auch in die Zukunft zeigen soll, kann die rot markierte Zahl entsprechend angepasst werden. Z.B. führt +365 zu einem Kalender, welcher heute in einem Jahr endet.


Oft werden der Datumstabelle weitere Spalten hinzugefügt. In Power Query Editor gibt dazu vordefinierte Funktionen. So wird zum Beispiel eine Spalte eingefügt, welche nur das Jahr zeigt.


Es ist zu empfehlen, einmal eine gute Datumstabelle zu erstellen und den dadurch generierten M Code zu speichern um ihn für zukünftige Berichte wiederverwenden zu können.


Nach dieser Änderung muss die neue Tabelle in Power BI Desktop geladen und mit der Sales Tabelle verknüpft werden.


Wir erstellen dieselben Measures wie oben beschrieben noch einmal, diesmal verlinken wir jedoch auf die Datumstabelle:

  • Der bereits erstellte Measure Sales enthält keine Zeitintelligenzfunktion und muss entsprechend nicht neu erstellt werden

  • Sales YTD (correct) = CALCULATE([Sales]; DATESYTD(Date_Lookup[Date]))

  • Sales PY (correct) = CALCULATE([Sales]; DATEADD(Date_Lookup[Date];-1;YEAR))

  • Sales PY YTD (correct) = CALCULATE([Sales PY (correct)];DATESYTD(Date_Lookup[Date]))


Wir sind jetzt soweit um die beiden Resultate zu vergleichen. Dafür fügen wir wiederum eine Tabelle mit den neuen Measures ein. Diesmal verwenden wir jedoch das Datum aus der Datumstabelle.

In dieser neuen Tabelle sieht man klar, dass nun jedes Datum vorhanden ist und in jeder Zeile der korrekte Wert steht.


Das Ganze wird jedoch noch viel klarer, sobald die kumulierten Verkaufszahlen 2020 mit dem Vorjahr in einer Grafik verglichen werden.

Das linke Bild zeigt den Verlauf ohne der Datumstabelle. Da nur Tage gezeigt werden, an welchen es einen Verkauf gab, ist diese Ansicht nicht korrekt. Auf der rechten Seite hingegen gibt es einen Datenpunkt pro Tag und das Bild entspricht dem tatsächlichen Verlauf.


Wir wissen nun, dass die Daten ohne Datumstabelle nicht korrekt sind, jedoch gab es bis zu diesem Zeitpunkt keine Fehlermeldung. Was passiert aber, wenn wir nun noch nach der Verkaufsregion filtern?


Wenn wir uns die Fehlermeldung genauer anschauen wird klar, dass wir spätestens jetzt nicht um eine Datumstabelle herumkommen.



Hier kann der fertige Power BI Report angeschaut werden:


Live Report


Können Sie die Frage, warum man für einen Power BI Report eine Datumstabelle benötigt nun beantworten? Melden Sie sich an, um diesen Artikel zu kommentieren.





0 Ansichten