Die Verteilung von Jahreswerten auf Monate oder von Monatswerten auf Tage ist eine häufige Anforderung in Power BI.

Die Umsetzung in Power BI kann entweder DAX-basiert oder query-basiert erfolgen. In Teil 1 haben wir gesehen, daß die DAX-basierte Verteilung mit einem relativ einfachem DAX-Statement umgesetzt werden kann. In diesem Teil 2 sehen wir, daß die eigentliche Herausforderung in den darauf aufbauenden Folgeberechnungen wie bspw. einer Year-to-Date oder einer Running Total Berechnung liegt. In Teil 3 betrachten wir dann die query-basierte Lösung, die in vielerlei Hinsicht die praxistauglichste Lösung darstellt.

1. Ausgangssituation

Die auf Jahresebene importierten BUDGET-Daten wurden im Teil 1 auf Basis der Anzahl der Kalendertage auf die Monate verteilt.

Die Year-to-Date Berechnung mit der gewohnt einfachen DAX-Funktion TOTALYTD() führt dabei leider nur zur Ermittlung unsinniger Werte:

BUDGET (allocated) YTD v1 = 
    TOTALYTD(
        [BUDGET (allocated) v1];
        'Dim Dates'[Date]
    )

BUDGET (allocated) YTD v2 = 
    TOTALYTD(
        [BUDGET (allocated) v2];
        'Dim Dates'[Date]
    )

Ebenso führt die Running Total Berechnung mit dem gewohnten DAX Pattern ebenfalls nur Berechnung unsinniger Werte:

BUDGET (allocated) RT v1 = 
    CALCULATE(
        [BUDGET (allocated) v1];
        FILTER(
            ALL('Dim Dates');
            'Dim Dates'[Date] <= MAX('Dim Dates'[Date])
        )
    )

BUDGET (allocated) RT v2 = 
    CALCULATE(
        [BUDGET (allocated) v2];
        FILTER(
            ALL('Dim Dates');
            'Dim Dates'[Date] <= MAX('Dim Dates'[Date])
        )
    )

2. Lösung für die YTD-Berechnung

Die Ursache für die fehlerhafte YTD-Berechnung liegt in der unkontrollierten Kumulation der Tageszähler auf Jahres- und Monatsebene. Zur Veranschaulichung dieses Kumulationseffekts können wir Debugging Measures anlegen:

Days in Year YTD = 
    TOTALYTD(
        [Days in Year];
        'Dim Dates'[Date]
    )

Days in Period YTD = 
    TOTALYTD(
        [Days in Period];
        'Dim Dates'[Date]
    )

Test IsInScope YTD = 
    TOTALYTD(
        [Test IsInScope];
        'Dim Dates'[Date]
    )

Die unsinnigen YTD-Werte für das v1-Measure werden durch die Division durch den kumulierten Jahrestageszähler und die anschließende Multiplikation mit dem kumulierten Monatstageszähler verursacht. Die ISINSCOPE()-Funktion ist nicht kompatibel mit der TOTALYTD()-Funktion und liefert im v2-Measure konstant ein FALSE(), was über die IF-Bedingung zur Anzeige des Jahreswertes über alle 12 Monate führt:

Die Lösung liegt in einer "manuellen Kumulation". Mit dem folgenden DAX-Statement wird bereits die richtige Berechnung auf Monats- und Jahresebene erreicht:

BUDGET (allocated) YTD v3 = 
    TOTALYTD([BUDGET (imported)];'Dim Dates'[Date])
    / [Days in Year]
    * TOTALYTD([Days in Period];'Dim Dates'[Date])

Für die richtige Berechnung auf der Total Ebene wird noch eine Korrektur des Jahrestageszählers benötigt:

Days in Year (v2) = 
VAR var_Letztes_Jahr = YEAR(MAX('Dim Dates'[Date]))

RETURN
    CALCULATE(
        [Days in Year];
        'Dim Dates'[Year]=var_Letztes_Jahr
    )

BUDGET (allocated) YTD v4 = 
    TOTALYTD([BUDGET (imported)];'Dim Dates'[Date])
    / [Days in Year (v2)]
    * TOTALYTD([Days in Period];'Dim Dates'[Date])

Die Ergebnisse im v4-Measure sind jetzt auf allen zeitlichen Ebenen korrekt ...

... und das Measure kann jetzt (endlich) für Visualisierungen verwendet werden:

3. Lösung für die Running Total Berechnung

Die Ursache für die fehlerhafte Running Total Berechnung liegt ebenfalls in der unkontrollierten Kumulation der Tageszähler auf Jahres- und Monatsebene. Zur Veranschaulichung dieses Kumulationseffekts können wir auch hier Debugging Measures anlegen:

Days in Year RT = 
    CALCULATE(
        [Days in Year];
        FILTER(
            ALL('Dim Dates');
            'Dim Dates'[Date] <= MAX('Dim Dates'[Date])
        )
    )

Days in Period RT = 
    CALCULATE(
        [Days in Period];
        FILTER(
            ALL('Dim Dates');
            'Dim Dates'[Date] <= MAX('Dim Dates'[Date])
        )
    )

Test IsInScope RT = 
    CALCULATE(
        [Test IsInScope];
        FILTER(
            ALL('Dim Dates');
            'Dim Dates'[Date] <= MAX('Dim Dates'[Date])
        )
    )

Die unsinnigen RT-Werte für das v1-Measure werden durch die Division durch den kumulierten Jahrestageszähler und die anschließende Multiplikation mit dem ebenfalls kumulierten Monatstageszähler verursacht. Die ISINSCOPE()-Funktion ist nicht kompatibel mit der FILTER()-Funktion und liefert im v2-Measure konstant ein FALSE(), was über die IF-Bedingung zur Anzeige des RT-Wertes über alle 12 Monate führt:

Die Lösung liegt wieder in einer "manuellen RT Berechnung". Die im folgenden gezeigte Variante ist sicherlich nur eine von vielen Lösungsmöglichkeiten: zuerst wird die Summe der importierten Budgetwerte bis zum Vorjahr ermittelt und dazu einfach der YTD-Wert des laufenden Jahres addiert:

BUDGET (allocated) RT v3 = 
VAR var1 = EDATE(ENDOFYEAR('Dim Dates'[Date]);-12)
RETURN
    CALCULATE(
        [BUDGET (imported)];
        FILTER(
            ALL('Dim Dates');
            'Dim Dates'[Date] <= var1
        )
    )
    +
    [BUDGET (allocated) YTD v4]

Funktioniert!

Fazit

Das ist viel Debugging für ansonsten sehr einfache DAX Berechnungen. Es ist leicht zu erkennen, daß jede Folgeberechnung mit einer gewissen Wahrscheinlichkeit zu einem Mehraufwand für die Entwicklung des DAX-Statements führt. Weiters ist im DAX-basierten Ansatz die Transparenz zur Herkunft der berechneten Werte häufig wiederum nur aufwändig zu gewährleisten.

Für mich hat sich in der Praxis rasch die eindeutige Präferenz für die query-basierte Lösung herauskristallisiert, die ich im Teil 3 dieser Blogserie vorstellen werde.