Scorecarding in Power BI Teil 3: DAX-Pattern für Period-over-Period Berechnungen bei relativem Zeitfilter

Publiziert von

KPIs in einer Scorecard werden typischerweise mit dem Wert der laufenden Periode sowie dessen prozentualer und/oder absoluter Veränderung zur vorangegangenen Vergleichsperiode dargestellt. Im ersten Teil dieser Blogserie wurde gezeigt, wie die in Power BI verfügbaren Standardberechnungen für Month-over-Month (MoM), Quarter-over-Quarter (QoQ) und Year-over-Year (YoY) noch ausgebaut werden können. Im zweiten Teil wurde die Week-over-Week (WoW) Berechnung realisiert.

In diesem dritten Teil wird nun gezeigt, wie bei Anwendung eines relativen Zeitfilters nach dem Muster Last 7 Days dynamisch die Vorperiode mit der gleichen Anzahl an Kalendertagen ermittelt werden kann.

Die hier vorgestellten DAX-Funktionen können auch in SQL Server Tabular Model und Excel Power Pivot verwendet werden.

Übersicht zur Blogserie

Teil 1: DAX-Pattern für Period-over-Period Berechnungen
Teil 2: DAX-Pattern für Week-over-Week Berechnungen
Teil 3: DAX-Pattern für Period-over-Period Berechnungen bei relativem Zeitfilter

1. Aufgabenstellung

Der relative Zeitfilter – in Power BI verfügbar auf jedem Datumsfeld sowohl als Slicer als auch als im Filterbereich – ermöglicht die Realisierung automatisch rollierender Dashboards. Die Aufgabe im Scorecarding besteht in diesem Zusammenhang darin, bei der Anwendung bspw. eines Last 7 Days Filters den Vergleichswert des davor liegenden (gleich langen) Zeitraums zu ermitteln:

2. Ausgangssituation

Wir verwenden das Datenmodell aus Teil 1 und 2: die Faktentabelle Fact InternetSales kommt aus der Adventure Works Demodatenbank und die Datumsdimension Dim Datum wurde manuell erstellt. Der disconnected Measure Table dient zur Aufnahme sämtlicher Measures (jene aus dem ersten und zweiten Teil der Blogserie sind bereits enthalten):

  • Sales = Sum(‚Fact InternetSales'[SalesAmount])
Die Datumsdimension ist auch hier von zentraler Bedeutung, um die Zeiträume verlässlich ermitteln zu können. Die Deklaration der Tabelle als Date Dimension ist hier – anders als bei der Week-over-Week Berechnung – wieder wichtig, da eine DAX Time Intelligence Funktion für die Lösung der Aufgabenstellung verwendet wird. Die Deklaration erzwingt, daß die Datumsdimension lückenlos sein muß:

3. DAX Lösung

Eine vorgefertigte Time Intelligence Funktion gibt es in Power BI bisher nicht, auch steht kein Quick Measure zur Verfügung. Die Umsetzung in DAX ist aber recht einfach, aus didaktischen Gründen werden sämtliche Ermittlungsparameter als eigenständige Measures angelegt und die Ergebnisse mit dem Card Visual dargestellt.

Der relative Zeitslicer ist hier stellvertretend für jeden beliebigen Betrachtungszeitraum auf Last 7 Days konfiguriert, das aktuelle Datum in dieser Demo ist der 29.03.2019. Im ersten Schritt wird dieser ausgewählte Betrachtungszeitraum als einzelne Measure-Parameter ermittelt, um dann im nächsten Schritt damit rechnen zu können:

  • Start of dynamic Period = FIRSTDATE(‚Dim Datum'[Datum])
  • End of dynamic Period = LASTDATE(‚Dim Datum'[Datum])
  • Days in dynamic Period = DATEDIFF([Start of dynamic Period];[End of dynamic Period];DAY)+1
  • Sales = Sum(‚Fact InternetSales'[SalesAmount])

Das Measure Sales benötigt die Parameter nicht, da sich die Filterung auf den ausgewählten Zeitraum aus dem sogenannten Filter Context ergibt. Weiters ist hier zu beachtend, daß Start- und Enddatum auf Basis des Datumsfeldes aus der Dimensionstabelle ermittelt werden und nicht auf das OrderDate in der Faktentabelle aufgebaut sind (diese Variante wird weiter unten behandelt).

Im nächsten Schritt kann jetzt sehr einfach das Start- und Enddatum der Previous Period ermittelt werden, zur Kontrolle wird auch hier nochmals die Anzahl der Tage des Zeitraums ermittelt:

  • Start of previous Period = [Start of dynamic Period]-[Days in dynamic Period]
  • End of previous Period = [Start of dynamic Period]-1
  • Days in previous Period = DATEDIFF([Start of previous Period];[End of previous Period];DAY)+1

Mit diesen Parametern kann jetzt mit der DAX Time Intelligence Funktion DATESBETWEEN sehr einfach die Kennzahl Sales für den vorgelagerten Zeitraum ermittelt werden. Aus didaktischen Gründen erfolgt diese Ermittlung getrennt von der Period-over-Period Ermittlung, die dann als simple Substraktion der beiden Measures umgesetzt wird:

  • Sales dynamic prev Period =
    CALCULATE(
    [Sales];
    DATESBETWEEN(‚Dim Datum'[Datum];[Start of previous Period];[End of previous Period])
    )
  • Sales dynamic PoP = [Sales] – [Sales dynamic prev Period]
  • Sales dynamic PoP % = DIVIDE([Sales dynamic PoP];[Sales dynamic prev Period])

Die Period-over-Period Ermittlung kann jetzt auf die Attribute der Dimensionen des Datenmodells für Analysen der aktuellen Entwicklung angewendet werden:

4. Vertiefung 1: Level-spezifische Ermittlung

Die Auswertung eines dynamischen Zeitraums auf der Zeitachse wird in der Regel nicht durchgeführt. Werden aber die beiden Ergebnis-Measures dennoch in das Table Visual mit der Tagesachse eingesetzt, ergibt sich eine – in der Regel – unerwünschte Ermittlung der beiden Kennzahlen auf Tagesebene. Da hier ein geänderter Filter Context auf das DAX Statement wirkt, ist die Vorperiode nicht mehr ein Zeitraum von 7 Tagen sondern der Vortag (da die Ermittlung sowohl des Start- als auch Enddatums den jeweiligen Tag der Zeile ergibt):

Die Ermittlung auf Tagesebene kann unter Zuhilfenahme der DAX-Funktion HASONEVALUE unterbunden werden:

  • Sales dynamic prev Period (adv.) =
    If (
    HASONEVALUE(‚Dim Datum'[Datum]);
    Blank();
    [Sales dynamic prev Period] )
  • Sales dynamic PoP (adv.) =
    If (
    HASONEVALUE(‚Dim Datum'[Datum]);
    Blank();
    [Sales dynamic PoP] )

Damit wird die level-spezifische Ermittlung des Measures erreicht:

Die Einschränkung dieser Lösung besteht darin, daß die Konfiguration des Slicers auf Last 1 Days zu keinen Ergebnissen mehr führen würde. Hier muß im Einzelfall abgewogen werden, welche Vorgehensweise am zielführendsten ist. Die Ermittlung könnte weiterführend auch auf anderen zeitlichen Ebenen unterbunden werden.

Hier zusammenfassend die sich ergebende Measure Struktur im Datamodel:

5. Vertiefung 2: Referenz auf die Faktentabelle

Sehr wichtig ist die Frage, ob das Datum aus der Dimensionstabelle oder aus der Faktentabelle verwendet wird, da dies in unvollständig bebuchten Betrachtungszeiträumen zu unterschiedlichen Ergebnissen führt. Wir wechseln dazu den Betrachtungszeitraum im Demo-Dataset zum Ende des bebuchten Zeitraums (das ist der 28.01.2020). Als Filter verwenden wir einen Zeitslider, um wiederum einen Zeitraum von 7 Tagen auszuwählen.

Im Table Visual ist gut zu sehen, daß im ausgewählten Betrachtungszeitraum nur 2 Tage bebucht sind. Da das Start- und Enddatum für die Vorperiodenberechnung in der Dimensionstabelle ermittelt werden, wird exakt der gleiche Zeitraum wie im Slicer abgebildet. Das führt jedoch dazu, daß die Vorperiode aus 7 bebuchten Tagen besteht (Wert = 11.711), während der aktuelle Zeitraum nur aus 2 Tagen besteht (Wert = 4.121) und der Vergleich damit möglicherweise ungültig ist:

  • Start of dynamic Period = FIRSTDATE(‚Dim Datum'[Datum])
  • End of dynamic Period = LASTDATE(‚Dim Datum'[Datum])

Wird das Start- und das Enddatum in der Faktentabelle ermittelt, dann wird als höchstes (bebuchtes) Datum lediglich der 28.01.2020 gefunden und damit ein de-facto Betrachtungszeitraum von 2 Tagen bestimmt:

  • Start of dynamic Period = FIRSTDATE(‚Fact InternetSales'[OrderDate])
  • End of dynamic Period = LASTDATE(‚Fact InternetSales'[OrderDate])

Der Vergleich von 2 Tagen mit 2 vorangegangenen Tagen erscheint zwar richtiger, ist aber trotzdem problematisch. Dem User könnte einerseits der verkürzte Zeitraum möglicherweise nicht bewußt sein oder andererseits könnte es auch erwünscht sein, in jedem Fall exakt den ausgewählten Zeitraum dem Vergleich zugrunde zu legen.

und/oder der Vergleichszeitraum ebenfalls nur lückenhaft bebucht ist und daher immer noch eine unterschiedliche Anzahl an bebuchten Tagen verglichen wird.

Bei Datenbeständen mit fallweise oder regelmäßig auftretenden unbebuchten Tagen könnte es zu der Anforderung kommen, der Ermittlung des Vergleichszeitraums gleich viele bebuchte Tage zugrunde zu legen. Diese interessante Frage wird jedoch in diesem Blogbeitrag nicht mehr behandelt.

Quellen

radacad.com/previous-dynamic-period-dax-calculation

Robert Lochner

Robert Lochner ist seit 2001 als Unternehmer tätig und Gründer der Linearis. Seit seinem Betriebswirtschaftsstudium an der WU Wien ist er als Unternehmer in der BI Branche tätig, ist Trainer für Excel BI und Power BI und unterstützt Unternehmen bei der digitalen Transformation ihrer Business Intelligence Prozesse. Er ist Autor des Linearis Blogs und mehrerer fachlicher Publikationen.

Weitere Beiträge

Kategorien: Analyse, Dashboarding, Power BI im Team nutzen, Power Pivot nutzen, Reporting, SQL Server als DWH nutzen