Im letzten Beitrag wurde die Visualisierung von Abweichungen mit dem Zebra BI Custom Visual behandelt. Im (Normal-)Fall einer unterjährigen Gegenüberstellung von IST und PLAN ist jedoch die Visualisierung nur so gut, wie die zugrundeliegenden Measures. Liegen die PLAN-Werte für 12 Monate und die IST-Werte beispielsweise erst für 4 Monate vor, dann führen weder eine simple Subtraktion von IST- minus PLAN-Werten noch die Standard YTD-Berechnung zu aussagekräftigen Visualisierungen.

In diesem Beitrag wird gezeigt, wie mit einem DAX-Pattern zur Limitierung der Berechnungen bis zum höchsten IST-Monat ("MAX-Monat") eine perfekte Abweichungsermittlung gewährleistet wird. Die Visualisierung der Abweichungen in diesem Beitrag wurden wieder mit dem Custom Visual Zebra BI Power Charts erstellt.

1. Ausgangssituation und Standard YTD-Berechnung in Power BI

Typischerweise steht einer 12-Monatsreihe an PLAN-Werten im laufenden Jahr eine geringere Anzahl an IST-Monaten gegenüber. Bei der Ermittlung der Abweichung zwischen IST und PLAN wird bereits die zu lösende Herausforderung erkennbar, da die Abweichung nur bis zum laufenden IST-Monat Sinn macht:

• Sales ∆ ACT-BUD = [Sales ACT]-[Sales BUD]

Die Ermittlung der YTD-Werte für IST und PLAN mit den Time Intelligence Funktionen der Formelsprache DAX ist sehr leistungsfähig, im Fall unterjährigen Berechnung tritt aber ebenfalls das Problem der Nicht-Vergleichbarkeit der kumulierten Werte nach dem letzten IST-Monat auf:

• Sales ACT YTD = TOTALYTD([Sales ACT]; 'DIM Date'[Date])
• Sales BUD YTD = TOTALYTD([Sales BUD]; 'DIM Date'[Date])

2. Verbesserung der YTD- und Abweichungsberechnung durch Limitierung auf die IST-Perioden

2.1 Nicht Empfohlen: Limitierung mittels einer Bedingung auf Actuals <> 0

Von "Excel kommend" ist es erst mal naheliegend, auf die "benachbarte Spalte" der IST-Werte zu referenzieren, und die kumulierten Werte nur anzuzeigen, wenn es auch Actuals <> 0 in der aktuellen Periode gibt. In der DAX-Formelsprache kann diese Bedingung so umgesetzt werden:

• Sales BUD YTD (too simple 1) =
  IF(
  [Sales ACT] = 0 || [Sales ACT] = BLANK();
  BLANK();
  [Sales BUD YTD]
  )
• Sales ∆ ACT-BUD (too simple 1) =
  IF(
  [Sales ACT] = 0 || [Sales ACT] = BLANK();
  BLANK();
  [Sales ACT]-[Sales BUD]
  )

Dieser Lösungsansatz hat 2 bedeutende Schwachstellen und ist daher nicht empfehlenswert: (1) gibt es in einer Periode "zwischendurch" keine IST-Werte, so setzt auch die Anzeige der BUD-YTD-Werte aus und (2) wird auf Jahresebene weiterhin der kumulierte 12-Monatswert angezeigt.

Hinweis: in dieser und in den folgenden Abbildungen wurde das Visual auf eine einzelne Produktgruppe gefiltert, um anhand der 0-Periode März 2018 die Berechnungen prüfen zu können.

2.2 Nicht Empfohlen: Limitierung mittels NOW-Funktion

Eine mögliche nächste Idee könnte sein, die Anzeige der YTD-Werte auf den heutigen Tag zu begrenzen, damit werden zumindest die unbebuchten Vormonate angezeigt:

• Sales BUD YTD (too simple 2a) =
  IF(
  MAX('Fact Sales'[Date]) > NOW();
  BLANK();
  TOTALYTD([Sales BUD];'DIM Date'[Date])
  )
• Sales ∆ ACT-BUD (too simple 2a) =
  IF(
  MAX('Fact Sales'[Date])>Now();
  BLANK();
  [Sales ACT]-[Sales BUD]
  )

Dieser Lösungsansatz hat 3 bedeutende Schwachstellen und ist daher ebenfalls nicht empfehlenswert: (1) der heutige Tag ist idR kein verlässlicher Begrenzungswert (hier: Juni 2018, IST-Daten liegen aber nur bis April 2018 vor),  (2) es werden keine Jahres- und Gesamtwerte mehr angezeigt und (3) es wird die Null-Periode August 2018 sichtbar.

Die Problematik #2 kann behoben werden, indem in obigen DAX-Statements anstelle der MIN-Funktion die MAX-Funktion verwendet wird. Allerdings wird dann auf Jahres- und Gesamtebene wieder nur der 12-Monatswert angezeigt. Problematik #3 kann behoben werden, indem in obigen DAX-Statements anstelle des Datumsfeldes aus der Faktentabelle jenes aus der Datumsdimension verwendet wird (da die MIN-/MAX-Abfrage in der Faktentabelle in diesem Fall ein BLANK liefert und die Bedingung nicht geprüft werden kann).

2.3 Best Practice: Limitierung mittels MAX-Datum (der Actuals)

Wie gezeigt, können weder die Bedingung Actuals <> 0 noch der Bezug auf den heutigen Tag eine belastbare Lösung liefern. Die Ermittlung des höchsten im IST-Datenbestand gefundenen Datums ist hingegen ein sehr stabiles Kriterium zur Limitierung von unterjährigen Abweichungs- und YTD-Berechnungen und kann mit folgendem DAX-Statement (als eigenes Measure) ermittelt und beispielsweise mit einem Card-Visual auch gleich angezeigt werden:

• Info Highest Date (ACT) =
  CALCULATE(
  Max('Fact Sales'[Date]);
  ALL('Fact Sales');
  'Fact Sales'[ACTUAL Sales]<>0
  )

Hinweis: im vorliegenden Demo-Datenbestand sind sämtliche IST- und PLAN-Werte auf den 1. des jeweiligen Monats gebucht, daher wird der 1.4.2018 als MAX-Datum geliefert.

Dieses MAX-Datum kann jetzt über eine Variable in ein CALCULATE-Statement als Filterkriterium übergeben werden:

• Sales BUD YTD (LIM-year) =
  VAR
  var1 = [Info Highest Date (ACT)]
  RETURN
  CALCULATE(
  [Sales BUD YTD];
  'Fact Sales'[Date] <= var1
  )
• Sales ∆ ACT-BUD YTD (LIM-year) =
  [Sales ACT YTD] - [Sales BUD YTD (LIM-year)]

Damit wird zweierlei erreicht:

1. Die Variable bewirkt, daß das MAX-Datum aus dem "Scalar Table"-Format in eine Konstante transformiert wird und daher im Calculate Statement direkt als Filterbedingung verwendet werden kann (alternativ könnte ein FILTER-Statement in das CALCULATE eingesetzt werden, siehe unten)
2. Das CALCULATE bewirkt - anders als das IF-Statement in den nicht empfohlenen Varianten - eine Filterung auf die Perioden bis zum MAX-Datum auch in den Folge- und damit in der übergeordneten Jahresperiode.

Die Fortschreibung des limitierten YTD-Wertes auf die Folgeperioden kann Anwendungsfälle haben, in vielen Fällen ist aber eine Limitierung der Anzeige nur bis zum MAX-Datum erwünscht:

• Sales ACT (LIM-month) =
  VAR
  var1 = [Info Highest Date (ACT)]
  RETURN
  If(MIN('Fact Sales'[Date])<=var1;
  [Sales ACT];
  Blank()
  )
• Sales BUD YTD (LIM-month) =
  VAR
  var1 = [Info Highest Date (ACT)]
  RETURN
  If(MIN('DIM Date'[Date])<=var1;
  CALCULATE(
  [Sales BUD YTD];
  'Fact Sales'[Date] <= var1);
  Blank()
  )
• Sales ∆ ACT-BUD YTD (LIM-month) =
  [Sales ACT YTD (LIM-month)] - [Sales BUD YTD (LIM-month)]

Die Ermittlung der Werte ist jetzt auf allen zeitlichen Ebenen korrekt, der Jahres- und Gesamtwert zeigt jetzt sowohl für IST als auch PLAN nur noch den kumulierten Wert der ersten 4 Monate ...

... ein derart optimiertes Measures ermöglicht jetzt die perfekte Visualisierung der unterjährigen YTD- und Abweichungsermittlung:

Alternativ kann auch eine simplere DAX-Lösung mit einem FILTER-Statement verwendet werden, dieses ermöglicht die direkte Verwendung des MAX-Datum im "Scalar Table"-Format als Filterbedingung:

• Sales BUD YTD (LIM-month) v2 =
  CALCULATE(
  [Sales BUD YTD];
  FILTER(
  'DIM Date';
  'DIM Date'[Date] <= [Info Highest Date (ACT)])
  )

Fazit

Die Visualisierungen in Power BI sind immer nur so gut wie die zugrundeliegende Ermittlung der Measures - umgekehrt können mit perfekten Measures auch perfekte Visualisierungen erzeugt werden. Die Verwendung des hier vorgestellten DAX-Patterns ermöglicht es, diese Potentiale zu nutzen ohne selbst viel DAX Know-How aufbauen zu müssen.