Kalendertabellen gehören in (fast) jede Auswertung. Aber was sind Kalendertabellen? Wozu und wie viele benötigt man? Wie geht man mit Urlaub und Betriebsferien um? Wie verhält sich Power BI ohne Kalendertabelle? Wir finden: genug Stoff für eine Serie. In diesem Teil: Wie erzeugt man Kalendertabellen?

Kalendertabelle

Im ersten Teil der Serie haben ich dir gezeigt, warum man Kalendertabellen verwendet, wie diese eigentlich definiert sind und wie viele man davon benötigt. In diesem Teil schauen wir uns an, welche Wege es gibt Kalendertabellen zu erzeugen

Quellsysteme haben bereits eine Kalendertabelle

Wir fangen mal wirklich ganz einfach an: Wenn du beispielsweise auf ein Datawarehouse zugreifst, wirst du dort sehr sicher schon eine oder mehrere Kalendertabellen finden. Das Modell dort ist sehr vermutlich auf die Kalendertabellen abgestimmt (Zeitzonen etc.). Importier die vorhandene Kalendertabelle und nutze sie.

Kalendertabelle in Excel

Eine Kalendertabelle kann man sich ganz einfach in Excel erzeugen. Das geht ganz schnell: Excel öffnen, die erste Spalte mit der Überschrift Datum versehen, ein Startdatum eintragen (beispielsweise 1.1.2010) und dann mit der Autovervollständigung via Maus solange Daten erzeugen, bis ein gewünschtes Enddatum erreicht ist. Und dann weitere Spalten erzeugen - beispielsweise mit folgenden Formeln:

  • Jahr: =Jahr(A2)
  • Quartal: =AUFRUNDEN(MONAT(A2)/3;0)
  • Quartal und Jahr: = C2 & "-"& TEXT(G2;"00")
  • QuartalName: ="Q" & D2
  • Monat: =MONAT(A2)
  • Monatname: =TEXT(G2;"MMMM")
  • Jahr und Monat: = I2 & " " & C2
  • DayOfYear: =A2-DATUM(JAHR(A2);1;0)
  • DayOfMonth: =TAG(A2)
  • Weekday: =WOCHENTAG(A2;2)
  • WeekdayName: =TEXT(WOCHENTAG(A2);"TTTT")

Diese Excel-Datei importierst du dann in dein Datenmodell. Beim Import kannst du dann einschränken, ob du wirklich alle Jahre importieren willst oder nicht.

Kalendertabelle in Power BI

Excel Power Pivot-Nutzer kennen die Funktion, eine Kalendertabelle erzeugen zu lassen (im Power Pivot Datenmodell unter Entwurf-Datumstabelle-Neu). Je nachdem, ob man in diesem Moment einen Datumswert in einer Tabelle markiert hat oder nicht, nimmt Power Pivot die ausgewählte Datumsspalte oder sucht sich in allen Tabellen eine Spalte mit Datentyp Datum. Die markierte oder gefundene Datumsspalte dient als Grundlage: Power Pivot sucht sich von dieser Spalte den Maximal- und den Minimal-Wert - streng genommen eigentlich nur das jeweilige Jahr der beiden Werte. Denn die neue Datumstabelle startet am 1.1. des kleinsten und endet am 31.12. des größten Jahres. Weitere Spalten werden automatisch angelegt.

Wo aber ist diese Funktionalität in Power BI?

In Power BI kann man, anders als in Excel Power Pivot, Tabellen im Datenmodell via DAX anlegen. So kann man beispielsweise eine neue Tabelle auf Basis einer vorhandenen Tabelle anlegen:

  • 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 neue Tabelle heißt Bestellungen Retail und hat nur die Zeilen mit IsOnlineOrder=False(). Um eine solche Formel eingeben zu können wählt man im Menü von Power BI im Reiter Modellierung die Funktion Neue Tabelle.

Achtung: In der Diagrammsicht ist diese Funktion nicht vorhanden. Man findet sie nur in der Berichtssicht und in der Datensicht.

In Power BI eine neue Tabelle anlegen

Um Datumsinformationen zu erzeugen, kennt Power BI die Funktion CALENDAR. Die Funktion erhält einen Datumswert als Startwert und einen Datumswert als Endwert. Ein Datum erzeugt man über die Funktion DATE():

  • 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

Willst du also eine Kalendertabelle DimKalender mit Start am 1.1.2018 und Ende am 31.12.2020 erzeugen, dann lautet die Formel:

  • 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

Ergebnis von CALENDAR()

Alle weiteren Spalten werden mit berechneten Spalten hinzugefügt. Beispielsweise:

  • Jahr = YEAR(DimKalender[Date])
  • Monat = MONTH(DimKalender[Date])
  • Monatname = FORMAT(DimKalender[Date],"MM")

Tabellenausdruck für die Kalendertabelle

Wenn man sich jetzt über die Funktion CALENDAR eine Kalendertabelle erzeugt hat, möchte man diese vermutlich in einer anderen Auswertung wiederverwenden. Leider lassen sich die manuell angelegten Benutzer-Spalten aber nicht so einfach in die nächste Auswertung kopieren. Da Power BI aber Tabellenausdrücke versteht, kann man das Anlegen der berechneten Spalten auch direkt mit DAX Formeln ausdrücken. Und diese Formel lässt sich prima kopieren:

  • 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

ADDCOLUMNS erhält als ersten Parameter die Tabelle, die ergänzt werden soll. In unserem Fall existiert die zu ergänzende Tabelle noch nicht, sondern wird von Calendar() erzeugt. Die Tabelle besteht nur aus einer Spalte, nämlich der Spalte Date. Für jede anzufügende Spalte übergibt man der Funktion einen Nanmen und einen DAX-Ausdruck. Wenn du als Benutzer eine neue Spalte anlegst, passiert intern nichts anderes.

Dynamik in der DAX-Kalendertabelle

Gerade hat das Jahr 2020 angefangen. Mancher Kunde rief mich an: "Meine Auswertung zeigt seltsame Daten an".

Das liegt dann oft daran, dass die Kalendertabelle im Datenmodell am 31.12.2019 endet. also erweitern wir die Kalendertabelle auf den 31.12.2020. Das gleiche Problem trifft einen dann am 1.1.2021 erneut - und wer weiß schon noch, wo das Problem vergangenes Jahr lag und wie man es gelöst hat...

Eine Variante ist, man legt einfach die Kalendertabelle weit in die Zukunft. Vielleicht bis zum 31.12.2025. Diese Jahre stören allerdings auf der Oberfläche in den Berichten. Die Datenschnitte zeigen dann die Jahre 2021, 2022, 2023, 2024 und 2025 an - obwohl noch gar keine Daten vorhanden sind. Natürlich kann man auch das durch geschickte Filter unterdrücken.

Ein anderer Weg ist, die Funktion CALENDAR im obigen Ausdruck nicht auf fixe Datumsinformationen anzuwenden, sondern auf dynamische. Sei beispielsweise das Feld Order[Date] das Datumsfeld, welches bestimmt, wie lange die Kalendertabelle erzeugt werden soll. Dann würde ich die Min- und die Max-Werte dieser Spalte ermitteln und in diesen Grenzen die Kalendertabelle erzeugen. Außerdem würde ich die Kalendertabelle nicht unterjährig beginnen bzw. enden lassen, sondern jeweils immer vom 1.1. bis zum 31.12. Also ändern wir die Formel leicht ab. Die Variablen dienen der Lesbarkeit. Solltest du nicht wissen, was Variable in DAX sind, lies einfach unseren Artikel zu Variablen in DAX:

  • 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 leidigen Kalenderwochen

Kalenderwochen sind in Datenauswertungen...sagen wir: unangenehm. Bei den Amerikaner beginnt die Kalenderwoche Sonntags, bei den Europäern Montags. Kalenderwochen passen nicht so richtig in ein Jahr. Je nachdem bleibt ein Tag übrig (normales Jahr) oder zwei (Schaltjahr). Damit ist der 1.1. nicht immer ein Montag und die letzte Kalenderwoche des Vorjahres nicht immer abgeschlossen.

Die ISO 8601 definiert, dass die erste Kalenderwoche immer den Donnerstag beinhalten muss. Wenn also der 1.1. ein Montag, ein Dienstag, ein Mittwoch oder ein Donnerstag ist, dann beginnt das Jahr mit der KW1. Die Tage im alten Jahr gehören dann schon zur Kalenderwoche des neuen Jahres.

Beginnt das Jahr mit einem Freitag, Samstag oder Sonntag gehören diese Tage noch zur Kalenderwoche des alten Jahres.

Und dann möchte man möchte häufig Kalenderwochen mit der jeweilig vorherigen vergleichen. In DAX gibt es dazu keine guten Funktionen - wenn man sich schon nicht auf einen Kalenderwochenstandard einigen kann, wie sollen dann mit Vorkalenderwochen-Vergleiche funktionieren.

Abhilfe: Man definiert einfach eine Spalte mit einem Index (KW ISO8601 Index). Bei einer Auswertung nimmt man dann einfach den Index der aktuellen KW und zieht 1 ab - und landet so bei der vorherigen.

Die komplette Funktion in DAX

  • 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

Kalendertabelle via Power Query

Eine Kalendertabelle können wir auch in Power Query erzeugen. Die grundsätzliche Vorgehensweise ist identisch zu der Erstellung mit DAX:

  • Abfrage anlegen
  • Funktion ausführen, die eine Datumsspalte erzeugt
  • Benutzerspalten hinzufügen

Die Funktion zum Erzeugen von Datumswerten heißt in Power Query List.Dates. Für die Kalendertabelle in Power Query legen wir also eine leere Abfrage an und wechseln in den erweiterten Editor. Dort fügen wir folgenden Code ein:

List.Dates erhält als ersten Parameter ein Startdatum. In Power Query gibt es verschiedenen Varianten ein Datum zu erzeugen. Die Variante mit #date ist vermutlich die schnellste. Der zweite Parameter ist die Anzahl an Datumsinformationen, die erzeugt werden sollen. Für das erste Beispiel 365 Tage - wenn wir also Tage verwenden als Intervall kommen wir genau auf dem 31.12.2018 aus.

wenn wir also Tage verwenden bedeutet: Der dritte Parameter ist der spezielle Datentyp Duration in Power Query (den es in DAX leider nicht gibt). Eine Duration legt man beispielsweise mit #duration an und übergibt

  • Anzahl Tage
  • Anzahl Stunden
  • Anzahl Minuten
  • Anzahl Sekunden

Das Ergebnis von List.Dates ist eine Spalte mit fortlaufender Datumsinformation.

Power Query Liste mit Datumsinformationen

Wichtig zu verstehen ist, dass wir es jetzt erstmal mit einer Liste, nicht mit einer Tabelle zu tun haben. Listen haben genau eine Spalte, die keinen Namen benötigt. Um die Liste in eine Tabelle zu wandeln, bedienen wir uns der entsprechenden Transformation, die in Power Query angeboten wird.

Power Query Liste in Tabelle wandeln

Die erste Spalte hat jetzt den Standardnamen Column1 - diesen solltest du am besten gleich in Date oder Datum umbenennen. Den Datentyp legst du auf Datum fest.

Im Menü im Reiter Spalte hinzufügen kann man über Benutzerdefinierte Spalte beliebig viele Spalten hinzufügen. Beispielsweise Jahre, Monate und Monatsnamen, die jeweils auf die erste Spalte [Datum] verweisen:

  • Jahr: Date.Year([Date])
  • Monat: Date.Month([Date])
  • Monatsname: Date.ToText([Date],"MMMM")

Die leidigen Schaltjahre

Im vorherigen Beispiel habe ich den 1.1.2018 als Startpunkt und 365 Tage als Länge des Intervalls verwendet. Wenn man die Kalendertabelle jetzt auf 10 Jahre erweitern will, steht man vor dem leidigen Schaltjahrproblem. Denn: Wie viele Tage muss ich denn jetzt beispielsweise auf den 1.1.2010 draufrechnen, wenn ich 10 Jahre abbilden will?

Natürlich gibt es dafür eine Lösung: Die Funktion Duration.Days gibt für eine Dauer (Datentyp Duration) die Anzahl Tage zurück. Zieht man ein Datum von einem anderen Datum ab, wird das Ergebnis als Duration zurückgegeben. Die obige Abfrage in Power Query ändern wir dann wie folgt:

Kalenderwochen in Power Query

In Power Query gibt es leider keine eingebaute Variante, um die Kalenderwoche gemäß ISO 8601 zu berechnen. Daher haben wir eine eigene Funktion entwickelt, die später in die gesamte Abfrage eingebettet wird;

Record als Rückgabe bedeutet, dass die Funktion eben zwei Rückgabewerte hat. Nämlich die Kalenderwoche und das Jahr, zu der die Kalenderwoche gehört. Das benötigen wir für die Darstellung im Format YYYY/WW. Beispiel:

  • 01.01.2020: 2020/01
  • 31.12.2019: 2020/01 (obwohl das Kalenderjahr des Datums 2019 ist)
  • 01.01.2010: 2009/53 (obwohl das Kalenderjahr des Datums 2010 ist)

Datumstabelle erzeugen als Funktion

Um die Sache noch rund zu bekommen, können wir die Erzeugung der Datumstabelle in eine Funktion auslagern. Die Funktion erhält zwei Datumsinformationen als Grenzen und gibt eine Datumstabelle zurück:

Die Funktion können wir leicht testen:

Power Query Funktion zum Erzeugen einer Kalendertabelle

Die komplette Funktion in M (Power Query)

Hier noch eine vollständige Version als Kopiervorlage:

Welche Variante ist die Beste?

Standardantwort: es kommt drauf an! Ich verwende tatsächlich alle Varianten:

  • Excel-Tabelle: Wenn diese schon vorhanden ist und mein Kunde hier vielleicht sogar Betriebsferien etc. pflegt, dann nehme ich selbstverständlich diese Kalenderinformation
  • Power BI: Diese Variante hat den Charme, dass man die Grenzen (Start/Ende) dynamisch anpassen kann. Das geht in den anderen Varianten nicht oder nur mit mehr Aufwand
  • Power Query: Manchmal benötige ich die Datumsinformationen schon in Power Query - um hier beispielsweise Berechnungen durchführen zu können. Dann kann ich die Datumstabelle nicht mit DAX in Power BI berechnen, weil mir die Daten dann zu spät vorliegen. Problematisch finde ich allerdings, dass man die Grenzen der Kalendertabelle manuell anpassen muss. Außer, man will in Poweer Query beispielsweise den Min- und den Max-Wert einer entsprechenden Abfrage berechnen und diese Werte für die Erzeugung der Kalendertabelle verwenden. Dann wird die Abfrage mit den Daten (meist eine große Faktentabelle) aber mindestens zweimal ausgeführt: einmal , um die Daten für die Auswertung zu laden und ein weiteres Mal, um das Minimum und Maximum zu ermitteln - und davor schrecke ich aus Performance-Gründen zurück.

Im nächsten Teil betrachten wir abweichende Geschäftsjahre, abweichende Controlling-Perioden und das Thema Feiertage und Betriebsferien.

Schneller zum Power BI Pro?

Wir schulen Power BI mit Power Query und der Abfragesprache DAX in unseren Trainings. Oder wir erstellen mit unseren Kunden in Workshops direkt gemeinsam benötigte Reports. Mit unseren Ergebnissen kann unser Kunde dann selbst weiterarbeiten. Oder wir übernehmen das Reporting, wenn die eigenen Mitarbeiter fehlen oder überlastet sind.

Interessiert? Dann einfach Kontakt aufnehmen. Entweder per E-Mail oder gleich zum Telefonhörer greifen!