Power BI - YTD-Werte in Datenquelle mit Measure zurückrechnen

Startseite » Allgemein » Power BI – YTD-Werte in Datenquelle mit Measure zurückrechnen

Ein Seminarteilnehmer hat es in seiner Datenquelle mit Quartalsergebnissen zu tun, die als YTD-Werte vorliegen. Daher erhält er im Bericht stets falsche Ergebnisse. Er möchte wissen, wie er die YTD-Werte in die eigentlichen Quartalsergebnisse zurückrechnen kann.

Darüber hinaus möchte ich in diesem Artikel auch aufzeigen, wie Sie ChatGPT nutzen können, um Ihre Measures zu überprüfen und gegebenenfalls zu verbessern. Auch auf mögliche Fallstricke, die bei der Nutzung von KI auftreten können, möchte ich hier aufmerksam machen.

Die Daten

Schauen Sie sich einmal die Tabelle „Umsatz“ an:

Tabelle in Power BI. In der Spalte „YTD“ wachsen die Werte bis zum jeweiligen Quartal 4 stetig an. — In der Tabelle „Umsatz“ werden die Werte in der Spalte „YTD“ im Jahresverlauf immer größer

YTD (Year-to-date) meint den Zeitraum seit Beginn des Jahres (in der Regel der 1. Januar; kann in Geschäftsjahren abweichen) bis zum aktuellen Zeitpunkt. In Power BI ist der aktuelle Zeitpunkt als letztes Datum im jeweiligen Filterkontext zu verstehen.

In Quartal 4 stehen also die Umsätze aus allen Quartalen des jeweiligen Jahres, in Quartal 3 die Umsätze aus den Quartalen 1-3 usw.
Zum besseren Verständnis zeige ich Ihnen in folgendem Excel-Ausschnitt, wie die Werte in der Spalte "YTD" berechnet werden. (Die Spalte "Quartalsergebnis" ist leider in der Datenquelle selbst nicht mehr enthalten).

Tabelle in Excel, welche die Formeln zur Berechnung der Spalte „YTD“ anzeigt — In diesem Beispiel sehen Sie, wie die Spalte „YTD“ berechnet wird

Bitte beachten Sie, dass dieses Beispiel stark vereinfacht ist. Tatsächlich sind die Daten beim Kunden wesentlich komplexer. In diesem Beispiel käme auch eine Lösung mittels Power Query in Betracht, die ich hier aber bewusst nicht zeigen möchte, da sie beim Kunden aufgrund der komplexeren Datenlage nicht einsetzbar wäre.

Außerdem verzichte ich in diesem Beispiel auf eine Datumstabelle und platziere die Felder „Jahr“ und „Quartal“ direkt in der Tabelle „Umsatz“. Dies tue ich hier um das Beispiel zu vereinfachen. In der Praxis sollten Sie zeitraumbezogene Felder wie Jahr und Quartal natürlich aus einer Datumstabelle ziehen. Generell empfiehlt es sich, in jedem Projekt eine Datumstabelle zu verwenden!

Wenn mein Seminarteilnehmer mit diesen Daten nun einen Bericht mit der Summe der Spalte „YTD“ erzeugt, dann kommt er nicht auf die korrekten Ergebnisse:

Matrix in Power BI, welche die Summe der Spalte „YTD“ nach Sparten, Jahren und Quartalen zeigt. Die Ergebnisse sind falsch. — Wenn Sie einfach nur die Summe der Spalte „YTD“ bilden, erhalten Sie die falschen Ergebnisse.

Der Grund dafür ist, dass Power BI in den Gesamtergebnissen die Summe aus allen Quartalen bildet. Dies ist im gegebenen Fall aber falsch, da ein Quartal ja bereits Umsätze aus mehreren anderen Quartalen beinhalten kann.

Außerdem wünscht sich der Seminarteilnehmer, dass er in den Quartalsspalten der Matrix auch wirklich nur das Ergebnis des jeweiligen Quartals sieht (und nicht die Summe aus den vorherigen Quartalen). Er möchte die YTD-Werte also zurückrechnen in die eigentlichen Quartalsergebnisse.

Die Lösung mit dem Measure "Umsatz korrekt"

Hier die Grundgedanken zu meinem Lösungsansatz:

Ich werde einfach von dem im aktuellen Filterkontext betrachteten Quartal das vorherige Quartal abziehen. Damit habe ich dann immer den tatsächlichen Wert des jeweiligen Quartals errechnet.

Allerdings sind hier zwei Besonderheiten zu beachten:

Für Quartal 1 gibt es kein vorheriges Quartal. Daher ist der Wert aus Quartal 1 bereits der richtige Wert.
In den Gesamtergebnissen darf Power BI natürlich nicht einfach alle Quartale aufsummieren. Stattdessen muss das Programm immer die Summe aus dem Quartal 4 ziehen.

Hier ist nun meine Berechnung:

Umsatz korrekt = 
IF(
    HASONEVALUE(Umsatz[Quartal]),
    VAR __Quartalsnummer = VALUE(RIGHT(SELECTEDVALUE(Umsatz[Quartal]), 1))
    VAR __Berechnng =
        IF(
            __Quartalsnummer > 1,
            VAR __Aktuelles_Quartal = 
                CALCULATE(SUM(Umsatz[YTD]), Umsatz[Quartal] = "Q " & __Quartalsnummer)
            VAR __Vorheriges_Quartal = 
                CALCULATE(SUM(Umsatz[YTD]), Umsatz[Quartal] = "Q " & __Quartalsnummer - 1)
            RETURN
            __Aktuelles_Quartal - __Vorheriges_Quartal,
            SUM(Umsatz[YTD])
        )
    RETURN
    __Berechnng,
    CALCULATE(SUM(Umsatz[YTD]), Umsatz[Quartal] = "Q 4"
    )
)

Mit meinem Measure erzeuge ich diesen Bericht:

Matrix in Power BI. Die Werte zeigen nun die korrekten Ergebnisse an. — Die Matrix zeigt nun die richtigen Ergebnisse

Im folgenden möchte ich das Measure ausführlich beschreiben:

Mit den Funktionen IF und HASONEVALUE prüfe ich, ob Power BI die Berechnung für ein einzelnes Quartal ausführt. HASONEVALUE(Umsatz[Quartal]) gibt „True“ zurück, wenn im aktuellen Filterkontext nur ein Quartal enthalten ist. Sind im Filterkontext jedoch mehrere Quartale sichtbar (und das ist immer in den Gesamtergebnissen der Fall), dann liefert die Funktion „False“.

Um die Funktionsweise von HASONEVALUE besser zu erklären, lege ich das Measure „Test“ an, in welchem folgende Formel steht:

Test = HASONEVALUE(Umsatz[Quartal])

Schauen Sie sich einmal folgenden Bericht an, in welchem ich das Measure „Test“ in den Wertebereich der Matrix gelegt habe:

Matrix in Power BI. In den Gesamtergebnissen steht „False“, in allen anderen Zellen „True“ — In den Gesamtergebnissen liefert HASONEVALUE immer der Wert „False“

In den Gesamtergebnissen erhalte ich „False“, da im jeweiligen Filterkontext mehr als ein Quartal sichtbar ist.

VAR __Quartalsnummer = VALUE(RIGHT(SELECTEDVALUE(Umsatz[Quartal]), 1))

In der Variablen "__Quartalsnummer" speichere ich die Quartalsnummer. Diese lese ich mit der Funktion RIGHT aus dem Quartalsnamen heraus. Da das Ergebnis der RIGHT-Funktion eine Zeichenfolge ist, wandle ich es mit VALUE in eine Zahl um.

SELECTEDVALUE muss ich benutzen, da ich nicht direkt auf die Spalte Quartal zugreifen kann (dies geht innerhalb eines Measures nur mit einer Aggregatfunktion oder eben u.a. mit SELECTEDVALUE).

IF(
    __Quartalsnummer > 1,
    VAR __Aktuelles_Quartal = 
        CALCULATE(SUM(Umsatz[YTD]), Umsatz[Quartal] = "Q " & __Quartalsnummer)
    VAR __Vorheriges_Quartal = 
        CALCULATE(SUM(Umsatz[YTD]), Umsatz[Quartal] = "Q " & __Quartalsnummer - 1)
    RETURN
    __Aktuelles_Quartal - __Vorheriges_Quartal,
    SUM(Umsatz[YTD])
)

Sollte die Quartalsnummer größer als 1 sein, dann speichere ich die Werte des aktuellen sowie des vorherigen Quartals (__Quartalsnummer -1) in jeweils einer Variablen.

Anschließend ziehe ich vom aktuellen Quartal das vorherige Quartal ab. Das RETURN ist notwendig, damit ich mit den beiden zuvor erzeugten Variablen rechnen kann.

Sollte die Quartalsnummer dagegen 1 sein, dann soll die IF-Funktion einfach nur den Wert des Quartals (SUM(Umsatz[YTD]) zurückgeben.

Beurteilung und Verbesserung meines Measures durch ChatGPT

Sie können Ihre Measures von ChatGPT prüfen lassen. Die Künstliche Intelligenz kann Sie auf Unstimmigkeiten hinweisen und Ihnen gegebenenfalls sogar andere Lösungswege aufzeigen. Wichtig ist, dass Sie der KI alle Informationen mitgeben, die diese braucht, um Ihre Frage zu beantworten.

Genau hier liegt auch die Schwierigkeit: wenn Sie der KI nicht deutlich genug mitteilen, was sie tun soll, dann kann es sein, dass sie Ihnen eine falsche Antwort liefert. Nehmen Sie sich also Zeit, damit Sie Ihren Prompt strukturiert aufbauen können. Lassen Sie keine Informationen weg, die wichtig sein könnten. Sie könnenChatGPT auch Beispieldaten (etwa im CSV-Format) zur Verfügung stellen.

Künstliche Intelligenz wird im Controlling immer wichtiger! Ich möchte Ihnen in diesem Beitrag zeigen, wie ich ChatGPT nutze, um meine Produktivität zu erhöhen. Achten Sie aber bitte immer darauf, dass Sie der KI nur Beispieldaten zur Verfügung stellen! Der Grund dafür ist, dass nicht ausgeschlossen werden kann, dass die eingegebenen Informationen zu Trainingszwecken weiterbenutzt werden.

Mein 1. Prompt an ChatGPT

Dies ist meine erste Eingabe in ChatGPT:

Ich habe eine Frage zu einem Measure in Power BI Desktop. Ich zeige dir erst einmal die Daten, erkläre dir dann das Problem und zeige dir anschließend meine Measure-Lösung.

Meine Daten (Tabelle "Umsatz):
Sparte;Jahr;Quartal;YTD
A;2021;Q 1;53
A;2021;Q 2;80
A;2021;Q 3;159
A;2021;Q 4;216
A;2022;Q 1;92
A;2022;Q 2;162
A;2022;Q 3;205
A;2022;Q 4;224
B;2021;Q 1;91
B;2021;Q 2;136
B;2021;Q 3;154
B;2021;Q 4;179
B;2022;Q 1;39
B;2022;Q 2;115
B;2022;Q 3;191
B;2022;Q 4;288

Das Problem:
Die Werte in der Spalte „YTD“ werden innerhalb eines Jahres hochgerechnet. Das bedeutet, in Quartal 4 steht die Summe aus allen Quartal eines Jahres. Ich möchte im Bericht aber nur den tatsächlichen Quartalsumsatz sehen. Das bedeutet, ich muss die Ergebnisse der vorherigen Quartale herausrechnen.

Mein Measure:
Ich habe dazu das folgende Measure geschrieben. Es zieht den Betrag des vorherigen Quartals immer vom aktuellen Quartal ab (es sei denn, das aktuelle Quartal ist das 1. Quartal, dann führe ich keine Subtraktion durch). Außerdem prüfe ich mit der Funktion HASONEVALUE noch, ob die Berechnung für ein einzelnes Quartal oder für ein Jahr durchgeführt wird. Wenn Power BI ein Jahresergebnis errechnen soll, dann will ich natürlich nur den Wert aus dem Quartal 4 nehmen. Hier nun meine Lösung:

Umsatz korrekt =
IF(
HASONEVALUE(Umsatz[Quartal]),
VAR __Quartalsnummer = VALUE(RIGHT(SELECTEDVALUE(Umsatz[Quartal]), 1))
VAR __Berechnng =
IF(
__Quartalsnummer > 1,
VAR __Aktuelles_Quartal = CALCULATE(SUM(Umsatz[YTD]), Umsatz[Quartal] = "Q " & __Quartalsnummer)
VAR __Vorheriges_Quartal = CALCULATE(SUM(Umsatz[YTD]), Umsatz[Quartal] = "Q " & __Quartalsnummer - 1)
RETURN
__Aktuelles_Quartal - __Vorheriges_Quartal,
SUM(Umsatz[YTD])
)
RETURN
__Berechnng,
CALCULATE(SUM(Umsatz[YTD]), Umsatz[Quartal] = "Q 4"
)
)

Sage mir bitte, ob meine Lösung effizient ist oder ob es eine bessere Lösung gibt. Bitte begründe deine Antwort und zeige mir bessere Lösungen auf, falls du eine solche findest.

Versuchen Sie der KI so viele Informationen wie möglich zu geben. Bauen Sie Ihren Prompt strukturiert auf. Lassen Sie keine Details aus, damit das Programm möglichst keinen Spielraum für eigene Interpretationen hat!

Die 1. Antwort von ChatGPT

Hier ist die Antwort von ChatGPT:

Dein Measure ist gut durchdacht und berücksichtigt die verschiedenen Szenarien, die auftreten können. Es gibt jedoch einige Punkte, die optimiert werden können, um die Effizienz zu erhöhen und den Code zu vereinfachen:

Verwendung von SWITCH statt IF:
Die SWITCH-Funktion ist in vielen Fällen effizienter als mehrere IF-Anweisungen, insbesondere wenn es um die Überprüfung von einzelnen Werten geht.

Vereinfachung der Berechnung:
Statt für jedes Quartal den Wert des vorherigen Quartals zu subtrahieren, könntest du direkt den Wert des aktuellen Quartals abzüglich des Wertes des vorherigen Jahresendes (Q4) verwenden. Dies würde die Anzahl der CALCULATE-Aufrufe reduzieren.

Hier ist eine optimierte Version deines Measures:

Umsatz korrekt =
SWITCH(
TRUE(),
NOT HASONEVALUE(Umsatz[Quartal]), CALCULATE(SUM(Umsatz[YTD]), Umsatz[Quartal] = "Q 4"),
SELECTEDVALUE(Umsatz[Quartal]) = "Q 1", SUM(Umsatz[YTD]),
VAR __Quartalsnummer = VALUE(RIGHT(SELECTEDVALUE(Umsatz[Quartal]), 1))
VAR __Aktuelles_Quartal = CALCULATE(SUM(Umsatz[YTD]), Umsatz[Quartal] = "Q " & __Quartalsnummer)
VAR __Vorheriges_Jahresende = CALCULATE(SUM(Umsatz[YTD]), Umsatz[Quartal] = "Q 4", Umsatz[Jahr] = SELECTEDVALUE(Umsatz[Jahr]) - 1)
RETURN __Aktuelles_Quartal - __Vorheriges_Jahresende
)

Diese Version verwendet die SWITCH-Funktion, um die verschiedenen Szenarien zu überprüfen, und reduziert die Anzahl der CALCULATE-Aufrufe, indem sie direkt den Wert des aktuellen Quartals abzüglich des Wertes des vorherigen Jahresendes verwendet.

Zusammenfassend ist deine ursprüngliche Lösung korrekt und funktioniert, aber die vorgeschlagene Version könnte effizienter sein und hat weniger Code. Es ist immer eine gute Idee, verschiedene Ansätze in deinem tatsächlichen Power BI-Modell zu testen, um die beste Performance zu erzielen.
ChatGPT, Abgerufen am 11.09.2023

Ich habe die vorgeschlagene Lösung direkt ausprobiert. Das Ergebnis war leider ernüchternd:

Matrix in Power BI. Die Werte, die ChatGPT errechnet, sind leider falsch. — Das Measure von ChatGPT liefert leider die falschen Ergebnisse.

ChatGPT kann mit seinem Measure die Gesamtergebnisse zwar korrekt berechnen, die Quartalswerte sind allerdings falsch! Die KI führt hier eine ganz seltsame Berechnung durch, in welcher Sie vom Quartalsergebnis immer stur das Ergebnis von Quartal 4 des Vorjahres abzieht.

Offensichtlich hat die KI die Aufgabenstellung nicht richtig verstanden!

Mein 2. Prompt an ChatGPT

Ich habe ChatGPT mit folgenden Prompt auf den Fehler hingewiesen. (Um ChatGPT den Fehler deutlich zu machen, habe ich die Ergebnisse aus der Matrix in eine CSV-Datei exportiert und die Daten in den Prompt mit eingegeben.)

Leider komme ich mit deiner Berechnung nicht auf das gewünschte Ergebnis. Ich erhalte zwar in den Jahreswerten korrekte Ergebnisse, die Quartalsergebnisse sind aber falsch. Diese Daten erhalte ich mit deiner Lösung (Export aus einer Visualisierung):

Sparte,Jahr,Quartal,Umsatz korrekt
A,2021,Q 1,53
A,2021,Q 2,80
A,2021,Q 3,159
A,2021,Q 4,216
A,2022,Q 1,92
A,2022,Q 2,-54
A,2022,Q 3,-11
A,2022,Q 4,8
B,2021,Q 1,91
B,2021,Q 2,136
B,2021,Q 3,154
B,2021,Q 4,179
B,2022,Q 1,39
B,2022,Q 2,-64
B,2022,Q 3,12
B,2022,Q 4,109

Kannst du das Measure bitte noch mal anpassen?

Die 2. Antwort von ChatGPT

Diesmal hat die Künstliche Intelligenz mein Problem verstanden und liefert mir tatsächlich eine verbesserte Version des Measures:

Entschuldigung für das Missverständnis. Ich verstehe jetzt das Problem besser. Lass uns das Measure korrigieren.

Die Idee ist, den aktuellen YTD-Wert mit dem YTD-Wert des vorherigen Quartals zu vergleichen und die Differenz zu nehmen. Für das erste Quartal nehmen wir einfach den YTD-Wert.

Hier ist eine überarbeitete Version des Measures:

Umsatz korrekt =
IF(
    NOT HASONEVALUE(Umsatz[Quartal]),
    CALCULATE(SUM(Umsatz[YTD]), Umsatz[Quartal] = "Q 4"),
    SWITCH(
        SELECTEDVALUE(Umsatz[Quartal]),
        "Q 1", SUM(Umsatz[YTD]),
        VAR __Quartalsnummer = VALUE(RIGHT(SELECTEDVALUE(Umsatz[Quartal]), 1))
        VAR __Aktuelles_Quartal = CALCULATE(SUM(Umsatz[YTD]), Umsatz[Quartal] = "Q " & __Quartalsnummer)
        VAR __Vorheriges_Quartal = CALCULATE(SUM(Umsatz[YTD]), Umsatz[Quartal] = "Q " & __Quartalsnummer - 1)
        RETURN __Aktuelles_Quartal - __Vorheriges_Quartal
    )
)

In dieser Version wird die SWITCH-Funktion verwendet, um den Wert für das erste Quartal direkt zurückzugeben. Für die anderen Quartale wird der YTD-Wert des aktuellen Quartals mit dem YTD-Wert des vorherigen Quartals verglichen, und die Differenz wird zurückgegeben.

Ich hoffe, dass diese Version die gewünschten Ergebnisse liefert. Es ist immer eine gute Idee, den Code in deinem tatsächlichen Power BI-Modell zu testen, um sicherzustellen, dass er wie erwartet funktioniert.
ChatGPT. Abgerufen am 11.09.2023

Der Code, den ChatGPT gefunden hat, ist tatsächlich etwas eleganter als meine Lösung, da die DAX-Anweisungen nicht ganz so verschachtelt sind. Er ist - zumindest wenn Sie die SWITCH-Funktion kennen - etwas leichter zu lesen als der Code in meinen Measure.

Die Ergebnisse sind nun korrekt:

Sie können die Power BI Desktop-Datei mit allen Measures hier kostenfrei herunterladen:

Power BI Desktop-Datei "YTD zurückrechnen.pbix"Herunterladen

Fazit

Wenn Ihnen Daten im YTD-Format vorliegen, dann dürfen Sie diese in Power BI nicht einfach aufsummieren, da Sie ansonsten falsche Ergebnisse erhalten. Mit einem Measure können Sie die Werte im YTD-Format jedoch zurückrechnen. Der Code dafür ist sicherlich nicht ganz einfach, mit etwas Geduld und guten DAX-Kenntnissen werden Sie es aber ganz sicher schaffen!

Helfen kann Ihnen hier natürlich auch ChatGPT. Dabei kommt es vor allen Dingen darauf an, den Prompt (also die Anweisung an die Künstliche Intelligenz) so zu formulieren, dass diese das Problem richtig versteht. Es lohnt sich definitiv, sich mit der neuen KI-Technik auseinanderzusetzen!