Power Pivot und Tabular Model bieten mit dem Date Table und den darauf aufbauenden Funktionen zur Zeitintelligenz eine sehr leistungsfähige Technologie zur raschen Erstellung von berechneten Measures wie Year-to-Date, Vormonat, Vorjahresmonat, Vorjahr  und vieles mehr.

Zur Vorstellung dieser Funktionen verwenden wir unser bewährtes SAP-IDES Power BI Präsentationsmodell in Excel 2013.

Unser Ziel ist es, ein Power Pivot Datenmodell mit folgenden berechneten Measures zu erstellen:

Im ersten Schritt werden aus dem vereinheitlichten FLEX-Reporting! Measure die in dieser Analyseanwendung benötigten Untergruppen "Menge" und "Wert" ermittelt (Filter auf die Datacategory-Dimension). Anschließend sollen für das berechnete "Wert"-Measure die abgeleiteten Measures für

• Laufend
• Laufend YTD (= oranger Rahmen)
• Vormonat (= lila Rahmen)
• Vorjahr 1..12 (= brauner Rahmen)
• Vorjahresmonat und (= grüner Rahmen)
• Vorjahresmonat YTD (= blauer Rahmen)

ermittelt werden.

Die Umsetzung in Power Pivot erfolgt in 2 Schritten:

1. Erstellen und Definieren einer Tabelle als "Date Table" (Zeittabelle)
2. Erstellen der berechneten Measures mittels DAX-Formeln (Zeitintelligenz)

Im ersten Schritt wird also eine separate Zeittabelle in das Datenmodell eingefügt und mit der Transaktionsdatentabelle (hier: "Transaktionen CO-PA") verknüpft:

Diese Zeittabelle wird mit dem Befehl Entwurf -> Als Datumstabelle markieren als Zeittabelle im Datenmodell definiert und das Feld Zeit Datum KEY als eindeutiger Bezeichner der Zeittabelle bestimmt. Eine Datumstabelle in Power Pivot wird durch folgende Eigenschaften gekennzeichnet: (1) enthält eine Spalte im Datumsformat und (2) diese Datumsspalte enthält ausschließlich eindeutige Einträge. Diese zweite Bedingung erzwingt also, daß eine eigenständige Datumstabelle im Datenmodell vorhanden sein muß, um die Zeitintelligenz nutzen zu können. Berechnete Felder für Jahr, Quartal, Monat usw. in der Transaktionsdatentabelle reichen also nicht aus.

Im zweiten Schritt wird die eigentliche Zeitintelligenz in Form von berechneten Measures in das Datenmodell implementiert:

Hier die einzelnen DAX-Formeln zur SUMMEWENN-Ermittlung:

•  Wert:=CALCULATE(SUM([Measure]);FILTER('Transaktionen CO-PA';[Dateneinheit]="EUR.group"))
• Menge:=CALCULATE(SUM([Measure]);FILTER('Transaktionen CO-PA';[Dateneinheit]="Menge"))

Hier die einzelnen DAX-Formeln zur Realisierung der Zeitintelligenz:

• Laufend:=[Wert]
• Laufend YTD:=TOTALYTD([Wert];Zeitdimension[Zeit Datum KEY])
• Vormonat:=CALCULATE([Wert];PREVIOUSMONTH(Zeitdimension[Zeit Datum KEY]))
• Vorjahr 1..12:=CALCULATE([Wert];PREVIOUSYEAR(Zeitdimension[Zeit Datum KEY]))
• Vorjahresmonat:=CALCULATE([Wert];SAMEPERIODLASTYEAR(Zeitdimension[Zeit Datum KEY]))
• Vorjahresmonat YTD:=CALCULATE([Wert];SAMEPERIODLASTYEAR(DATESYTD(Zeitdimension[Zeit Datum KEY])))

Beachten Sie bitte, daß die DAX-Formeln zur Zeitintelligenz nur korrekte Werte zurückliefern, wenn zuvor der "Date Table" definiert wurde!

Noch deutlich leistungsfähiger als die PREVIOUS-Funktionen sowie die SAMEPERIODLASTYEAR-Funktion ist die DATEADD-Funktion, diese ermöglicht die freie Definition, um wieviele Perioden versetzt (rückwärts, vorwärts) abgefragt werden soll. Hier nur beispielhaft 3 Varianten zu den oben gezeigten Formeln:

• Vormonat:=CALCULATE([Wert];DATEADD(Zeitdimension[Zeit Datum KEY];-1;MONTH))
• Vorjahresmonat:=CALCULATE([Wert];DATEADD(Zeitdimension[Zeit Datum KEY];-1;YEAR))
• Vorjahresmonat:=CALCULATE([Wert];DATEADD(Zeitdimension[Zeit Datum KEY];-12;Month))

Natürlich ist noch deutlich mehr möglich, wie etwa eine dynamische Rolling-12-Month Berechnung, hier sind der Phantasie fast keine Grenzen gesetzt.

Kritisch ist anzumerken, daß die SAMEPERIODLASTYEAR- und DATEADD-Funktion nicht in Verbindung mit "querfilternden" Zeitfiltern verwendet werden können (bspw. Filterung des Wochentags oder des relativen Monats ohne Jahresbezug), deren Existenz (bspw. als Slicer in einem Pivottable) führt zu einer kryptischen Fehlermeldung.

Nach der Definition dieser berechneten Measures stehen diese im Datenmodell zur Auswahl zur Verfügung, neben dem eingangs verwendeten Pivottable sind natürlich auch Power View Auswertungen mit den neuen Measures jetzt sehr effektiv:

Nicht zu vergessen - alle gezeigten Funktionalitäten stehen natürlich auch im serverbasierten Tabular Model zur Verfügung bzw. werden beim Import des Power Pivot Datenmodells übernommen.

Quellennachweis:

office.microsoft.com/en-us/excel-help/understand-and-create-date-tables-in-power-pivot-in-excel-2013

blogs.msdn.com/b/analysisservices/archive/2010/04/12/time-intelligence-functions-in-dax

technet.microsoft.com/en-us/library/ee634763

office.microsoft.com/en-us/excel-help/totalytd-function-dax

bipassion.wordpress.com/2012/08/26/dax-sameperiodlastyear-and-datesytd

www.powerpivotpro.com/2009/12/powerpivot-dax-calculate-is-a-supercharged-sumif

Rechtlicher Hinweis

SAP und IDES sind geschützte Marken der SAG AG in Walldorf.