In meinem letzten Post habe ich gezeigt, wie man mit der TOTALYTD()-Funktion in Power BI ein vom Kalenderjahr abweichendes Geschäftsjahr berücksichtigt. Dies führt in 11 von 12 Monaten zum korrekte Ergebnis. Doch wie muss ich vorgehen, wenn der Monat, in dem mein Geschäftsjahr endet, gerade der Februar ist. Oder anders: Wie berücksichtige ich Schaltjahre bei dieser Kalkulation? Dieser Fage gehe ich im aktuellen Beitrag nach.

Als Abonnent meines Newsletters erhältst Du die Beispieldateien zu den Beiträgen dazu. Hier geht’s zum Abonnement des Newsletters!

Die Ausgangssituation

Die Basis für diese Lösung ist, wie auch im vorangegangenen Beitrag, das folgende Datenmodell:

Da sich Beginn und Ende des Geschäftsjahres verändert haben, muss ich eine kleine Anpassung des Datenmodells vornehmen.

Anpassung der Geschäftsjahresspalte mittels Power Query

Wie schon im letzten Post, muss die Spalte Geschäftsjahr jeden Kalendermonat dem entsprechenden Geschäftsjahr zuordnen. Um dies anzupassen, gehe ich in Power Query in die Abfrage Datum und ändere die Berechnung der Spalte Geschäftsjahr, wie im folgenden Screenshot zu sehen ist:

Basierend auf diesem Datenmodell gilt es nun, ein entsprechendes DAX-Measure zu schreiben, das dem Schaltjahr Rechung trägt.

Die Lösung: Ein DAX-Measure, das dem Schaltjahr Rechnung trägt

Um ein DAX-Measure zu schreiben, das auch bei Schaltjahren den korrekten Year-to-date-Wert kalkuliert, greife ich auf eine Lösung zurück, welche die Kollegen von SQLBI auf ihrer Website DAXPatterns.com als Vorlage erstellt haben. Der folgende DAX-Code kalkuliert den Year-to-date-Wert auch dann richtig, wenn das Geschäftsjahr im Februar endet:

=
CALCULATE (
    SUM ( Umsaetze[Wert] );
    FILTER (
        ALL ( Datum );
        AND (
Datum[Datum] <= MAX ( Datum[Datum] );
Datum[Geschäftsjahr] = MAX ( Datum[Geschäftsjahr] )
        )
    )
)

Auf diese Weise umgehe ich das Problem, dass bei der TOTALYTD()-Funktion im letzten Parameter ein einzelner Wert, als Jahresendwert angegeben werden muss. Dies führt im Falle des Februar zu Problemen, da dieser am 28.02., oder am 29.02. enden kann.

Der Code mag auf den ersten Blick verwirren. So ging es mir zumindest, als ich ihn vor Jahren das erste Mal gesehen habe. Daher widme ich den restlichen Beitrag der Funktionsweise dieses Measures, um ein grundlegendes Verständnis dafür zu erzeugen. Falls Du also „nur“ an einer Lösung Deines Problems interessiert bist und Dich ein tieferes Verständnis dieser Lösung nicht interessiert, dann kannst Du an dieser Stelle aufhören weiterzulesen. Nimm Dir den oben stehenden Code, passe ihn an Dein Datenmodell an und fertig. Falls Du verstehen möchtest, was dieser Code tut, dann schnall Dich jetzt besser an. Es gibt viel zu betrachten.

Die Funktionsweise des Measures verstehen

Meiner Ansicht nach besteht die beste Möglichkeit, dieses Measure zu verstehen darin, es gemeinsam zu entwickeln. Genau dies werde ich jetzt mit Dir machen.

Bei DAX dreht sich alles um Filter

Jedes DAX-Measure wird im Rahmen eines bestimmten, sog. Evaluierungskontextes berechnet. Ein Bestandteil dieses Evaluierungskontextes, ist der sog. Filterkontext. Was darunter zu verstehen ist, kann zumindest oberflächlich einfach beantwortet werden. Schau Dir folgende Pivottabelle an:

Filter in DAX werden immer durch Tabellen definiert. Betrachte ich den rot umrahmten Wert 1.827,00 €, so ist der Filter, der diesen Wert definiert, über drei Spalten im Datenmodell definiert:

• Datum[Geschaeftsjahr] = 2017
• Datum[MonatImKalender] = Mai 2017
• Verkaufsstellen[Verkaufsstelle] = VK01

Diese drei Filterkriterien filtern die Datensätze in den Tabellen Datum und Verkaufstellen ein. Über die im Datenmodell befindlichen Beziehungen wirken sich diese Filter von den beiden Tabellen Datum und Verkaufsstellen auf die Tabelle Umsaetze aus und grenzen damit auch die dort befindlichen Datensätze ein. Visuell kann man sich das wie folgt vorstellen:

Auf diese Art und Weise werden diejenigen Datensätze selektiert, die dann den Wert 1.827,00 € ergeben. Mein Ziel ist jedoch, den durch die Pivottabelle gesetzten Filter auf der Datumstabelle zu verändern. Schließlich soll der entsprechende Wert nicht die Werte des angezeigten Monats (hier Mai) wiedergeben, sondern alle Werte von März bis zum angezeigten Monat. Im aktuellen Fall soll der Filter also wie folgt aussehen:

• Datum[Geschaeftsjahr] = 2017
• Datum[MonatImKalender] = März oder April oder Mai 2017
• Verkaufsstellen[Verkaufsstelle] = VK01

Die einzige DAX-Funktion, die es bewerkstelligen kann, den bestehenden Filterkontext zu überwinden, ist die Funktion CALCULATE(). Ihre Schwesterfunktion CALCULATETABLE() ist hierzu auch in der Lage, soll an dieser Stelle jedoch vernachlässigt werden.

Mit CALCULATE() den Filterkontext überwinden

Damit wäre erklärt, dass CALCULATE() der Schlüssel zum Erfolg ist. Mein erster Ansatz für ein Measure sieht also wie folgt aus:

=
CALCULATE ( SUM ( Umsaetze[Wert] ) )

Setze ich nun dieses Measure in meine Pivottabelle ein, dann sieht das Ergebnis so aus:

Wie Du sehen kannst, ist das Ergebnis immer noch das gleiche. Somit hat diese DAX-Formel (noch) nicht den gewünschten Effekt gebracht. Schauen wir uns doch mal die Syntax von CALCULATE() an:

CALCULATE(<Ausdruck>;<Filter1>;<Filter2>…)

CALCULATE() nimmt einen Ausdruck auf (in meinem Beispiel SUM ( Umsaetze[Wert] ) ) und ermittelt diesen unter Zuhilfenahme diverser optionaler Filterkriterien. Diese Filterkriterien sind der Schlüssel zum Erfolg.

Die Filterkriterien von CALCULATE definieren

Was ich nun erreichen möchte ist das korrekte Definieren der Filterkriterien für die CALCULATE()-Funktion. Die Ausgangssituation hierfür sieht also wie folgt aus:

=
CALCULATE ( SUM ( Umsaetze[Wert] ); Filterkriterien )

Da in meinem Datenmodell zwischen den Tabellen Datum und Umsaetze eine 1:n-Beziehung herrscht (die Linien, die die Tabellen im Datenmodell miteinander verbinden symbolisieren dies) wirkt sich ein Filter auf der Tabellen Datum automatisch auf die Tabelle Umsaetze aus. Meine DAX-Formel muss jetzt folgendes schaffen: Obwohl der Filterkontext (durch die Pivotüberschrift) auf Monat Mai eingrenzt, muss der Filterkontext in meinem Measure März, April und Mai beinhalten.

Um einen entsprechenden Filter in meiner CALCULATE()-Funktion zu erzeugen, nutze ich die Funktion FILTER().

Die Nutzung der Funktion FILTER(), um die Filter in CALCULATE() zu definieren

Die DAX-Funktion FILTER() hat folgende Syntax:

FILTER(<Tabelle>;<Filter>)
Dieser Funktion muss eine Tabelle übergeben werden, die dann entsprechend gefiltert wird.

FILTER() verbindet zwei nützliche Eigenschaften:

1. Es handelt sich um eine Tabellenfunktion. Diese Funktion gibt also eine Tabelle zurück, die dann als Filterkriterum in CALCULATE genutzt werden kann.
2. FILTER() ist zugleich ein sog. Iterator und ist daher in der Lage, auf der Tabelle (erster Parameter) zeilenweise Filteroperationen auszuführen.

Mit der Kenntnis um die Funktion FILTER() und dem Wissen, dass ich auf Basis der Tabelle Datum filtern muss, kann ich meine bisheriges Measure wie folgt erweitern:

=
CALCULATE ( SUM ( Umsaetze[Wert] ); FILTER ( Datum; FILTERKRITERIEN ) )

Die Filterfunktion wird die Tabelle Datum also zeilenweise durchlaufen und nach bestimmten Filterkriterien filtern, die ich bisher noch nicht definiert habe. Diese definiere ich jetzt.

Die Filterkriterien für die Funktion FILTER() definieren

Damit die Funktion FILTER() eine korrekt eingeschränkte Tabelle zurückliefern kann, die für die CALCULATE()-Funktion als Filter dient, müssen nun die Filterkriterien in der FILTER()-Funktion definiert werden. Ich beschreibe zunächst in Worten, wie dies inhaltlich aussehen soll, bevor ich es als Formel definiere:

=
FILTER (

führe alle nachfolgend definierten Filteroperationen zeilenweise auf der gesamten Tabelle Datum aus;
           Monat in der entsprechenden Zeile in Tabelle Datum <= Monat in der Überschrift der Pivot UND
           Geschäftsjahr in der entsprechenden Zeile in Tabelle Datum = Geschäftsjahr (ausgewählt über den Datenschnitt in der Pivot)
)

Verformelt sieht das ganze dann wie folgt aus:

=
FILTER (
    Datum;
    AND (
Datum[MonatImJahr] <= MAX ( Datum[MonatImJahr] );
Datum[Geschäftsjahr] = MAX ( Datum[Geschäftsjahr] )
    )
)

Was an dieser Formel wohl am schwierigsten zu verstehen ist, sind die folgenden zwei Sachverhalte:

1. Der Ausdruck MAX ( Datum[Geschäftsjahr] ) ermittelt den über den Datenschnitt erzeugten Filter auf der Spalte Datum[Geschaeftsjahr], in meinem Beispiel 2017
2. Der bisher genutzte Beispielwert 1.827,00 € hat bzgl. der Datumstabelle den Filterkontext Datum[MonatImKalender] = „Mai 2017“, denn die Spalte Datum[MonatImKalender] wird als Spaltenüberschrift für die Pivot benutzt und hat beim Wert 1.827,00 € den Wert Mai 2017. Dadurch ist die Datumstabelle bereits auf Zeilen eingeschränkt, die zum Mai 2017 gehören. Der Ausdruck MAX ( Datum[MonatImJahr] ) ermittelt jetzt auf Basis dieser gefilterten Datumstabelle den maximalen Wert der Spalte Datum[MonatImJahr], der für das genannte Beispiel 5 ergibt.

Der folgende Screenshot veranschaulicht dies noch einmal:

 

Basierend auf den nun erstellten Filterkriterien, sieht das Measure wie folgt aus:

=
CALCULATE (
    SUM ( Umsaetze[Wert] );
    FILTER (
        Datum;
        AND (
Datum[MonatImJahr] <= MAX ( Datum[MonatImJahr] );
Datum[Geschäftsjahr] = MAX ( Datum[Geschäftsjahr] )
        )
    )
)

Lass uns auf das Ergebnis schauen:

Ja, Du siehst richtig: Wir haben immer noch nicht das gewünschte Ergebnis erzielt. Genaugenommen ist das Ergebnis (zumindest der Zahlenwert) identisch. Aber glaube mir, wir sind kurz davor. Zunächst eine Frage an Dich: Hast Du eine Idee, wieso der Wert immer noch unverändert ist, obwohl wir doch den Filter auf der Tabelle Datum so ausführlich definiert haben? Da muss doch jetzt der April mit in das Ergebnis einfließen!

Die Antwort ist folgende: März und April fehlen in unserem Ergebnis deshalb, weil noch bevor die FILTER()-Funktion dazu kommt, über die Tabelle Datum zu iterieren und nach bestimmten Kriterien zu filtern, der Filterkontext der Pivot bereits gegriffen hat. Das bedeutet, dass noch bevor FILTER() über die Datumstabelle iteriert, diese bereits auf den Monat Mai eingeschränkt ist, weil dieser zum Filterkontext der Pivot gehört. Verwirrend, oder?! Daran gewöhnt man sich mit der Zeit 😉 Die FILTER()-Funktion ist also nie über alle Zeilen der Datumstabelle iteriert, sondern immer nur über diejenigen, die zum Monat Mai 2017 gehören. Es gibt eine weitere (und für dieses Beispiel letzte) DAX-Funktion, die die Rettung bringt: ALL().

Die Rettung durch ALL()

Um die korrekten Werte zu erhalten, muss ich abschließend dafür sorgen, dass die genutzte FILTER()-Funktion auf der gesamten Datumstabelle filtern kann und nicht auf der, die bereits durch den Filterkontext der Pivot eingeschränkt wurde. Wirf einen Blick auf die abschließende Lösung, die Du bereits vom Anfang des Beitrags kennst:

=
CALCULATE (
    SUM ( Umsaetze[Wert] );
    FILTER (
        ALL ( Datum );
        AND (
Datum[MonatImJahr] <= MAX ( Datum[MonatImJahr] );
Datum[Geschäftsjahr] = MAX ( Datum[Geschäftsjahr] )
        )
    )
)

Die ALL()-Funktion ignoriert den bestehenden Filterkontext durch die Datumstabelle. ALL( Datum ) liefert die gesamte (nicht gefilterte) Datumstabelle an die FILTER()-Funktion zurück, so dass diese auf Basis der gesamten Datumstabelle filtern kann. Auf diese Weise ist es möglich den durch die Pivot auf Mai 2017 gesetzten Filter zu überschreiben und auf März, April und Mai 2017 zu erweitern. Dadurch ist die CALCULATE()-Funktion in der Lage, die gewünschten Year-to-date-Werte zu kalkulieren und das folgende Ergebnis zu erzeugen:

Fazit

Ich weiß: Das war lang und nicht trivial. Ich habe dieses Measure in dieser Ausführlichkeit beschrieben, weil es viele DAX-Konzepte beinhaltet, die Dir in Deinem Umgang mit DAX regelmäßig begegnen werden. Ich hoffe, ich habe damit ein wenig zum besseren Verständnis dieser Sprache beitragen können 🙂

Bis zum nächsten Mal und denk dran: Sharing is caring. Wenn Dir der Beitrag gefallen hat, dann teile ihn gerne. Falls Du Anmerkungen hast, schreibe gerne einen Kommentar, oder schicke mir eine Mail an lars@ssbi-blog.de

Viele Grüße aus Hamburg,

Lars

Ich schreibe meine Beiträge für Dich, den Leser. Bitte schenke mir eine Minute Deiner Zeit und bewerte die folgenden Kategorien, um mir zu helfen meine Beiträge so gut wie möglich zu schreiben. Danke 🙂