Bestandsgrößen werden in BI Systemen als semi-additive Measures bezeichnet, da diese in allen Dimensionen summmiert werden dürfen mit Ausnahme der Zeitdimenion. Für die Abbildung in Power BI bzw. in DAX stehen mehrere Lösungsmethoden zur Verfügung, deren Einsatz von der Beschaffenheit der Datensätze in der Faktentabelle abhängt.

Im ersten Teil dieser Blogserie wurden semi-additive Measures im Überblick vorgestellt und in diesem zweiten Teil werden jetzt die Lösungsmethoden für den ersten großen Anwendungsfall beschrieben, nämlich für Faktentabelle die auch alle 0-Datensätze enthalten.

Anwendungsfall 1 mit 0-Datensätzen

Wir arbeiten hier mit einer Faktentabelle, die auch sämtliche 0-Datensätze enthält. Dies ist der einfachere Anwendungsfall und kommt häufig bei Excel als Datenquelle vor.

Lösungsmethode für den Anwendungsfall 1 mit LASTDATE()

Grundsätzlich müssen wir der unbedingten Summierung des Measures eine Bedingung hinzufügen, daher packen wir die Summierung in ein CALCULATE(). Als Bedingung bietet sich die LASTDATE() Tabellenfunktion an, diese liefert im aktuellen Filter Context der konkreten Measure Berechnung eines Datenpunktes das höchste Datum. Auf Tagesebene ist das der Tag selbst, auf Monatsebene der letzte Tag dieses Monats, auf Jahresebene der letzte Tag dieses Jahres usw.

Der folgende Syntax ist naheliegend ...

Closing Balance CASE 1 (LASTDATE Dim) = 
CALCULATE(
    SUM('Fact Balances CASE 1'[Balance]);
    LASTDATE('Dim Dates'[Date])
)

... und würde in allen abgeschlossenenen ("vollen") Perioden perfekt funktionieren, nicht aber in der laufenden ("unvollständigen") Periode:

Eine Syntaxvariation auf das Datumsfeld der Faktentabelle bringt hingegen schon die perfekte Lösung für die Darstellung des semi-additiven Measures in diesem Anwendungsfall:

Closing Balance CASE 1 (LASTDATE Fact) = 
CALCULATE(
    SUM('Fact Balances CASE 1'[Balance]);
    LASTDATE('Fact Balances CASE 1'[Date])
)

Warum ist das so?

Das können wir mit 2 Helper Measures zeigen, mit denen wir uns das Berechnungsergebnis der beiden LASTDATE() Berechnungen auf den jeweiligen Datenpunkten des Visuals verdeutlichen:

LASTDATE Dim = LASTDATE('Dim Dates'[Date])

LASTDATE Fact = LASTDATE('Fact Balances CASE 1'[Date])

Die Berechnung mit Referenz auf die Datumstabelle hat keinen Bezug zum Datenstand in der Faktentabelle und ermittelt "stur" nach Kalender auf Monatsebene den Monatsletzten - also im März den 31.03.2025. Die Berechnung mit Referenz auf die Faktentabelle findet auf Monatsebene lediglich Datensätze bis zum 06.03.2025 und liefert daher dieses Datum auf allen übergeordneten zeitlichen Ebenen:

DAX Hintergrundinformationen:

LASTDATE() wandelt den implizieten Filter Context - bspw. alle Tage eines Monats - auf einen explizit abweichenden Filter Context - bspw. nur der letzte Tag des Monats - ab und sorgt auf diese Weise für die erwünschte "Bestandslogik" für semi-additive Measures.

LASTDATE() ist eine Tabellenfunktion, die eine sogenannte "1-row-1-column" Tabelle als Ergebnis liefert und keinen Skalarwert wie etwa die sehr ähnliche MAX() Funktion. Das ist auch der Grund, warum LASTDATE() direkt in die CALCULATE() Funktion als Filter(tabelle) eingesetzt weden kann und MAX() nur über einen bspw. mittels FILTER() erweiterten Syntax eingesetzt werden kann.

Lösungsmethode für den Anwendungsfall 1 mit LASTNONBLANK()

Aus DAX Sicht eleganter aber auch deutlich aufwendiger ist der Einsatz von LASTNONBLANK() als Filterbedingung:

Closing Balance CASE 1 (LASTNONBLANK) = 
CALCULATE(
    SUM('Fact Balances CASE 1'[Balance]);
    LASTNONBLANK(
        'Dim Dates'[Date]; 
        CALCULATE(SUM('Fact Balances CASE 1'[Balance]))
    )
)

Die Berechnung führt zum exakt gleichen Ergebnis wie die LASTDATE() Berechnung mit Referenz auf die Faktentabelle:

Das Helper Measure zeigt - natürlich - ebenfalls die gleichen Ergebnisse:

LASTNONBLANK = 
    LASTNONBLANK(
        'Dim Dates'[Date]; 
        CALCULATE(SUM('Fact Balances CASE 1'[Balance]))
    )

DAX Hintergrundinformationen:

LASTNONBLANK() ist eine Iteratorfunktion und - ebenso wie LASTDATE() - eine Tabellefunktionen. D.h. das Berechnungsergebnis von LASTNONBLANK() ist eine Tabelle mit genau einem Datum ("1-row-1-column" Tabelle) und kann daher ebenso wie LASTDATE() unmittelbar im CALCULATE() als Filter(tabelle) eingesetzt werden.

Die Ermittlung dieser Ergebnistabelle erfolgt zweistufig als Iterator. In der ersten Stufe wird eine virtuelle Tabelle mit den verschiedenen Datumswerten ("Dim Dates[Date]") aus dem jeweiligen Filter Context des Datenpunktes für die Berechnung des Measures erzeugt. Im zweiten Schritt wird eine virtuelle Calculated Column mit der Summe der Balances des jeweiligen Tages aus der verknüpften Faktentabelle ermittelt - die notwendige "Context Transition" des Row Context der virtuellen Datumstabelle in einen Filter Context für diese Summierung wird durch den CALCULATE() Wrapper bewerkstelligt. Auf diese Weise wird bspw. im März 2025 eine Datumstabelle mit den 31 Tagen erzeugt, aber nur die ersten 6 Tage weisen eine Summe auf. Der letzte Tag dieser Tabelle mit einem Wert ("Last Non Blank") wird dann als Ergebnistabelle zurückgegeben und als Filter angewendet.

Fazit

Der Syntax für Iteratoren in DAX ist zwar sehr kompakt, die Berechnungslogik ist aber für DAX Einsteiger nicht so leicht nachzuvollziehen. Daher bewährt sich in der Praxis der einfachere LASTDATE() Syntax mit Referenz auf die Faktentabelle. Bitte beachte, daß beide Lösungsmethoden nur iVm Faktentabellen garantiert richtige Ergebnisse liefern, die auch alle 0-Datensätze enthalten.