DAX Pattern zur unterjährigen YTD- und Abweichungsberechnung in Power BI

Publiziert von

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.

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, Reporting