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, im zweiten Teil wurden die Lösungsmethoden für die Anwendungsfall 1 ("mit 0-Datensätzen") vorgestellt und in diesem dritten und letzten Teil werden die Lösungsmethoden für den zweiten Anwendungsfall ohne 0-Datensätze beschrieben.

Anwendungsfall 2 ohne 0-Datensätze

Wir arbeiten hier mit einer Faktentabelle, die für die 0-Werte keine Datensätze enthält. Dies ist der anspruchsvollere Anwendungsfall und kommt häufig bei Datenbanken als Datenquelle vor.

Lösungsmethode für den Anwendungsfall 2 mit LASTDATE() oder LASTNONBLANK() - funktionieren beide nicht

Die Lösungsmethode aus Anwendungsfall 1 ("mit 0-Datensätze") funktioniert hier leider nicht ...

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

... da die BLANK() Werte am relevanten Stichtag nicht als 0-Werte interpretiert werden sondern der letzte verfügbare Wert innerhalb der Periode - egal von welchem Stichtag - angezeigt wird. Die Berechnung des Measures liefert damit ein Gemisch aus zutreffenden und nicht zutreffenden Werten:

Die Ursache liegt darin, daß das LASTNONBLANK() Datum hier inhaltlich betrachtet nicht zutreffend ist und es zu stark einzelfallbezogenen Darstellung kommt. Das ist wiederum gut mit dem Helper Measure nachvollziehbar:

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

Lösungsmethode für den Anwendungsfall 2 mit ALL()

Die Herausforderung bei diesem Anwendungsfall liegt darin, möglichst robust den relevanten Stichtag innerhalb der Periode zu ermitteln.

Die bedingte Summierung mit dem weiter unter ermittelten "Highest Date CASE 2" ist dabei nur Formsache ...

Closing Balance CASE 2 (Highest Date) = 
VAR var_HighestDate = [Highest Date CASE 2]
RETURN
CALCULATE(
    SUM('Fact Balances CASE 2'[Balance]);
    'Dim Dates'[Date] = var_HighestDate
)

... die Darstellung auf den aggregierten zeitlichen Ebenen ist jetzt absolut richtig:

Die Herausforderung liegt in der Ermittlung dieses "Highest Date". Die Berechnung muß einerseits variabel sein bezüglich der zeitlichen Periode, der Einfluß der Company wird aber mit einem ALL() gezielt ausgeschalten:

Highest Date CASE 2 = 
CALCULATE(
    MAX('Fact Balances CASE 2'[Date]);
    ALL('Fact Balances CASE 2'[Company])
)

Dies führt dazu, daß in allen 5 Gesellschaften auf allen zeitlichen Ebenen der gleiche relevante Stichtag ermittelt wird und daher die fallweisen BLANK() Werte auch auf die aggregierte zeitliche Ebene "geholt" wird und damit die Darstellung richtig wird:

DAX Hintergrundinfos:

In der Praxis liegt die Herausforderung darin, mittels ALL() Statements die richtigen Dimensionstabellen und ggfs. Faktenattribute aus dem Filter Context der Berechnung zu eliminieren und den Filter Context der gewollten Dimensionen aufrecht lässt.

Fazit

Bei näherer Betrachtung lässt sich feststellen, daß die hier gezeigte Lösungsvariante mit CALCULATE() und ALL() sich nicht nur auf den Anwendungsfall 2 ("ohne 0-Datensätze") sondern auch auf den Anwendungsfall 1 ("mit 0-Datensätze") anwenden lässt. Dennoch haben die Lösungen mit LASTDATE() und LASTNONBLANK() im Anwendungsfall 1 ein starke Berechtigung, da die einfachere Syntax im simpleren Anwendungsfall 1 auch einfacher nachvollziehbar ist.

Generell lässt sich sagen, daß sich bei der Berechnung semi-additver Measures sich alles um die Ermittlung des relevanten Stichtags auf den übergeordneten zeitlichen Ebenen dreht. Grundsätzlich also eine einfach Sache, aber wie immer in der Praxis liegt die Herausforderung immer wieder im Detail und in der konkreten Umsetzung. :)