Month-to-Date in Detailtabellen: Teil 2 Fortlaufende Summe

Die Month-to-Date Berechnung mit den DAX-Funktionen TOTALMTD()- oder DATESMTD() ist wirklich sehr einfach zu nutzen. Interessant wird es aber, wenn diese Kumulationsberechnung in eine Tabelle mit Einzelbuchungen eingesetzt wird. In diesem Fall führt der angewendete Filter Context zu einem für viele Anwender unerwartetem Berechnungsergebnis.

In Teil 1 dieser Blogserie haben wir die Fragestellung beleuchtet und gezeigt, warum die normale MTD-Berechnung nicht zum gewünschten Ergebnis führen kann. In Teil 2 zeigen wir jetzt einen Lösungsansatz für die Berechnung einer monatlich fortlaufenden Summe in einer Detailtabelle.

Ausgangssituation

Wir fügen dem Datenmodell einen Calculated Table für die Order Numbers hinzu und verknüpfen diesen mit einer 1:n Beziehung mit der Faktentabelle:

Der Calculated Table wird mit dem folgenden DAX Statement dynamisch aus der Faktentabelle erzeugt:

Dim Orders = 
    ADDCOLUMNS(
        VALUES('Fact Sales'[Order Number]);
        "Order No"; RIGHT([Order Number];5)
    )

Hier ein Auszug aus der Tabelle:

Wir werden weiter unten im Blogbeitrag über die Gründe der Anlage dieser zusätzlichen Tabelle diskutieren.

Lösungsvariante 1: Verbesserung der DATESMTD()-Kumulation

Eine erste Verbesserung der in Teil 1 festgestellten "Aufblähung" der Detailliste kann durch ein Abfedern der MTD-Berechnung mit einer IF-Bedingung erreicht werden:

Quantity MTD v1 = 
    IF(
        [Quantity] <> BLANK(); 
        TOTALMTD(
            [Quantity];
            'Dim Dates'[Date]
        )
        ;
        BLANK()
    )

Gegenüber der linken Tabelle werden jetzt in der rechten Tabelle nur noch die Datensätze mit Verkäufen berechnet:

Die MTD-Berechnung kann jetzt mit mehreren ALL()-Statements gegenüber dem Filter Context der Detailtabelle immunisiert werden:

Quantity MTD v2 = 
    IF(
        [Quantity] <> BLANK(); 
        CALCULATE(
            [Quantity];
            DATESMTD('Dim Dates'[Date]);
            ALL('Dim Employees');
            ALL('Dim Customers');
            ALL('Dim Orders');
            ALL('Fact Sales'[ID])
        )
        ;
        BLANK()
    )

Die verbesserte Berechnung funktioniert wunderbar, solange es nur eine Zeile pro Tag in der Detailliste gibt ...

... sobald aber das Feld Order Number der Detailliste hinzugefügt wird, wird die Schwäche dieser Berechnungsmethode sichtbar:

Lösungsvariante 2: Inviduelle Kumulationsfunktion

Eine mögliche Lösung besteht in einer individuellen Kumulationsfunktion bestehend aus zwei Berechnungskomponenten:

  1. Mit der ersten Berechnung wird die undifferenzierte MTD-Summe bis zum Vortag ermittelt
  2. Mit der zweiten Berechnung wird das Running Total / Cumulative Total innerhalb des aktuellen Tages nach aufsteigenden Order Numbers ermittelt

Wir haben hier eine wichtige Prämisse in unserem Datenmodell, die diese 2-teilige Berechnung notwendig macht: die Order Numbers sind nämlich nicht chronologisch aufsteigend. Daher kann die Order Number zwar zur Aufgliederung eines einzelnen Tages verwendet werden, nicht aber zur chronologisch richtigen Berechnung über alle Tage des Monats. Wäre ein solches Feld vorhanden, könnte die MTD Berechnung mit der zweiten Berechnungskomponente alleine erreicht werden. Der Vorteil dieser komplizierteren 2-teiligen Berechnung hier liegt darin, daß die Berechnung auch dann funktioniert, wenn überhaupt keine Order Number o.ä. verfügbar ist und die Tage nach Kunden, Verkäufern, Artikel usw. aufgegliedert werden.

Die erste Berechnungskomponente für die MTD-Summe bis zum Vortag beruht auf der Lösungsvariante 1 und ersetzt lediglich die DATESYTD()-Funktion durch eine individuell gestaltete DATESBETWEEN()-Funktion mit Bezug auf den höchsten Tag:

Quantity MTD v3a (until previous day) = 
VAR var_LatestDate = 
    MAX('Dim Dates'[Date])

VAR var_MTD_before_LatestDate = 
    CALCULATE(
        [Quantity];
        DATESBETWEEN(
            'Dim Dates'[Date];
            DATE(YEAR(var_LatestDate);MONTH(var_LatestDate);1);
            var_LatestDate-1
        );
        ALL('Dim Employees');
        ALL('Dim Customers');
        ALL('Dim Orders');
        ALL('Fact Sales'[ID])
    ) 

RETURN
    IF(
        [Quantity] <> BLANK();
        var_MTD_before_LatestDate;  
        BLANK()
    )

Beachten Sie, daß die Berechnung des höchsten Tages mit der MAX()-Funktion im aktuellen Filter Context der jeweiligen Zelle des Table Visuals erfolgt (und damit relativ ist):

Die zweite Berechnungskomponente folgt dem allgemeinen DAX Pattern zur Running Total / Cumulative Total Berechnung, hier innerhalb des aktuellen Tages nach aufsteigenden Order Numbers. Der Kern der Berechnung ist die Filtertabelle für die Order Numbers, diese wird aus Übersichtlichkeitsgründen in einer eigenen Variable ermittelt und dann als Parameter in das CALCULATE()-Statement eingesetzt:

Quantity MTD v3b (current day) = 
VAR var_LatestDate = 
    MAX( 'Dim Dates'[Date] )

VAR var_FilterTable =
        ALL('Dim Orders');
        'Dim Orders'[Order No] <= MAX ('Dim Orders'[Order No])
        // Der Vergleich funktioniert nur mit einer Zahl und nicht mit einem Text
    )

VAR var_RT_LatestDate=  
    CALCULATE (
        [Quantity];
        var_FilterTable;
        ALL('Dim Employees');
        ALL('Dim Customers');
        ALL('Fact Sales'[ID]);
        'Dim Dates'[Date] = var_LatestDate
    )

RETURN
    IF(
        [Quantity] <> BLANK();
        var_RT_LatestDate;  
        BLANK()
    )

Die Berechnung der Filtertabelle ist auch der Grund, warum eingangs der Calculated Table für die Dim Orders angelegt wurde, weil die direkte Verwendung des Faktenfeldes Order Number einerseits den Vergleich "<=" in der Running Total Berechnung nicht unterstützen würde und andererseits die Performance der Berechnung zusammenbrechen würde.

Beachten Sie, daß bei dem gewählten Artikel der Monatsletzte (31.03.2018) keinen Verkauf hat und daher die Berechnung auf Total-Ebene ein BLANK() liefern muß, damit die Berechnung richtig ist:

Jetzt können die beiden Komponenten in ein einziges Measures zusammengeführt werden:

Quantity MTD v3 = 
VAR var_LatestDate = 
    // Wird hier das Order Date aus der Fact Sales eingesetzt, bricht die Performance zusammen
    MAX('Dim Dates'[Date])

VAR var_MTD_before_LatestDate = 
    CALCULATE(
        [Quantity];
        DATESBETWEEN(
            'Dim Dates'[Date];
            DATE(YEAR(var_LatestDate);MONTH(var_LatestDate);1);
            var_LatestDate-1
        );
        ALL('Dim Employees');
        ALL('Dim Customers');
        ALL('Dim Orders');
        ALL('Fact Sales'[ID])
    ) 

VAR var_FilterTable =
    // Wird hier die Order Number aus der Fact Sales eingesetzt, bricht die Performance zusammen
    FILTER (
        ALLSELECTED('Dim Orders');
        'Dim Orders'[Order No] <= MAX ('Dim Orders'[Order No])
        // Der Vergleich funktioniert nur mit einer Zahl und nicht mit einem Text
        // Das Feld Order Number muß im ALL()-Statement enthalten sein, weil es im Table Visual verwendet werden soll)
    )

VAR var_SUM_LatestDate =  
    CALCULATE (
        [Quantity];
        var_FilterTable;
        ALL('Dim Employees');
        ALL('Dim Customers');
        ALL('Fact Sales'[ID]);
        'Dim Dates'[Date] = var_LatestDate
    )
    
RETURN
    IF(
        [Quantity] <> BLANK();
        var_MTD_before_LatestDate + var_SUM_LatestDate;  
        BLANK()
    )

FERTIG ist die fortlaufende Summe :)

Evaluierung

Die vorgestellten Lösungsvarianten sind nur 2 von vielen möglichen Lösungswegen für diese unerwartet schwierige Berechnung. In beiden Lösungsvarianten kann übrigens die Berechnung durch (differenzierte) Verwendung von ALLSELECTED() anstatt von ALL() je nach Anforderung kontextsensitiver gemacht werden.

Der Vorteil der Lösungsvariante 1 ist, daß diese sehr einfach umzusetzen ist, aber auch nur dort in Detaillisten eingesetzt werden kann, in denen nicht auf Tagesebene aufgegliedert wird.

Die Lösungsvariante 2 ist differenzierter zu evaluieren:

  • Die Berechnung liefert das gewünschte Ergebnis.
  • Das DAX Statement ist in Abhängigkeit von der gewählten Darstellung in der Detailliste zu gestalten.
    d.h. bei Veränderungen der Darstellung muß auch das DAX Statement angepaßt werden.
  • Weiters muß die Detailliste richtig sortiert sein.
    Das ist keine Besonderheit dieser Berechnung sondern gilt für sämtliche DAX Berechnungen dieser Art. Nach dem geltenden Prinzip des FILTER CONTEXT wird jedes Ergebnis vollkommen unabhängig von den Ergebnissen in den Nachbarzellen ermittelt, es gibt in DAX also kein Konzept der Nachbarzelle. Auch die Summe auf der Total-Ebene wird völlig autonom von den darüberstehenden Einzelwerten ermittelt.
  • Weiters ist die Berechnung spürbar langsam, es gibt eine Performance Thematik.
    Möglicherweise könnte dies durch eine Syntax Optimierung gelöst werden ...

Offene Fragen

Einerseits stellt sich die Frage, ob das gleiche Ergebnis nicht auch mit einem einfachereren DAX Statement erreicht werden könnte.

Andererseits ist mir unklar, warum die Performance des Measures völlig zusammenbricht, wenn anstelle des Calculated Tables Dim Orders direkt das Faktenattribut Order Number im v3 Measure verwendet wird. Diese Syntaxänderung führt selbst bei kleinen Faktentabellen zu minutenlangen Berechnungen bis zur Out-of-Memory Meldung:

Quellen

https://www.sqlbi.com/articles/computing-running-totals-in-dax/

https://www.daxpatterns.com/cumulative-total/

Inputs im Kommentarbereich dieses Blogs sind sehr willkommen!

Über den Autor

Blog auf Feedly abonnieren

Kategorien

Verwandte Beiträge

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

Dashboarding mit Power BI, DAX & Datenmodellierung und Power Query. Drei Einzelmodule oder als ganze Trainingswoche - für Einsteiger und Fortgeschrittene!

Termine 2022

Wien: (7./8. Februar 2022)
und 25.-28 April 2022
Nürnberg: (14./15. Februar 2022)
und 9.-12. Mai 2022

Jetzt buchen und Rabatt sichern.

Jetzt buchen!

Leave a Replay

Schreibe einen Kommentar

Kostenlos zum Newsletter anmelden

Ihre Anfrage

Schicken Sie uns Ihre Fragen und Anregungen!