Wenn das Geschäftsjahr vom Kalenderjahr abweicht, kann dieses Schwierigkeiten bei der Erstellung von Diagrammen bereiten.
Dabei bin ich auf folgende Problemstellung gestoßen:
Wie kann ich mit Power Pivot für Excel eine monatlich kumulierte Menge sowohl für das Geschäftsjahr als auch für das Kalenderjahr berechnen und visualisieren?
Im folgenden Beispiel fängt das Geschäftsjahr im September an und es sollen täglich erreichte Ist-Mengen mit monatlich geplanten Soll-Mengen verglichen werden.
Ziel ist es, die monatliche Kumulierung der jeweiligen Ist- und Soll-Mengen für beide Varianten (Kalenderjahr und Geschäftsjahr) zu berechnen und darzustellen.
Die geplanten Soll-Mengen werden nur einmal im Monat geliefert, wohingegen die Ist-Werte fast täglich ermittelt werden. Damit liegen sie auf unterschiedlichen Ebenen der Zeit-Dimension.
Die DimDatum-Tabelle sieht so aus:
Durch Hinzufügen der 3 Tabellen in das Power Pivot-Datenmodell und der Verknüpfung beider Datum_ID mit der DimDatum-Tabelle entsteht folgendes Datenmodell:
Wichtig ist, dass die DimDatum-Tabelle mindestens eine Spalte mit Datumsformat beinhaltet und somit als Datumstabelle markiert werden kann, wie in der Abbildung oben zu sehen ist.
Im nächsten Schritt wird die Power Pivot DimDatum-Tabelle um die Spalte Monat_Name_GJ erweitert, welches dieselben Daten beinhaltet wie die Spalte Monat_Name_KJ. Die Besonderheit an der neu hinzugefügten Spalte ist, dass sie nach Monat_GJ sortiert wird, wie in der folgenden Abbildung zu sehen ist:
Analog dazu ist die Spalte Monat_Name_KJ nach Monat_KJ zu sortieren.
In den Berechnungsbereich von Menge_Ist werden folgende 3 Berechnungen eingetragen:
Ist Menge:=SUM([Menge_Ist])
Ist Menge kumuliert KJ:=TOTALYTD(SUM([Menge_Ist]);DimDatum[Datum_Kurz])
Ist Menge kumuliert GJ:=TOTALYTD(SUM([Menge_Ist]);DimDatum[Datum_Kurz];"31-08")
Wichtig: Der optionale Parameter „31-08“, der hinter DimDatum[Datum_Kurz] steht, darf nicht fehlen! Er kennzeichnet den letzten Tag im Geschäftsjahr und ist in diesem Beispiel der 31. August.
Analog werden diese Berechnungen in der Tabelle Menge_Soll durchgeführt:
Soll Menge:=SUM([Menge_Soll])
Soll Menge kumuliert KJ:=TOTALYTD(SUM([Menge_Soll]);DimDatum[Datum_Kurz])
Soll Menge kumuliert GJ:=TOTALYTD(SUM([Menge_Soll]);DimDatum[Datum_Kurz];"31-08")
Wenn eine Auswahl nach Jahr, Quartal und Monat erwünscht ist, lässt sich das durch Erstellen von Hierarchien realisieren:
Hier ist das Ergebnis als PivotTable und als PivotChart zu sehen:
Hier finden Sie die komplette Excel-Datei zum Download.