Die meisten Auswertungen haben einen Zeitbezug. Beispielsweise möchten wir die Summe der Ausgaben für Bestellungen wissen – allerdings nicht die Gesamtsumme als eine Zahl, sondern den zeitlichen Verlauf. So kann man beispielsweise saisonalen Schwankungen auf die Spur kommen.

Beispiel:

In der Datenbank AdventureWorks finden wir Bestellungen in der Tabelle PurchaseOrderHeader: image Wir benötigen zwei Felder: OrderDate (das Bestelldatum) und SubTotal (die Summe des Auftrags). Das Feld Monat ist berechnet:

  • US
    Für Power-BI oder englisches Excel
    formatieren (Komma statt Semikolon).
    Mehr Infos
  • DE
    Für deutsches Excel formatieren
    (Semikolon statt Komma).
    Mehr Infos

Die Spalte Monat stellt damit das Datum monatsweise im Format yyyyMM dar. Erstellen wir anschließend aus den Daten ein Pivot-Chart als Kurve der Summe der Umsätze über die Zeit (OrderDate), nehmen die Spalte Monat als Datenschnitt (Slicer) und wählen als Monat im Slicer 200804, erhalten wir folgendes Ergebnis: image Der x-Achsen-Abstand zwischen dem 01.04.2008 sowie dem 03.04.2008 ist gleich groß wie der Abstand zwischen dem 03.04.2008 und 04.04.2008, obwohl dieser doppelt so breit sein sollte. Der Abstand zwischen dem 07.04.2008 und dem 10.04.2008 müsste dreimal so groß sein.

Das Problem

Excel nimmt sich aus den Daten der Tabelle PurchaseOrderHeader alle verfügbaren OrderDates und trägt diese auf der x-Achse ab. Fehlt ein Datum, weil am entsprechendem Tag keine Ware bestellt wurde, fehlt der Eintrag. Das gleiche würde man erhalten, möchte man die verkaufte Stückzahl aller Produkte wissen, die man im Produktstamm hat. Nimmt man nur die Informationen aus den Verkaufsaufträgen, erhält man nur Ergebnisse für Produkte, die tatsächlich verkauft wurden. Wurde ein Produkt nie verkauft, wird es im Ergebnis fehlen – obwohl man eventuell das Produkt mit Verkaufsmenge 0 sehen möchte. Man würde niemals von Excel verlangen, dass es das fehlende Produkt “errät” und mit “0” anzeigt. Bei Zeitdimensionen ist das gleich: Excel müsste wissen, dass es sich um eine Zeitdimension handelt, und dass der Schritt zwischen zwei Punkten immer ein Tag beträgt. Aber das weiß Excel nicht.

Die Lösung: eine Datumstabelle

Wie bei einer Produkttabelle, die als Produktdimension verwendet wird, erstellen wir für die Zeit ebenfalls eine Tabelle, eine Datumsdimension. Pro gewünschter Zeiteinheit wird ein Satz in der Tabelle angelegt. Möchte man beispielsweise eine Auflösung von einem Tag haben, legt man für jeden Tag einen Datensatz an (möchte man stundengenaue Aussagen muss man entsprechend pro Stunde einen Satz anlegen). Eine solche Datumstabelle könnte wie folgt aussehen: image Die Formeln für diese Spalten lauten:

  • DateKey: das Datum, keine Formel
  • Year: =Jahr(A2)
  • Quarter: =AUFRUNDEN(MONAT(A2)/3;0)
  • QuarterAndYear: = C2 & "-"& TEXT(G2;"00")
  • QuarterName: ="Q" & D2
  • Month: =MONAT(A2)
  • MonthAndYear: =C2 & "/" & TEXT(G2;"00")
  • MonthName: =TEXT(G2;"MMMM")
  • MonthNameAndYear: =I2 & " " & C2
  • DayOfYear: =A2-DATUM(JAHR(A2);1;0)
  • DayOfMonth: =TAG(A2)
  • Weekday: =WOCHENTAG(A2;2)
  • WeekdayName: =TEXT(WOCHENTAG(A2);"TTTT")

Diese Datumstabelle können Sie im Downloadsbereich herunterladen (01.01.2000 – 31.12.2020). Diese Tabelle wird in Power Pivot importiert. Damit Power Pivot die Datumstabelle als solche erkennt, muss diese bekannt gemacht werden. Dies erfolgt über das Menü über die Schaltfläche Als Datumstabelle markieren: image Neben allen anderen Informationen benötigt die Datumstabelle immer ein Feld, in dem das Datum als vollständiges Datum vorhanden ist. Dieses Feld müssen Sie angeben: image Dadurch werden in der Pivot-Table / im Pivot-Chart viele Datumsfunktionen angeboten.

Verknüpfen der Datumstabelle

Anschließend muss sie mit der Faktentabelle verknüpft werden. Dies erfolgt über den DateKey. Hinweis: Es gilt als “Best Practice” einen künstlichen Schlüssel sowohl in der Datumstabelle als auch in der Faktentabelle zu erstellen – üblicherweise das Datum im Format yyyyMMdd (also für den 01. April 2013 den Schlüssel 20130401). Über diesen Schlüssel wird dann die Verknüpfung der Tabellen hergestellt. In Power Pivot wird seitens Microsoft explizit davon abgeraten. Die Datumsfelder werden direkt verknüpft. Achtung: Stellen Sie sicher, dass das Datum in Ihrer Faktentabelle keine Uhrzeiten enthält. Siehe dazu den Artikel zur Datums-Bereinigung. Über das Feld DateKey lassen sich die beiden Tabellen jetzt verknüpfen: image Unser Pivot-Chart ändern wir jetzt wie folgt: image An den Lücken hat sich bis jetzt noch nichts geändert. Das liegt daran, das Excel die Daten jetzt tatsächlich kennt, allerdings in Pivot-Tabellen automatisch Nullwerte unterdrückt werden. Diese können wir sichtbar machen, indem wir in den Pivot-Optionen der zum Pivot-Chart gehörenden Pivot-Tabelle die Anzeige von Nullwerten konfigurieren: image

image Anschließend verändert sich der Pivot-Chart wie folgt: image

Lösung via Measures

Bei Power Pivot führen viele Wege nach Rom. Natürlich gibt es auch die Möglichkeit in der Datumstabelle ein berechnetes Feld anzulegen, welches die SubTotal-Werte der PurchaseOrderHeader-Tabelle addieren. Verwendet man als Fakt dann diese Werte, werden die 0-Werte ebenfalls angezeigt. Das gleiche gilt, wenn man ein Measure definiert, dass Nullwerte explizit umwandelt. Beispielsweise:

  • US
    Für Power-BI oder englisches Excel
    formatieren (Komma statt Semikolon).
    Mehr Infos
  • DE
    Für deutsches Excel formatieren
    (Semikolon statt Komma).
    Mehr Infos