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 diesem Teil 1 sehen wir, daß die DAX-basierte Verteilung mit einem relativ einfachem DAX-Statement umgesetzt werden kann. In Teil 2 stellt sich dann heraus, 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 die query-basierte Lösung, die in vielerlei Hinsicht die praxistauglichste Lösung darstellt.

1. Ausgangssituation

Die IST-Daten liegen auf Monatsebene vor, die BUDGET-Daten als Jahreswerte. Diese Jahreswerte sollen auf Basis der Anzahl der Kalendertage auf Monate verteilt werden:

Das Datenmodell ist ein sehr simples Star Schema mit 1 Faktentabelle und nur 1 Dimensionstabelle - letztere wurde auch als Date Table zur Nutzung der Time Intelligence DAX-Funktionen definiert. Die Measures sind - wie in all unseren Showcases - in einem (ansonsten leeren) Measure Table eingehängt:

Die Faktentabelle enthält Datensätze für IST- als auch BUDGET-Daten, die Trennung erfolgt über die Spalte Scenario:

Die IST- und BUDGET-Werte werden über 2 Measures getrennt ausgewertet, damit es zu keiner Summierung der beiden Größen kommen kann:

ACTUAL = 
    CALCULATE(
        SUM('Fact Sales'[Value]);
        'Fact Sales'[Scenario]="ACTUAL"
    )

BUDGET (imported) = 
    CALCULATE(
        SUM('Fact Sales'[Value]);
        'Fact Sales'[Scenario]="BUDGET"
    )

2. DAX-basierte Lösung zur Budgetverteilung

Sicherlich gibt es mehrere Möglichkeiten, um die Budgetwerte auf die Monate zu verteilen. Da die BUDGET-Werte auf den 1. Jänner des Jahres zugeordnet sind ist es naheliegend, diesen Werte mit der DAX-Funktion TOTALYTD() auf die Folgemonate fortzuschreiben, um mit diesem fortgeschriebenen Wert dann die Aliquotierung vorzunehmen:

BUDGET (allocated) v0 = 
    TOTALYTD(
        [BUDGET (imported)];
        'Dim Dates'[Date]
    )

Danach wird die Anzahl der Tage des jeweiligen Jahres sowie die Anzahl der Tage im aktuellen Monat ermittelt:

Days in Year = 
    CALCULATE(
        COUNTROWS('Dim Dates');
        ALLEXCEPT('Dim Dates';'Dim Dates'[Year])
    )

Days in Period = COUNTROWS('Dim Dates')

Jetzt kann die Berechnung der aliquotierten Monatwerte erfolgen:

BUDGET (allocated) v1 = 
    [BUDGET (allocated) v0]
    / [Days in Year] 
    * [Days in Period]

Bitte beachten Sie, daß die gesamte Berechnung auch in einem einzigen Measure erfolgen kann - wir haben hier die Einzelteile lediglich zur Erhöhung der Verständlichkeit als eigenständige Measures angelegt.

Die Berechnung ist schon beinahe richtig, im Säulendiagramm ist die Schwäche nicht zu sehen aber in der Tabelle schon: die Gesamtsumme stimmt noch nicht. Dies deshalb, weil die Funktion TOTALYTD() auf Total-Ebene den kumulierten Wert des letzten Jahres und nicht die Gesamtsumme ausgibt:

Die Ermittlung auf der Total-Ebene kann mit einer ISINSCOPE()-Bedingung recht einfach nach einer alternativen Berechnung erfolgen:

BUDGET (allocated) v2 = 
IF(
    ISINSCOPE('Dim Dates'[Year]);
    [BUDGET (allocated) v0]
    / [Days in Year] 
    * [Days in Period];
    [BUDGET (imported)]
)

Fertig:

Um die DAX-Funktion ISINSCOPE() vollständig austesten zu können, wird am einfachsten ein Hilfsmeasure angelegt:

Test IsInScope = 
    IF(
        ISINSCOPE('Dim Dates'[Year]);
        TRUE();
        FALSE()
    )

Das Schöne in DAX ist, daß die Berechnung auf Anhieb auch auf allen anderen zeitlichen Ebenen - hier Quartale und Tage - funktioniert:

Fazit

Die Aliquotierung der Jahres- auf Monatswerte ist mit einem relativ einfachen DAX-Statement möglich.

Im Teil 2 dieser Blogserie werden wir sehen, daß die eigentliche Herausforderung in den darauf aufbauenden Folgeberechnungen wie bspw. einer Year-to-Date oder einer Running Total Berechnung liegt.