Power Pivot für die DatenanalyseDynamische Kalendertabelle in Power Query erstellen und in Power Pivot nutzen
Viele DAX-Funktionen zur Zeitintelligenz arbeiten nur dann korrekt, wenn ein vollständiger Kalender im Datenmodell vorhanden ist. Das betrifft beispielsweise kumulierte Umsatzberechnungen (Year-to-Date, YTD), Vergleiche mit dem Vorjahr oder gleitende Durchschnitte.
Die Kalendertabelle bildet das Rückgrat aller zeitbasierten Auswertungen:
- Jeder Tag ist genau einmal enthalten – ohne Lücken.
- Die Kalendertabelle enthält Zusatzinformationen wie Jahr, Monat, Quartal oder Wochentag.
- Sie ist mit dynamischen Faktentabellen im Datenmodell wie zum Beispiel Bestelldaten (tbl_Bestellungen) über das Datumsfeld verbunden und steuert so den Filterkontext für Ihre Measures.
Die in Power Pivot automatisch erstellte Datumstabelle ist zwar praktisch, hat aber Schwächen; zum Beispiel ein falsches Startdatum, amerikanische Wochentagslogik oder fehlende Dynamik. Deshalb sollten Sie eine dynamische Kalendertabelle mit Power Query erstellen, die sich automatisch an Ihre Daten anpasst.
Dynamische Kalendertabelle mit Power Query erstellen
Öffnen Sie den Power Query-Editor, indem Sie im Menüband die Befehlsfolge Registerkarte Daten > Befehlsgruppe Daten Abrufen & transformieren > Befehl Daten abrufen > Befehl Power Query-Editor starten.
Erstellen Sie zunächst eine leere Abfrage, indem Sie im Menüband die Befehlsfolge Registerkarte Start > Befehlsgruppe Neue Abfrage > Befehl Neue Quelle > Befehl Andere Quellen > Leere Abfrage.
Beginndatum der Kalendertabelle berechnen
Es öffnet sich eine leere Abfrage. Erfassen Sie hier im ersten Schritt Quelle die folgende Formel:
= Number.From( Date.StartOfYear( List.Min(tbl_Bestellungen[Bestelldatum])))
Mit der Formel ermitteln Sie aus der Faktentabelle tbl_Bestellungen das früheste Bestelldatum. Dann setzen Sie das Datum auf den 1. Januar desselben Jahres zurück und wandeln das Ergebnis in eine Zahl um.
Diesen Zahlenwert nutzt man häufig, um später mit dem Bereichsoperator {Start..Ende} eine Datumssequenz zu erzeugen.
Auswertung der Formel „von innen nach außen“
tbl_Bestellungen[Bestelldatum]: Greift auf die Spalte Bestelldatum der Abfrage oder Tabelle tbl_Bestellungen zu und gibt sie als Liste zurück.
List.Min(tbl_Bestellungen[Bestelldatum]): Ermittelt den kleinsten (frühesten) Wert in dieser Liste, das erste Bestelldatum. Achten Sie darauf, dass die Spalte mit dem Bestelldatum vom Typ date oder datetime ist. Hinweis: null-Werte werden ignoriert, eine leere Liste führt zu einem Fehler.
Date.StartOfYear(List.Min(tbl_Bestellungen[Bestelldatum])): Nimmt dieses Datum und gibt den Jahresanfang des jeweiligen Jahres (01.01.jjjj) als Datumswert zurück. Beispiel: Aus 29.02.2016 wird 01.01.2016.
= Number.From(Date.StartOfYear( List.Min(tbl_Bestellungen[Bestelldatum])) ): Konvertiert den Datumswert in einen Zahlenwert (Datumsserialzahl). Bezugsnullpunkt in Power Query ist 1899-12-30 (wie in Excel). Das Datum 01.01.2016 wird zu 42370.
Warum das Datum als Zahl?
Mit dem Bereichsoperator {Start..Ende} wird im Folgenden eine Datumliste erzeugt. Der Bereichsoperator {Start..Ende} in M funktioniert nur mit Zahlen, nicht mit Datumswerten.
Darum wandelt man Startdatum und Enddatum häufig in Zahlen um, erzeugt die Sequenz {Start..Ende} und konvertiert diese Zahlen anschließend wieder zu Datumswerten – so entsteht eine Datumsreihe (Kalender).
Enddatum der Kalendertabelle berechnen
Klicken Sie in der Bearbeitungsleiste des Power-Query-Editors links auf die Schaltfläche fx und erzeugen Sie einen neuen Schritt. Standardmäßig wird hier der Name Benutzerdefiniert1 vergeben.
Erfassen Sie unter diesem neuen Schritt jetzt die folgende Formel in der Bearbeitungsleiste:
=Number.From( Date.From( Date.EndOfYear( List.Max(tbl_Bestellungen[Bestelldatum]))))
Die Formel ermittelt aus tbl_Bestellungen das jüngste (maximale) Bestelldatum, setzt es auf den 31.12. desselben Jahres, wandelt es in einen reinen Datumstyp um und gibt schließlich die Datumsserienzahl (Zahl) zurück.
Solch ein Zahlenwert eignet sich zum Beispiel als „Ende“ für Datumsbereiche oder für {Start..Ende}-Sequenzen.
Auswertung von „innen nach außen“
tbl_Bestellungen[Bestelldatum]:Liest die Spalte Bestelldatum aus der Tabelle oder Abfrage tbl_Bestellungen als Liste aus.
List.Max(tbl_Bestellungen[Bestelldatum]): Liefert den größten Wert aus der Liste – hier also das jüngste Bestelldatum. Achten Sie darauf, dass die Spalte mit dem Bestelldatum vom Typ date oder datetime ist. Hinweis: null-Werte werden ignoriert, eine leere Liste führt zu einem Fehler.
Date.EndOfYear(List.Max(tbl_Bestellungen[Bestelldatum])): Gibt zu einem Datum den letzten Tag des gleichen Jahres zurück (aus 2024-07-15 wird 2024-12-31). Diese Funktion akzeptiert date, datetime, datetimezone und gibt in der Regel den gleichen „Datumsfamilien-Typ“ zurück, der hineingeht.
Date.From(Date.EndOfYear(List.Max(tbl_Bestellungen[Bestelldatum]))): Stellt sicher, dass das Ergebnis ein reines Datum ist (ohne Uhrzeit oder Zeitzone). Das ist nützlich, falls List.Max zuvor einen Wert im Format datetime/datetimezone geliefert hat.
=Number.From(Date.From(Date.EndOfYear( List.Max(tbl_Bestellungen[Bestelldatum])))): Wandelt das Datum wieder in die Datumsserienzahl um (Tage seit 1899-12-30).
Kalendertabelle erstellen
Klicken Sie wieder auf fx in der Bearbeitungsleiste. Es wird automatisch ein Schritt mit dem Namen Benutzerdefiniert2 erstellt. Erzeugen Sie nun eine Liste aus den beiden Datumswerten, die Sie berechnet haben, indem Sie die folgende Formel in der Bearbeitungsleiste bei diesem Schritt erfassen:
= {Quelle..Benutzerdefiniert1}
Unter dem Schritt Quelle haben Sie das Anfangsdatum und unter dem Schritt Benutzerdefiniert1 das Enddatum berechnet.
Sie erzeugen mit dieser Formel nun eine Liste zwischen diesen beiden Werten.
Wandeln Sie die Liste jetzt in eine Tabelle um, indem Sie im Menüband die Befehlsfolge Listentools > Transformieren > Zu Tabelle ausführen.
Es öffnet sich das Dialogfeld Zu Tabelle. Bestätigen Sie die Standardeinstellungen, indem Sie das Dialogfeld durch Klick auf OK schließen.
Die Liste wurde in eine Tabelle umgewandelt. Benennen Sie nun die Spalte in Datum um. Doppelklicken Sie hierzu mit der linken Maustaste auf den Spaltennamen und überschreiben Sie diesen mit dem Wort Datum.
Passen Sie jetzt den Datentyp dieser Spalte auf Datum an. Klicken Sie am linken Rand der Spaltenüberschrift auf das Symbol für die Datentypen (ABC123) und wählen Sie hier in der Liste den Eintrag Datum aus.
Benennen Sie nun die Abfrage1 in Kalendertabelle um. Doppelklicken Sie hierzu mit der linken Maustaste auf den Namen Abfrage1 und überschreiben Sie diesen.
Sie haben nun eine automatische Liste an Datumswerten erstellt, die sich Ihrem aktuellen Datenbestand dynamisch anpasst. Das bedeutet: Enthält die Tabelle mit den Bestellungen bei einer späteren Datenaktualisierung andere Werte für das Bestelldatum, wird die Kalendertabelle automatisch angepasst.
So funktionieren die folgenden Auswertungen auch für die Folgejahre.
Kalendertabelle in das Datenmodell übernehmen
Damit Sie diese dynamische Datumsliste in Power Pivot verwenden können, müssen Sie diese in das Datenmodell übernehmen. Klicken Sie daher im Menüband auf die Befehlsfolge Registerkarte Start > Befehlsgruppe Schließen > Befehl Schließen & laden > Befehl Schließen & laden in…
Es öffnet sich das Dialogfeld Daten importieren. Aktivieren Sie hier das Optionsfeld Nur Verbindung erstellen. Dadurch werden die Daten nicht in ein Excel-Arbeitsblatt zurückgeschrieben.
Aktivieren Sie als Nächstes unten links das Kontrollkästchen Dem Datenmodell diese Daten hinzufügen. Durch diese Einstellung wird die Kalendertabelle dem Datenmodell hinzugefügt und Sie können diese Kalendertabelle in Power Pivot einsetzen und verwenden.
Bestätigen Sie Ihre Einstellungen, indem Sie das Dialogfeld durch Klick auf OK schließen.
Zusätzliche Spalten mit Datumsangaben mit DAX ergänzen
Sie können die Kalendertabelle jetzt im Power-Pivot-Editor einsetzen und verwenden. Öffnen Sie den Power-Pivot-Editor, indem Sie im Menüband die Befehlsfolge Registerkarte Power Pivot > Befehlsgruppe Datenmodell > Befehl Verwalten ausführen.
Hier finden Sie jetzt die neue dynamische Kalendertabelle.
Hilfreiche Zeitangaben in der Kalendertabelle ergänzen
Ihre Kalendertabelle enthält bisher nur die Spalte Datum. Für aussagekräftige Analysen benötigen Sie jedoch weitere Zeitmerkmale. Diese fügen Sie in Power Pivot als berechnete Spalten mit den folgenden Formeln hinzu:
Jahr = YEAR(Kalendertabelle[Datum])
Monat = FORMAT(Kalendertabelle[Datum]; "MMMM")
Monatsnummer = MONTH(Kalendertabelle[Datum])
Quartal = "Q" & ROUNDUP(Kalendertabelle[Monatsnummer] / 3; 0)
Wochentag = FORMAT(Kalendertabelle[Datum]; "dddd")
Wochentag-Nr. = WEEKDAY(Kalendertabelle[Datum]; 2)
Kalenderwoche = WEEKNUM(Kalendertabelle[Datum]; 21)
Für jede dieser Datumsangaben fügen Sie jeweils eine Spalte hinzu und benennen diese Spalte entsprechend.
In der Spalte mit dem Quartal wird ein Q dem Quartalswert vorangestellt. Das können Sie für Ihre Zwecke natürlich auf andere Art gestalten.
Zur Berechnung der Kalenderwoche legen Sie den sogenannten return_type = 21 fest. So wird die Kalenderwoche nach dem ISO-8601-Kalenderstandard festgelegt und beginnt mit einem Montag.
Tipp: Stellen Sie die Spalte Monat nach Monatsnummer und Wochentag nach Wochentag-Nr. sortieren ein. Im Beitrag zu den Datumsfunktionen in Power Pivot erfahren Sie, wie Sie die Wochentage und Monate richtig sortieren (nicht nach Alphabet).
Danach können Sie die Hilfsspalten (Monatsnummer, Wochentag-Nr.) in der Feldliste ausblenden, um die Übersicht zu verbessern.
Fazit
Die Kalendertabelle ist das Herzstück für alle zeitbasierten Analysen mit Power Pivot. Mit einer dynamischen Kalendertabelle aus Power Query vermeiden Sie die Schwächen der Standardlösung in Power Pivot.
Die Vorteile der dynamischen Kalendertabelle sind:
- Automatisch aktuell: Die Kalendertabelle wächst automatisch mit Ihren Bestellungen.
- Speicherschonend: Es werden nur die wirklich benötigten Tage geladen.
- Erweiterbar: Sie können alle relevanten Zeitdimensionen (Jahr, Monat, Quartal, Wochentag etc.) ergänzen.
- Flexibel: Sie können die Tabelle leicht um Geschäftsjahre, Kalenderwochen oder Feiertage erweitern.
- Konsistent: Alle Pivot-Tabellen und DAX-Measures greifen auf denselben Kalender zurück.
Damit schaffen Sie die Grundlage für professionelle Auswertungen und können alle Vorteile der Time-Intelligence-Funktionen in DAX nutzen.
