[Serie] Kalendertabellen: Kalender mit Feiertagen in DAX erzeugen

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 ergänzen wir Feiertage mit DAX?

Kalendertabellen

Im ersten Teil der Serie habe ich dir gezeigt, warum man Kalendertabellen verwendet, wie diese eigentlich definiert sind und wie viele man davon benötigt. Im zweiten Teil der Serie ging es darum, wie man Kalendertabellen erzeugt - natürlich gibt es mehrere Wege. Im dritten Teil haben wir die Kalendertabelle in Power Query um Feiertage erweitert. In diesem Artikel machen wir das auch - nur in diesem Fall mit DAX Mitteln.

Warum noch eine DAX-Variante

Im zweiten Teil der Serie habe wir schon etwas beleuchtet, welcher Weg welchen Vorteil bietet. Früher war ich Verfechter der externen Excel-Tabelle mit Datumsinformationen. Das liegt jetzt etwas am Alter: In Power Pivot wurde die Funktionalität der Generierung einer Datumstabelle noch nicht angeboten und Power BI war noch ein frommer Wunsch. Heute nutze ich vermehrt die DAX Variante. Weil hier die Dynamik, also wann beginnt meine Datumstabelle und wann endet sie, viel leichter und viel performanter abbildbar ist, als mit allen anderen Varianten.

Aber auch dann benötigen wir natürlich Feiertage in der Datumstabelle.

Ostern mit DAX

Im letzten Teil der Kalender-Serie haben wir die Gaußsche Osterformel schon angesprochen. Die Berechnung ist in DAX natürlich identisch. Der Umgang ist in DAX aber ein anderer. Da DAX keine imperative Programmiersprache ist, sondern eher deklarativ ist (also ncht: wie mache ich etwas, sondern: ich beschreibe, wie mein Ergebnis aussieht), kann man in DAX auch keine Funktionen schreiben. DAX kennt aber Measures. Und in einer Measure kann ich festlegen, dass diese nur dann ausgeführt wird, wenn zum Zeitpunkt der Ausführung des Measures ein Jahr einer Kalendertabelle eindeuig ausgewählt sein muss. In diesem Kontext berechnet das Measure dann eben das Osterdatum (andernfalls nichts, also BLANK()).

Folgendes Measure gibt (bei eindeutig gewähltem Jahr) das Osterdatum zurück:

Um das Ergebnis zu testen legen wir einfach eine Tabelle in einem Power BI Bericht an (also eine Matrix). Wir gehen davon aus, dass wir schon eine Kalendertabelle haben (am besten die aus dem zweiten Teil mit DAX erzeugt). Als erste Spalte in der Matrix wählen wir das Jahr aus der Kalendertabelle. In der zweiten Spalte unser neues Maasure EasterDate:

Und siehe da: Für jedes Jahr gibt das Measure das korrekte Osterdatum zurück.

Ziel: Feiertagstabelle

Wie in der Power Query Variante aus dem letzten Artikel benötigen wir eine Feiertagstabelle mit zwei Spalten: Datum, Feiertag. Dafür benötigen wir DAX-Funktionen, die dir vermutlich nicht so bekannt sein dürften:

• Row()
• Union(
• Generate()
• und später: GenerateSeries()

ROW()

In Power BI oder Excel Power Pivot kommt man nicht ganz so häufig in Verlegenheit dynamisch eine Tabelle mit einer Zeile anzulegen. Bis jetzt! Die Funktion Row() legt eine Tabelle mit einer Datenzeile und beliebig vielen Spalten an. Die Spaltennamen werden als Zeichenkette übergeben. So können wir beispielsweise eine Tabelle mit dem Feiertag Neujahr 2020 anlegen:

Da man eine Tabelle nicht direkt in ein Measure übergeben darf, habe ich im Beispiel ein COUNTROWS() um die Ergebnistabelle verwendet. Das Ergebnis ist natürlich immer 1. Es geht aber hier um die Verwendung der Funktion ROW().

UNION()

Eine Tabelle an eine andere andere hängen nennt man in SQL UNION (wie in DAX eben auch), in Access kennen es viele als APPEND oder Anfügeabfrage. Wenn ich in einer neuen Tabelle in DAX mehrere Zeilen haben möchte, generiere ich mehrere Tabellen mit einer Zeile und füge diese mit UNION() zusammen. Beispielsweise so:

Alternativ kannst du in Power BI in der Tabellen-Sicht auch einfach mal auf Neue Tabelle klicken.

Die Tabelle erzeugst du aus dem UNION() Ausdruck oben, also:

Die Tabelle hat dann schonmal ein paar Feiertage:

GENERATE()

GENERATE() wertet die Tabelle in Parameter zwei für jede Zeile in Parameter eins aus. Oder mit anderen Worten: GENERATE() erzeugt das Kreuzprodukt aus zwei Tabellen. Beispiel:

Das Ergebnis sieht wie folgt aus:

Diese Funktionalität nutzen wir für die Feiertage. Der UNION() Ausdruck liefert uns Zeilen für beispielsweise ein paar Feiertage. Wenn wir das für jedes Jahr haben wollen, erzeugen wir ein Kreuzprodukt aus allen Jahren der Kalendertabelle und dem Ergebnis von UNION(). Eine Tabelle nur mit den Jahren erhalten wir über die Funktion DISTINCT().

Jetzt ist nur in jedem Feiertag das Jahr 2020 fest hinterlegt. Dadurch, dass pro Zeile das Jahr (Kalender[Jahr]) als Spalte eingefügt wird (durch das Kreuzprodukt), dürfen wir auf den Wert dynamisch in der zweiten Tabelle zugreifen- genauso, wie wenn wir eine berechnete Spalte im Datenmodell anlegen würden. Damit dürfen wir schreiben:

Und Ostern?

Klar, jetzt fehlt noch Ostern und die Feiertage die mit Ostern in Verbindung stehen.

Weiter oben haben wir ein Measure definiert, das solange ein Jahr eindeutig ausgewählt ist, das zu dem Jahr passende Oster-Datum zurückgibt. Dadurch, dass GENERATE() ja über DimKalender[Jahr] iteriert, ist in jeder Zeile das Jahr eindeutig bestimmt. Wenn jetzt im UNION() Audruck ein Measure aufgerufen wird, dann sorgt die Kontext-Transition dafür, dass das Jahr Teil des Filter-Kontextes ist (schau dir gerne nochmal den Artikel über Kontext-Transition in Blog an).

Die anderen Feiertagen, die von Ostern abhängig sind, können wir dann leicht berechnen. Wenn man in DAX eine (ganze) Zahl von einem Datum abzieht oder addiert, verschiebt man das Datum dadurch um die entsprechende Anzahl Tage. Ergo ergibt sich für unsere Feiertagstabelle:

Dubletten

Danke an den Hinweis eines aufmerksamen Lesers (siehe Kommentare unten): Es kann vorkommen, dass zwei Feiertage auf das gleiche Datum fallen. Beispielsweise am 1.Mai 2008. Hier ist "Tag der Arbeit" UND "Christi Himmelfahrt". Für 2008 hätte die alte (jetzt korrigierte) Berechnung nicht funktioniert. Bei meinen Feiertagen wären das zwischen den Jahren 2000-2999 zwar nur 8 Fälle gewesen - betrachtet man aber nicht nur bundeseinheitliche Feiertage können das schon mehr werden.

Bei der alten Berechnung wäre der 01.05.2008 also doppelt vorgekommen. Doppelte Datumseinträge in der Kalendertabelle führen zu Fehlern. Daher ist unten in der Berechnung noch ein GROUPBY() eingeführt worden. Gelöst ist es so, dass in der Feiertagsspalte die Feiertage mit / getrennt aufgezählt werden.

Die Formel unten mit GROUPBY sieht relativ komplex aus. Schnell fragt man sich, warum das so kompliziert formuliert ist. Das liegt daran, dass temporäre Tabellen keinen Zeilenkontext besitzen und die bekannten Funktionsweisen dann nicht mehr funktionieren. Sprengt aber den Rahmen dieses Artikels.

Alles zusammen

Im Prinzip kann man an dieser Stelle aufhören. Beide Tabellen (Kalender und Feiertage) kann man über die Datumsspalte verknüpfen und Informationen via RELATED() in die Kalendertabelle einfügen oder bei entsprechenden DAX-Berechnungen einach die Beziehung zu den Feiertagen berücksichtigen. Möchte man aber eine Kalendertabelle mit Feiertagen haben und das nur mit eienr DAX-Abfrage, benötigt man noch eine weitere Funktion: GenerateSeries(). Und zwar als Alternative zu DISTINCT().

Dadurch, dass wir alles in einer DAX-Abfrage haben möchten, legen wir die Kalendertabelle an und speichern das Ergebnis in einer Variable. Auf die Spalte DimKalender[Jahr] können wir daher nicht zugreifen, da die Tabelle DimKalender nicht existiert. Man müsste dann die Spalte Jahr über den Variablen-Namen aufrufen: DISTINCT(varKalender[Jahr]) - und das ist in DAX nicht erlaubt.

Für GENERATE() benötigen wir aber eine Tabelle mit den Jahreszahlen. Dafür bedienen wir uns einer anderen Funktion: GENERATESERIES(). Diese Funktion legt einfach eine Tabelle mit einer Spalte und Werten an, die man via Start, Ende und Increment definiert. Der Spaltenname der erzeugten Tabelle lautet schlicht: Value.

Damit können wir die Kalendertabelle inkl. Feiertage komplett mit einer DAX-Abfrage erzeugen:

Für die, die sich fragen, warum da noch die Funktion SELECTCOLUMNS() auftaucht: GENERATE liefert drei Spalten, nämlich das Jahr (Spalte Value), ein Datum und den Feiertag. Das Jahr brauchen wir aber nicht mehr. Mit SELECTCOLUMNS wird die Spalte entfernt.

NATURALLEFTJOIN() ist der letzte wichtige Schritt. Die Funktion fügt die beiden Tabellen zusammen. LEFT-JOIN bedeutet, dass jede Zeile aus der linken Tabelle in jedem Fall übernommen wird. Wird in der "rechten" Tabelle eine Entsprechnung gefunden, werden die entsprechenden Spalten mit den Werten gefüllt. Ansonsten bleiben die Spalten leer (BLANK()). "Entsprechung" bedeutet übrigens, dass NATURALLEFTOUTERJOIN() nach Spalten sucht, die in beiden Tabellen identisch heißen und den gleichen Datentyp besitzen. Alle diese Spalten müssen übereinstimmen, damit die Werte in einer Zeilen zusammengefügt werden.