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

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.

Power BI Camp - Präsenztrainings in Wien und Nürnberg!

DAX & Datenmodellierung, Power Query und Dashboarding mit Power BI - als einzelnes Modul oder als ganze Trainingswoche!

Die Termine für 2020 sind bereits abgeschlossen, nächste Termine voraussichtlich im Frühjahr 2021.

Gruppen ab 4 Teilnehmer können auch im Rahmen eines Inhouse Training ausgebildet werden.
Termine 2021
Share on linkedin
Share on twitter
Share on facebook

Leave a Replay

Über den Autor

Blog auf Feedly abonnieren

Kategorien

Verwandte Beiträge

Kostenlos zum Newsletter anmelden

Ihre Anfrage

Schicken Sie uns Ihre Fragen und Anregungen!