YTD berechnen | Tipps Tuesday #066

[RolandSchubert]

Die Berechnung von Werten "Year to Date" (YTD) gehört insbesondere (aber natürlich nicht nur) im Bereich Finance für viele zum "Tagesgeschäft". Ganz klar, dass hier auch Alteryx zum Einsatz kommen kann ...

Generell bezieht sich der Wert Year to Date (YTD) auf die Perioden vom Beginn des jeweiligen Jahres bis zum Ende des betrachteten Zeitraum (z.B. das aktuelle Datum oder das Ende des Berichtsmonats). 

Allerdings - anders als hier dargestellt - muss der Beginn des Jahres nicht unbedingt der 01. Januar sein; wenn das Geschäftsjahr vom Kalenderjahr abweicht, beginnt das Jahr und damit der "YTD-Zeitraum" vielleicht am 01. April. 

Das Jahr kann dabei das laufende Jahr sein, aber zum Beispiel auch das Vorjahr, etwa für Vergleiche zwischen Ist und Vorjahr. Mitunter ist dann bei "tagesaktuellen" YTD-Werten (also Summe von Beginn des Jahres bis zum aktuellen Datum) auch noch eine kleine Korrektur des Enddatums im Vorjahr erforderlich, um bei einem Vergleich die gleiche Anzahl von Arbeitstagen zu berücksichtigen.

Diesen Sonderfall berücksichtigen wir hier aber nicht, sondern wir schauen uns die Möglichkeiten am Beispiel von Monatsdaten an.

In unserem Beispiel haben wir für die Jahre 2021 und 2022 Planwerte je Monat zur Verfügung, die entsprechenden Istwerte für Januar 2020 bis März 2022. Wir wollen nun für den Berichtsmonat März die YTD-Werte für Ist (Actual 2022), Plan (Budget 2022) und Vorjahr (Actual 2021) berechnen. Wie immer gibt es eine Reihe von Möglichkeiten.

Variante 1: Filter + Summarize

Ein naheliegender Ansatz ist es, einfach die entsprechenden Kombinationen aus unseren Daten herauszufiltern und dann zu summieren. Konkret bedeutet das hier: Wir brauchen vom Actual für 2021 und 2022 sowie vom Budget 2022 jeweils die Monate Jan, Feb und Mar.

Dazu verwenden wir natürlich ein Filter Tool:

Wichtig ist es, hier die Bedingungen für die beiden Alternativen jeweils in Klammern einzufassen - das Thema gab es in einem früheren Tuesday Tipp ja schon mal.

Alternativ können die Bedingungen auch auf zwei Filter Tools aufgeteilt werden - zuerst wird der für beide Fälle gültige Teil abgefragt (Monate Jan, Feb, Mar), dann der "Rest (also die Kombinationen Scenario - Year):

Diese Version finde ich etwas übersichtlicher - aber wie so vieles im Leben ist das natürlich eine Frage des persönlichen Geschmacks.

Nachdem jetzt nur noch die benötigten Werte vorhanden sind, muss nur noch die Summe gebildet werden - dazu brauchen wir dann ein Summarize Tool. Durch die Gruppierung nach Scenario und Year bekommen wir genau die Werte, die wir ermitteln müssen.

Variante 2: Running Total

Natürlich können wir das Problem auch ganz anders angehen - schließlich gibt es das Running Total Tool. Dieses Tool berechnet die kumulierten Werte (also genau das, was wir hier brauchen), gruppiert nach den gewählten Feldern (hier wären das "Scenario" und "Year") für jeden einzelnen Monat.

Wir berechnen hier also viel mehr, als eigentlich gebraucht wird. Kann das unter dem Gesichtspunkt "Performance" sinnvoll sein? Schließlich werden hier ja nicht nur 3, sondern 51 Werte berechnet (4 komplette Jahre  + 3 Monate). Das allerdings ist kein Thema; ganz im Gegenteil ist dieser Ansatz sogar deutlich schneller! 

Variante 3: Multi-Row Formula

Aber es gibt noch einen weiteren Ansatz. Statt des Running Total Tools können wir auch ein Multi-Row Formula Tool einsetzen. Zwar ist etwas mehr Arbeit notwendig, denn wir müssen die Rechenlogik, also die Kumulation der Werte über die einzelnen Monate, hier als Formel hinterlegen.  Dabei wird für die jeweils erste Zeile (für jede Gruppe Scenario/Year) der Wert der Spalte "Revenue" eingesetzt, für die folgenden Zeilen wird jeweils der Wert von Revenue zu den kumulierten Werte der vorherigen Zeile addiert.  

Das Ergebnis stimmt mit dem der Running Total Variante überein, die Lösung mit einem Multi-Row Formula Tool ist aber sogar noch etwa performanter!

Und die Sonderfälle?

Es gibt also eine ganze Reihe von Möglichkeiten, das YTD zu berechnen. Aber funktionieren die Lösungen auch, wenn das Geschäftsjahr nicht mit dem Kalenderjahr übereinstimmt?

Nehmen wir einmal an, wir haben ein Geschäftsjahr, das am 01. Mai beginnt - für unser Beispiel würde das bedeuten, wir müssen jeweils die Monate von Mai bis März berücksichtigen - Mai 2021 bis März 2022 für das laufende Jahr (Ist und Plan) bzw. Mai 2020 - März 2021 für das Vorjahr. In unserer ersten Variante wird damit bereits die Bedingung im Filter sehr komplex, die Summenbildung ist auch nicht mehr so einfach. Auch der Running Total Ansatz passt nicht mehr so richtig - die Gruppierung nach "Year" stimmt schließlich nicht, da der Wechsel des Kalenderjahres keinen Wechsel des Geschäftsjahres mehr bedeutet.

Verhältnismäßig einfach ist die Umstellung im Multi-Row Formula Tool zu realisieren. Die "eingebaute" Gruppierung darf jetzt nur noch für das Scenario (Ist, Plan) genutzt werden, ansonsten wird der Monat "May"  das Kriterium für einen "Neustart" der Kumulation:

Wie wir hier deutlich erkennen, wird jeweils bis April/ab Mai summiert. 

Bei beiden anderen Varianten müsste eine Art "Umrechnung" von Kalenderjahr/Kalendermonat in Geschäftsjahr/Geschäftsjahresmonat vorgeschaltet werden; dazu kann beispielsweise ein Multi-Row Formula Tool verwendet werden. 

Haben wir nicht eine Kleinigkeit vergessen?

Allerdings haben wir bis jetzt stillschweigend vorausgesetzt (zumindest in den Varianten 2 und 3), dass uns die Daten aufsteigend sortiert und lückenlos erreichen. Voraussetzen sollte man so etwas aber nicht - irgendwann gibt es immer eine Abweichung von der Regel ...

Zum Thema "Lücken" folgt noch eine Fortsetzung, die Sortierung wollen wir uns allerdings hier noch ansehen. Leider können wir nicht einfach ein Sort Tool einbauen und nach Scenario, Year und Month sortieren (einbauen können wir es, das Ergebnis ist aber falsch  - Month wird als Text-Feld interpretiert und alphabetisch sortiert)!

Also - was tun? In diesem Fall müssen wir ein neues Feld "Datum" erstellen, das zur Sortierung benutzt werden kann. Dazu erzeugen wir aus "Year" und "Month" ein Datum und wandeln dies in ein Date-Feld um. Und das bringt bei der Sortierung die richtige Reihenfolge.

 Diesen zusätzlichen Schritt sollte man eigentlich in jedem Fall vorsehen - so können mit geringem Aufwand unnötige Fehler ausgeschlossen werden, nach denen man garantiert lange suchen würde!

Weitere Tipps Tuesday Beiträge

Dieser Eintrag ist Teil der Tipps Tuesday-Serie, alle Einträge dieser Serie findest du in unserem Index aufgelistet.