Power Pivot – KalendertabellenDynamische Kalendertabelle in Power Query erstellen und in Power Pivot nutzen

Was eine dynamische Kalendertabelle auszeichnet

Viele DAX-Funktionen zur Zeitintelligenz arbeiten nur dann korrekt, wenn ein vollständiger Kalender im Datenmodell vorhanden ist. 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 (t_Bestellungen) über das Datumsfeld verbunden und steuert so den Filterkontext für Ihre Measures.

Die in Power Pivot automatisch erstellte Kalendertabelle 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 wählen.

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(t_Bestellungen[Bestelldatum])))

Mit der Formel ermitteln Sie aus der Faktentabelle t_Bestellungen das früheste Bestelldatum. Dann setzen Sie das Datum auf den 1. Januar des entsprechenden Jahres zurück und wandeln das Ergebnis in eine Zahl um.

Diesen Zahlenwert benötigen Sie, um später mit dem Bereichsoperator {Start..Ende} eine Datumssequenz zu erzeugen.

Auswertung der Formel „von innen nach außen“

t_Bestellungen[Bestelldatum]: Greift auf die Spalte Bestelldatum der Abfrage oder Tabelle t_Bestellungen zu und gibt sie als Liste zurück.

List.Min(…): Ermittelt den kleinsten (frühesten) Wert in dieser Liste – das erste Bestelldatum. Achten Sie darauf, dass die Spalte mit dem Bestelldatum vom Typ Datum, Datum/Uhrzeit oder Datum/Uhrzeit/Zeitzone ist. Hinweis: null-Werte werden ignoriert, eine leere Liste führt zu einem Fehler.

Date.StartOfYear(…): Nimmt dieses kleinste Datum und gibt den Jahresanfang des jeweiligen Jahres (01.01.JJJJ) als Datumswert zurück. Beispiel: Aus 28.02.2022 wird 01.01.2022.

= Number.From(…): Konvertiert den Datumswert in einen Zahlenwert (Datumsserialzahl). Bezugsnullpunkt in Power Query ist 1899-12-30 (wie in Excel). Das Datum 01.01.2022 wird zu 44562.

Warum das Datum als Zahl?

Mit dem Bereichsoperator {Start..Ende} wird im Folgenden eine Datumliste erzeugt. Der Bereichsoperator {Start..Ende} funktioniert in der Power-Query-Sprache M nur mit Zahlen, nicht mit Datumswerten.

Darum wandelt man Startdatum und Enddatum 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(t_Bestellungen[Bestelldatum]))))

Die Formel ermittelt aus t_Bestellungen das jüngste (maximale) Bestelldatum, setzt es auf den 31.12. desselben Jahres, wandelt es in einen Datumstyp um und gibt schließlich die Datumsserienzahl (Zahl) zurück.

Dieser Zahlenwert eignet sich als „Ende“ für Datumsbereiche oder für {Start..Ende}-Sequenzen.

Auswertung von „innen nach außen“

t_Bestellungen[Bestelldatum]: Liest die Spalte Bestelldatum aus der Tabelle oder Abfrage t_Bestellungen als Liste aus.

List.Max(…): 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 Datum, Datum/Uhrzeit oder Datum/Uhrzeit/Zeitzone ist. Hinweis: null-Werte werden ignoriert, eine leere Liste führt zu einem Fehler.

Date.EndOfYear(…): Gibt zu einem Datum den letzten Tag des gleichen Jahres zurück (aus 15.07.2025 wird 31.12.2025). Diese Funktion akzeptiert den Datentyp Datum, Datum/Uhrzeit oder Datum/Uhrzeit/Zeitzone  und gibt in der Regel den gleichen „Datumsfamilien-Typ“ zurück, der hineingeht.

Date.From(…): Stellt sicher, dass das Ergebnis ein Datum ist (ohne Uhrzeit oder Zeitzone). Das ist nützlich, falls List.Max zuvor einen Wert im Format Datum/Uhrzeit oder Datum/Uhrzeit/Zeitzone  geliefert hat.

=Number.From(…): 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 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 mit 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 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 mit 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")

Wochentagnummer:= WEEKDAY(Kalendertabelle[Datum]; 2)

Kalenderwoche:= WEEKNUM(Kalendertabelle[Datum]; 21)

Für jede dieser Datumsangaben fügen Sie jeweils eine Spalte hinzu. Mit den genannten Formeln und dem Zeichen := werden die Spalten gleich entsprechend benannt (Spaltenname = Bezeichnung vor dem Zeichen :=). 

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 Wochentagnummer 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, Wochentagnummer) 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 neuen, zukünftigen 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.