Power BI liefert eine Vielzahl an Möglichkeiten, intelligent mit der Dimension Zeit zu kalkulieren. Dies erfolgt in der Programmiersprache DAX mittels sog. Time Intelligence-Funktionen. Diese ermöglichen es beispielsweise, eine kumulierten Jahreswert (oder auch Year-to-date-Wert (YTD)) sehr einfach zu ermitteln. Doch wie muss ich vorgehen, wenn mein Geschäftsjahr vom Kalenderjahr abweicht? Dieser Frage gehe ich in diesem Beitrag nach.

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

Die Problematik

Was bedeutet es, wenn das Geschäftsjahr eines Unternehmens vom Kalenderjahr abweicht? Ich nehme den Fall, dass ein Geschäftsjahr nicht wie das Kalenderjahr am 31.12. endet, sondern erst am 31.3. des Folgejahres. Die folgende Abbildung veranschaulicht dies:

Unter der Annahme, dass das erste Datum in Zelle A2 steht, kann das Geschäftsjahr in Excel also mit folgender Formel berechnet werden: =WENN(MONAT(A2)<4;JAHR(A2)-1;JAHR(A2)).

Ziel

Das Ziel, das ich für die Gestaltung meines Berichts verfolge, besteht aus zwei Teilen.

1. Ich möchte einen bestimmten Einfluss auf den Aufbau der Pivottabelle nehmen. Diese Lösung kann natürlich auch auf alle anderen Visualisierungen übertragen werden, die man in Power BI Desktop nutzen kann.
2. Die Berechnung eines entsprechenden Year-to-Date-Wertes soll nicht mit dem Januar beginnen, sondern sich an meinen spezifischen Geschäftsjahresanfang orientieren.

Für das bessere Verständnis zeige ich jetzt die gewünschten Resultate.

Teilziel 1 – Die Beeinflussung des Pivotaufbaus

Das Ziel ist, dass bei der Auswahl des Geschäftsjahres 2017 über den oben befindlichen Datenschnitt, die Pivottabelle automatisch mit dem April 2017 beginnt und mit dem März 2018 endet. Der folgende Screenshot zeigt dies:

Teilziel 2 – Die Berechnung eines Year-to-Date-Wertes, bezogen auf das Geschäftsjahr

Ein Year-to-Date-Wert gibt einen kumulierten Jahreswert zurück, der standardmäßig alle Werte vom 1.1. des Jahres bis zum gewünschten Berichtstag widergibt. In meinem Falle startet das Jahr jedoch am 1. April:

Diese beiden Ziele gilt es umzusetzen, um ein korrektes Reporting zu gewährleisten. Bevor ich jedoch auf die Lösung eingehe, erkläre ich kurz die Augangssituation.

Die Ausgangssituation

Die Basis für meine Lösung ist das folgende, sehr einfache Datenmodell. Gegeben ist eine Faktentabelle (Umsaetze) mit den Umsätzen. Hinzu kommen zwei Dimensionstabellen Datum und Verkaufsstellen, die mit der Faktentabelle in Beziehung stehen.

Mit diesem Wissen, mache ich mich nun an die Beeinflussung der Pivotstruktur, indem ich das Datenmodell entsprechend anpasse.

Den Aufbau der Pivot beeinflussen: Das Datenmodell anpassen

Ich hatte bereits beim Erläutern der Problematik gezeigt, wie sich das Geschäftsjahr als Formel in Excel ergibt. Da ich meine Daten über Power Query ins Datenmodell in Excel geladen habe, ist eine valide Lösung genau diese Excelformel in Power Query über eine neue berechnete Spalte hinzuzufügen. Dies gestaltet sich wie folgt:

• Ich gehe in Power Query in die Abfrage meiner Datumstabelle
• Dort füge ich eine neue Spalte hinzu und nutze hierfür die folgende Formel:

Dies führt zu der neuen Spalte Geschäftsjahr in meiner Dimensionstabelle Datum. Ich speichere meine Abfrage und verlasse Power Query. Ein Blick auf die Pivottabelle zeigt folgendes:

In dieser Lösung sind nun die Monate April 2017 bis März 2018 enthalten – so wie es sein soll. Allerdings ist die Sortierung der Monate in alphabetischer Reihenfolge erfolgt. Dies ist für den Nutzer kaum hilfreich. Deshalb sorge ich jetzt dafür, dass die Sortierung der Monate in chronologischer Reihenfolge erfolgt. Hierfür gehe ich zurück ins Datenmodell. Nun gehe ich wie folgt vor:

• Ich markiere in der Tabelle Datum diejenige Spalte, die in meiner Pivottabelle den Monat beinhaltet (Spalte MonatImKalenderjahr).
• Dann klicke ich auf die Schaltfläche Startseite → Nach Spalte sortieren.
• Hier wähle ich aus, dass die als Text formatierte Spalte MonatImKalender, die in der Pivottabelle bisher alphabetisch sortiert wird, nach der numerischen Spalte JahrMonat sortiert werden soll.

Der folgende Screenshot zeigt die Vorgehensweise bildlich:

Das Ergebnis sieht daraufhin wie gewünscht aus:

Bevor ich mich der Kalkulation der YTD-Kennzahl widme, definiere ich zunächst im Datenmodell, auf Basis welcher Spalte die datumsbezogenen Kalkulationen stattfinden können.

Die Datumstabelle markieren

Die sog. Time Intelligence-Funktionen, wie beispielsweise die TOTALYTD()-Funktion, müssen definiert bekommen, welche Tabelle im Datenmodell die Kalendertabelle ist. Dies macht eine korrekte Kalkulation erst möglich. Ich markiere die Tabelle Datum wie folgt als Datumstabelle:

Nachdem die Datumstabelle nun markiert ist, kann mit der Kalkulation des YTD-Measures begonnen werden.

Die Kalkulation der Kennzahl beeinflussen: DAX – TOTALYTD()

Um einen YTD-Wert zu kalkulieren, bietet es sich in der Regel an, die DAX-Funktion TOTALYTD() zu nutzen. Diese hat die folgende Syntax:

TOTALYTD(<Ausdruck>,<Datumswerte>[,<Filter>][,<Jahresenddatum>])
Hier eine kurze Erläuterung der einzelnen Parameter:

• Ausdruck: Der Wert, aus dem der YTD-Wert gebildet werden soll. In meinem Beispiel die Spalte Umsaetze[Wert].
• Datumswerte: Eine Spalte mit Datumswerten, anhand derer der YTD-Zeitraum kalkuliert werden kann. In meinem Beispiel die Spalte Datum[Datum].
• Filter: Ein optionaler Parameter, der zum gegenwärtigen Kontext, unter dem die Formel evaluiert wird, einen weiteren Filter ergänzt. Dies ist für mein Beispiel nicht notwendig und wird vernachlässigt.
• Jahresenddatum: Ein optionaler Parameter, der das (Geschäfts-) Jahresende als Text übergibt. Hierbei sollte die US-amerikanische Reihenfolge „Monat/Tag“ eingehalten werden. Varianten wie „6/30“, „6-30“ und „Jun 30“ sind möglich.

Die folgende Formel bildet den korrekten YTD-Wert für mein Beispiel in DAX ab:

=
TOTALYTD ( SUM ( Umsaetze[Wert] ); Datum[Datum]; „3/31“ )

Ziehe ich dieses neue Measure nun mit in meine Pivottabelle, so entspricht das Ergebnis meiner Zielstellung:

Diese Lösung funktioniert hervorragend in 11 von 12 Fällen. Falls das Geschäftsjahr jedoch im Februar endet, stehe ich vor einem Problem, denn ich müßte meiner TOTALYTD()-Funktion mitgeben, dass mein Geschäftsjahr am 28.02. oder am 29.2. enden kann, je nachdem, ob es sich um ein Schaltjahr handelt, oder nicht. Dies ist mit der TOTALYTD()-Funktion jedoch nicht möglich. Wie dieses Problem dennoch zu lösen ist, erkläre ich im nächsten Post.

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 🙂