Einen Kalender in Power BI mit DAX erstellen

Die Kalender-Tabelle ist in jedem Datenmodell eine der wichtigsten Tabellen.
Eine Auswertung ohne zeitlichen Bezug macht in der Regel keinen Sinn.

Unsere Datentabelle liefert in aller Regel ein oder gar mehrere Daten. So gibt es z.B. bei einem Bestellvorgang das Bestelldatum, vielleicht ein Angebotsdatum, das Datum der Auftragsbestätigung, das Liefer- und das Rechnungsdatum.

Nun wäre es naheliegend, eines dieser Daten als Grundlage für unseren Kalender zu verwenden. Soll zum Beispiel eine Auswertung über den Umsatz erstellt werden, wäre das Rechnungsdatum das Mittel der Wahl.

Es empfiehlt sich allerdings in aller Regel nicht, das Datum direkt zu verwenden. Stattdessen sollte eine eigene Kalender-Tabelle auf Basis dieses Datums erstellt werden.

Nehmen wir folgendes (etwas konstruiertes) Beispiel an:

Innerhalb einer Woche hatten wir Umsätze am Montag und am Dienstag. Am Mittwoch fand ein Betriebsausflug statt, weshalb an diesem Tag keine Rechnungen geschrieben wurden. Am Donnerstag und Freitag wurden wieder Rechnungen erstellt.
Eine Auswertung in Power BI anhand des reinen Rechnungsdatums würde den Mittwoch nun einfach überspringen – Es gab Umsätze am Dienstag und dann wieder am Donnerstag. Dass der Umsatz am Mittwoch 0 war, ginge aus der Auswertung nicht hervor, da das Datum für den Mittwoch einfach nicht vorhanden ist.

Ein Kalender sollte alle Tage innerhalb eines Zeitraums lückenlos enthalten.

Eine gute Methode, einen Kalender innerhalb unseres Datenmodells zu erstellen, ist diesen mit Hilfe von DAX zu erstellen.

Gehen wir schrittweise vor:

Nehmen wir an, wir haben in Power BI eine Tabelle „Rechnungen“ und in dieser eine Spalte „Rechnungsdatum“.

Nun erstellen wir über den Reiter „Tabellentools“ eine neue Tabelle, indem wir auf die Schaltfläche „Neue Tabelle“ klicken.

In die Eingabeleiste schreiben oder besser kopieren wir den folgenden DAX-Ausdruck:

Kalender Umsatz =
CALENDAR (
    DATE ( YEAR (
        MIN ( 'Rechnungen'[Rechnungsdatum] )
    ), MONTH (
        MIN ( 'Rechnungen'[Rechnungsdatum] )
    ), 1 ),
    DATE ( YEAR (
        MAX ( 'Rechnungen'[Rechnungsdatum] )
    ), MONTH (
        MAX ( 'Rechnungen'[Rechnungsdatum] )
    ), 31 )
)

Das liest sich zugegebenermaßen zunächst etwas komplex.

Gehen wir den Ausdruck durch:

Die neue Tabelle, die wir erstellen, heißt „Kalender Umsatz“. Wir erzeugen den Kalender mit dem DAX-Ausdruck „CALENDAR“. CALENDAR erwartet zwei Parameter: ein Start-Datum und ein Ende-Datum.

Das Start- und Ende-Datum könnten wir hier fix eingeben, also „hart kodieren“. Viel eleganter ist es aber, aus der Spalte Rechnungsdatum das kleinste vorkommende Datum als das Start-Datum und das größte vorkommende Datum als das Ende-Datum zu verwenden.

Wir erzeugen also das Start-Datum mit dem DAX-Ausdruck „DATE“. DATE erwartet drei Parameter: das Jahr, den Monat und den Tag.
Das Jahr erzeugen wir mit dem DAX-Ausdruck „YEAR“. Der Funktion YEAR wiederum übergeben wir das kleinste vorkommende Rechnungsdatum mit dem Ausdruck „MIN(‚Rechnungen'[Rechnungsdatum])“. Als Tag schließlich übergeben wir hier 1, also den Monatsersten.

Mit dem Ende-Datum verfahren wir analog, allerdings verwenden wir hier statt MIN den DAX-Ausdruck „MAX“ um das größtmögliche Rechnungsdatum zu ermitteln.

Das Schöne ist, man kann diesen komplexen Ausdruck immer wieder verwenden und muss nur den Tabellennamen und die Datums-Spalte anpassen. Es hilft aber enorm bei der Fehlersuche, wenn man den Ausdruck ein mal verstanden hat.

Wir haben nun eine Tabelle „Kalender Umsatz“ mit einem Datum namens Date. Date können wir nun noch umbenennen in „Datum“. Die Spalte Datum ist nun zunächst formatiert als „Datum/Uhrzeit“. Hier setzen wir in den Spaltentools noch den Datentyp auf „Datum“ und das Format auf „Short Date“.

Jahr, Halbjahr, Quartal, Monat und Woche erzeugen

Als nächstes erzeugen wir in unserer neuen Tabelle „Kalender Umsatz“ jeweils eine neue Spalte für das Jahr, das Halbjahr, das Quartal, den Monat und die Woche.

Jahr

Im Reiter Spaltentools klicken wir auf „Neue Spalte“ um eine neue Spalte innerhalb unserer Kalender-Tabelle zu erzeugen.

Dann geben wir in die Eingabezeile folgenden DAX-Ausdruck ein:

Jahr =
YEAR ( 'Kalender Umsatz'[Datum] )

Wir erzeugen eine Spalte „Jahr“ mithilfe des DAX-Ausdrucks „YEAR“, dem wir als Parameter die Datum-Spalte unseres Kalenders übergeben.

Halbjahr

Wir erzeugen eine neue Spalte und geben folgenden DAX-Ausdruck ein:

Halbjahr =
IF (
    MONTH ( 'Kalender Umsatz'[Datum] ) <= 6,
    1,
    2
) & ".HJ/" & 'Kalender Umsatz'[Jahr]

Das liest sich so: die DAX-Funktion MONTH liefert die Monatszahl (nicht den Namen des Monats). 1 ist Januar, 2 ist Februar usw.
Wenn (IF) die Monatszahl kleiner oder gleich 6 ist, dann ist es das erste Halbjahr, sonst ist es das zweite Halbjahr. Wir hängen dann mit dem Verkettungsoperator & noch das Jahr an und es entsteht ein Zelleninhalt z.B. in dieser Form: „1.HJ/2021“ – also erstes Halbjahr 2021.

Quartal

Für das Quartal erstellen wir wieder eine neue Spalte und fügen folgenden DAX-Ausdruck ein:

Quartal =
ROUNDUP (
    MONTH ( 'Kalender Umsatz'[Datum] ) / 3,
    0
) & ".Q/" & 'Kalender Umsatz'[Jahr]

Auch hier wird wieder etwas Mathematik verwendet.
Nehme die Monatszahlt (MONTH), teile diese durch 3 und runde dann auf die nächste Ganzzahl auf (mit dem Ausdruck ROUNDUP). Dann hängen wir an den String noch das Jahr aus unserem Kalender an und erhalten ein Ergebnis wie z.B. „1.Q/2021“ für erstes Quartal 2021.

Monatszahl

Die Monatszahl benötigen wir lediglich als Sortierkriterium, um den Monatsnahmen später richtig sortieren zu können. Da kommen wir gleich nochmals drauf zu sprechen.

Wir ermitteln die Monatszahl mit diesem DAX-Ausdruck:

Monatszahl =
MONTH ( 'Kalender Umsatz'[Datum] )

Monatsname

Den Monatsnamen erstellen wir in der Kurzschreibweise (JAN, FEB, MAR…) mit Hilfe des folgenden DAX-Ausdrucks:

Monatsname =
FORMAT (
    'Kalender Umsatz'[Datum],
    "MMM"
)

Der Ausdruck „MMM“ gibt dabei an, dass wir den Monat in der aus drei Zeichen bestehenden Kurzschreibweise haben möchten.

Sortierung:

Hier kommt jetzt die Monatszahl ins Spiel.
Ohne weitere Angaben würde der Monatsname im Report nun alphabetisch sortiert werden und nicht in der kalendarischen Sortierung JAN, FEB, MAR, APR… usw.

Um die korrekte Sortierung zu erreichen gehen wir auf den Reiter „Spaltentools“, wählen unsere Spalte „Monatsname“ aus und klicken auf die Schaltfläche „Nach Spalte sortieren“.
In dem dann erscheinenden Dropdown-Menü wählen wir „Monatszahl“ aus.

Eindeutiger Monat

Oft ist es auch sinnvoll, einen Monat eindeutig zu bezeichnen, also z.B. „2022 Jan“. Auf diese Weise kann in einem Slicer ein konkreter Monat angesprochen werden und es werden nicht, sofern man kein Jahr ausgewählt hat, alle vorhandenen „Januare“ addiert.

Wir erreichen das mit diesem DAX-Ausdruck:

Monat eindeutig =
FORMAT (
    'Kalender Umsatz'[Datum],
    "YYYY MMM"
)

Damit auch dieser eindeutige Monat richtig sortiert wird, benötigen wir noch eine Hilfsspalte „Monat eindeutig Sort“. Nach dieser Spalte sortieren wir dann auf die gleiche Weise, wie oben beim Monat beschrieben.

Der DAX-Ausdruck lautet:

Monat eindeutig Sort =
FORMAT (
    'Kalender Umsatz'[Datum],
    "YYYYMM"
)

Woche

Die Kalenderwoche erstellen wir mit Hilfe des folgenden DAX-Ausdrucks:

Woche =
WEEKNUM (
    'Kalender Umsatz'[Datum],
    21
)

Der zweite Parameter „21“ gibt hier an, dass die Woche mit einem Montag beginnt und dass das europäische System zur Nummerierung von Wochen verwendet wird.

Im Editor wird der Parameter 21 womöglich rot unterstrichen dargestellt, jedenfalls in Power BI Desktop Version Juni 2023. Er funktioniert dennoch wie gewünscht.

Hier könnte auch noch eine „eindeutige Woche“ hilfreich sein in der Form z.B. „08/22“ – also KW 8 im Jahr 2022. Das Vorgehen ist analog wie oben beim eindeutigen Monat beschrieben.
Auch beim Verwenden einer eindeutigen Woche sollte jedoch die mit WEEKNUM ermittelte Wochennummer als Sortierspalte verwendet werden.

Woche eindeutig =
'Kalender Umsatz'[Woche] & "/"
    & RIGHT (
        'Kalender Umsatz'[Jahr],
        2
    )

Auch die eindeutige Woche sollte nach einer Sortierspalte sortiert werden:

Woche eindeutig Sort =
'Kalender Umsatz'[Jahr]
    & IF (
        LEN ( 'Kalender Umsatz'[Woche] ) = 1,
        "0" & 'Kalender Umsatz'[Woche],
        'Kalender Umsatz'[Woche]
    )

Tabellen-Beziehung herstellen

Abschließend ist nun noch wichtig, dass im Bereich „Modell“ von Power BI Desktop eine Beziehung zwischen der Fakten-Tabelle „Rechnungen“ und unserer neuen Tabelle „Kalender Umsatz“ hergestellt wird.

Wir erreichen dies, indem wir das Datum unserer Kalendertabelle auf das Rechnungsdatum in unserer Faktentabelle ziehen. Es wird so eine 1:n – Beziehung zwischen den beiden Tabellen aufgebaut.

Wir haben nun eine umfangreiche und vollständige Kalendertabelle, die wir in Slicern und Filtern verwenden können.

Als Datumstabelle markieren

Wir haben nun eine vollständige Kalender-Tabelle.
Nicht zwingend erforderlich, aber guter Stil ist es, diese Tabelle im Datenmodell nun noch als Datumstabelle zu markieren. Dazu klicken wir die Tabelle mit der rechten Maustaste an, wählen „Als Datumstabelle markieren“, nochmals „Als Datumstabelle markieren“ und geben in dem sich öffnenden Dialog noch das Feld an, welches unser Datum enthält. Durch dieses Vorgehen stellt Power BI im Hintergrund sicher, dass

• eindeutige Werte vorhanden sind
• keine NULL-Werte enthalten sind
• ein zusammenhängender Bereich von Datumswerten enthalten ist
• dass es sich um den Datentyp „Datum / Uhrzeit“ handelt