KPIs in einer Scorecard werden idR 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 dieser Blogserie geht es nun darum, wie die Week-over-Week (WoW) Berechnung realisiert werden kann, da dafür in Power BI keine Standardfunktion zur Verfügung steht.

Die hier vorgestellten DAX-Funktionen können wiederum 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. Ausgangssituation

Wir verwenden das Datenmodell aus Teil 1: 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 des Basis-Measures (sowie der Measures aus dem ersten Teil der Blogserie):

2. Basislösung für die Week-over-Week Berechnung

Die im ersten Teil verwendete DAX-Funktion DATEADD sieht leider keinen Parameter WEEK vor, eine PREVIOUSWEEK Funktion gibt es auch nicht und deshalb existiert wohl auch kein Quick Measure. Daher muss die Week-over-Week Berechnung mit den Bordmitteln der DAX-Formelsprache umgesetzt werden, der grundsätzliche Lösungsansatz stammt aus diesen beiden Quellen:

• Enterprise DNA: Calculate Previous Weeks Sales - Advanced DAX in Power BI
• www.dutchdatadude.com/ultimate-time-based-calculations-cheat-sheet-for-dax-power-bi-including-week-based-calculations

Die dort vorgestellte (gleiche) Berechnungsmethode geht davon aus, daß es in der Datumsdimension eines Datasets nur Jahre mit der gleichen Anzahl an Kalenderwochen gibt. Das ist in der Praxis in Mehrjahres-Datasets nicht gegeben, daher muss der Lösungsansatz mit einer kleinen Adaption insofern variabilisiert werden, als die höchste Kalenderwoche nicht global sondern für das jeweilige Vorjahr ermittelt werden muß. Zur leichteren Nachvollziehbarkeit wird der Sales Last Week als eigenständiges Measure umgesetzt:

• Sales Last Week =
  VAR CurrentWeek = SELECTEDVALUE('Dim Datum'[KW System 3 (Nr)])
  VAR CurrentYear = SELECTEDVALUE('Dim Datum'[Jahr])
  VAR MaxWeekNumberLY = CALCULATE(
  MAX('Dim Datum'[KW System 3 (Nr)]);
  'Dim Datum'[Jahr]=CurrentYear-1;
  ALL('Dim Datum'))RETURN
  SUMX(
  FILTER(ALL('Dim Datum');
  IF(CurrentWeek=1;
  'Dim Datum'[KW System 3 (Nr)] = MaxWeekNumberLY && 'Dim Datum'[Jahr]=CurrentYear-1;
  'Dim Datum'[KW System 3 (Nr)] = CurrentWeek-1 && 'Dim Datum'[Jahr]=CurrentYear));
  [Sales]
  )

Die Ermittlung der Week-over-Week Berechnung ist jetzt nur noch eine einfache Substraktion bzw. Division:

• Sales WoW =
  [Sales] - [Sales Last Week]
• Sales WoW % =
  DIVIDE([Sales WoW];[Sales Last Week])

Die Berechnungsergebnisse können wiederum am effektivsten mit dem Matrix Visual auf der Zeitachse plausibilisiert werden:

Wie im Screenshot zu sehen ist, funktioniert die dynamische Ermittlung des Vorwochenwertes nicht nur während des Jahres sondern auch beim Jahreswechsel einwandfrei, da die letzte Woche des Vorjahres (KW 52 oder KW 53) für die Ermittlung in der KW 01 dynamisch ermittelt wird. In der allerersten Kalenderwoche des Datasets (hier: KW 01 im Jahr 2016) wird für den Vorwochenwert ein BLANK (und kein Fehler) ermittelt, auch das funktioniert also automatisch richtig.

Unzureichend ist aber, daß die absolute WoW-Berechnung in der KW 01 des ersten Jahres einen unbrauchbaren Betrag ermittelt, weil es zwar einen Wert für Sales nicht aber für Sales Last Week gibt. Weiters findet diese Berechnung auch auf Jahres- und Total-Ebene statt, das ist genauso wenig wie die Berechnung eines "Absturzes" aller 3 Measures in der Woche nach der letzten bebuchten Kalenderwoche (hier: KW 06 im Jahr 2018) sinnvoll.

3. Optimierung der Week-over-Week Berechnung

Die beschriebenen Problemzonen der Basislösung können durch die (schon aus dem ersten Teil dieser Blogserie bekannten) beiden DAX-Mechanismen gelöst werden:

1. Beschränkung der Berechnung auf den Zeitraum zwischen erster und letzter IST-Buchung
2. Beschränkung der Berechnung auf die zeitliche Ebene "Woche"

Zur Umsetzung des ersten Mechanismus werden 2 Hilfs-Measures zur Ermittlung des Min- und Max-Datums angelegt, da eine einfache Begrenzung auf bebuchte Perioden in der Praxis nicht ausreicht (wie bereits hier beschrieben). Die DAX-Funktion ALL ist dabei entscheidend, um die Ermittlung unabhängig vom sogenannten Filter Kontext zu machen. Alternativ könnten die beiden Measures auch als Variablen innerhalb der folgenden Measures ermittelt werden, der Nachteil wäre dann aber, daß das gleiche Statement in mehreren Measures wiederholt werden müßte (da es in DAX bisher keine globalen Variablen gibt):

• Info MIN-Date with Facts =
  CALCULATE(
  MIN('Fact InternetSales'[OrderDate]);
  ALL('Fact InternetSales')
  )
• Info MAX-Date with Facts =
  CALCULATE(
  MAX('Fact InternetSales'[OrderDate]);
  ALL('Fact InternetSales')
  )

Die oben vorgestellten Basis-Measures werden jetzt mit einem IF-Statement an die Bedingung zur "Ermittlung nur bis zur letzten bebuchten Woche" (= MAX, gekoppelt mit LOOKUPVALUE, um aus dem Max-Datum die Max-Woche nachzuschlagen) geknüpft:

• Sales Last Week =
  VAR WeekWithFactsMAX = LOOKUPVALUE('Dim Datum'[KW System 3 mit Jahr (Nr)];'Dim Datum'[Datum];[Info MAX-Date with Facts])
  VAR CurrentWeek = SELECTEDVALUE('Dim Datum'[KW System 3 (Nr)])
  VAR CurrentYear = SELECTEDVALUE('Dim Datum'[Jahr])
  VAR MaxWeekNumberLY = CALCULATE(
  MAX('Dim Datum'[KW System 3 (Nr)]);
  'Dim Datum'[Jahr]=CurrentYear-1;
  ALL('Dim Datum'))RETURN
  IF(
  MAX('Dim Datum'[KW System 3 mit Jahr (Nr)]) <= WeekWithFactsMAX;
  SUMX(
  FILTER(ALL('Dim Datum');
  IF(CurrentWeek=1;
  'Dim Datum'[KW System 3 (Nr)] = MaxWeekNumberLY && 'Dim Datum'[Jahr]=CurrentYear-1;
  'Dim Datum'[KW System 3 (Nr)] = CurrentWeek-1 && 'Dim Datum'[Jahr]=CurrentYear));
  [Sales]
  );
  BLANK()
  )

Für die WoW-Berechnung muß das IF-Statement um eine MIN-Bedingung sowie um eine Bedingung zur "Ermittlung nur auf Wochenebene" (-> DISTINCTCOUNT) erweitert werden:

• Sales WoW =
  VAR WocheBebuchtMIN = LOOKUPVALUE('Dim Datum'[KW System 3 mit Jahr (Nr)];'Dim Datum'[Datum];[Info MIN-Date with Facts])
  VAR WocheBebuchtMAX = LOOKUPVALUE('Dim Datum'[KW System 3 mit Jahr (Nr)];'Dim Datum'[Datum];[Info MAX-Date with Facts])RETURN
  IF(
  Min('Dim Datum'[KW System 3 mit Jahr (Nr)]) > WocheBebuchtMIN
  && Max('Dim Datum'[KW System 3 mit Jahr (Nr)]) <= WocheBebuchtMAX
  && DISTINCTCOUNT('Dim Datum'[KW System 3 mit Jahr (Nr)])=1;
  [Sales] - [Sales Last Week];
  Blank()
  )

An der Ermittlung der Sales WoW% ändert sich nichts:

• Sales WoW % = DIVIDE([Sales WoW];[Sales Last Week])

Hier die fertigen Berechnungsergebnisse der Measures, die Berechnung startet jetzt verlässlich erst in der zweiten bebuchten Kalenderwoche (hier. KW 2 2016), erfolgt jetzt nur noch auf Wochenebene und endet in der letzten bebuchten Kalenderwoche (hier: KW 5 2019):

Ausbaupotentiale

Die "Blanks" in den ersten Perioden sowie auf Jahres- und Total-Ebene könnten noch mit einem sprechenden Text wie "(select valid period)" ausgestattet werden und die WoW-Measures könnten mit einer vollständigen +/- Vorzeichenformatierung ausgestattet werden, wie in diesem Blogbeitrag gezeigt. In beiden Fällen ändert sich das Format des Measures jedoch von Zahl auf Text, was gewisse Folgethemen aufwirft.

Ein Praxisthema ist auch die Ermittlung der letzten abgeschlossenen Woche, um die Berechnung auch für die laufende (unvollständige) Woche zu unterdrücken. Die Regel dazu kann nur anwendungsspezifisch entwickelt werden.

Weiterführende Quellen

www.sqlbi.com/articles/week-based-time-intelligence-in-dax

www.dutchdatadude.com/ultimate-time-based-calculations-cheat-sheet-for-dax-power-bi-including-week-based-calculations