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
  

Den Kalender mit einer einzigen DAX-Funktion erstellen

Ergänzung 16.09.2024

Inzwischen habe ich eine Funktion erstellt, mit der ich den Kalender in einem Rutsch erstellen kann.

Hierzu in Power BI Desktop auf das Register „Modellierung“ gehen und dann auf „Neue Tabelle“ klicken.

Hier der Code:

Kalender = 
SELECTCOLUMNS (
    ADDCOLUMNS (
        CALENDAR (
            // Kalender mit fixem Start- und Enddatum
            DATE ( 2023, 1, 1 ),
            DATE ( 2024, 12, 31 ) // Kalender mit Start- und Enddatum basierend auf einer Datumsspalte in einer Faktentabelle (Beispiel)
            /*
            DATE ( YEAR (
                MIN ( Beispieltabelle[Beispieldatum] )
            ), MONTH (
                MIN ( Beispieltabelle[Beispieldatum] )
            ), 1 ),
            DATE ( YEAR (
                MAX ( Beispieltabelle[Beispieldatum] )
            ), MONTH (
                MAX ( Beispieltabelle[Beispieldatum] )
            ), 31 )            
            */
        ),
        "Jahr", YEAR ( [Date] ),
        -- Das Jahr. hier und im Folgenden muss zwingend 'Date' verwendet werden. Die Spalte wird weiter unten in "Datum" umbenannt
        "Halbjahr",
            -- das Halbjahr in der Form X.HJ/JJJJ
            IF (
                MONTH ( [Date] ) <= 6,
                1,
                2
            ) & ".HJ/"
                & YEAR ( [Date] ),
        "Halbjahr sort",
            -- Sortierspalte zum Sortieren des Halbjahres            
            YEAR ( [Date] )
                & IF (
                    MONTH ( [Date] ) <= 6,
                    1,
                    2
                ),
        "Quartal",
            -- Das Quartal in der Form x.Q/JJJJ
            ROUNDUP (
                MONTH ( [Date] ) / 3,
                0
            ) & ".Q/"
                & YEAR ( [Date] ),
        "Quartal sort",
            -- Sortierspalte zum Sortieren des Quartals
            YEAR ( [Date] )
                & ROUNDUP (
                    MONTH ( [Date] ) / 3,
                    0
                ),
        "Monatszahl", -- Der Monat als Zahl von 1 bis 12
        MONTH ( [Date] ),
        "Monatsname",
            -- Der Monatsname in der Form MON
            FORMAT (
                [Date],
                "MMM"
            ),
        "Monat eindeutig",
            -- Monat und Jahr kombiniert in der Form JJJJ MON
            FORMAT (
                [Date],
                "YYYY MMM"
            ),
        "Monat eindeutig sort",
            -- Sortierspalte zum Sortieren von Monat eindeutig
            FORMAT (
                [Date],
                "YYYYMM"
            ),
        "Woche Nr. ISO",
            -- Die Wochennummer im in der EU gebräuchlichen Format nach ISO-8601, beginnend mit Montag. KW 1 ist die erste Woche im Jahr mit 4 Tagen
            WEEKNUM (
                [Date],
                21
            ),
        "Woche eindeutig",
            -- Woche und Jahr kombiniert in der Form KW/JJ
            VAR Datum = [Date]
            VAR Jahr =
                YEAR ( Datum )
            VAR Woche =
                WEEKNUM (
                    Datum,
                    21
                )
            VAR ISOJahrTemp =
                IF (
                    AND (
                        Woche = 1,
                        MONTH ( Datum ) = 12
                    ),
                    Jahr + 1,
                    IF (
                        AND (
                            Woche >= 52,
                            MONTH ( Datum ) = 1
                        ),
                        Jahr - 1,
                        Jahr
                    )
                )
            VAR ISOJahr =
                RIGHT (
                    ISOJahrTemp,
                    2
                )
            RETURN
                FORMAT ( Woche, "00" ) & "/" & ISOJahr,
        "Woche eindeutig sort",
            -- Sortierspalte zum Sortieren von 'Woche eindeutig'
            VAR JahrTemp =
                YEAR ( [Date] )
            VAR WocheAnteil =
                IF (
                    LEN ( WEEKNUM ( [Date], 21 ) ) = 1,
                    "0" & WEEKNUM ( [Date], 21 ),
                    WEEKNUM (
                        [Date],
                        21
                    )
                )
            VAR JahrAnteil =
                IF (
                    AND (
                        WEEKNUM ( [Date], 21 ) = 1,
                        MONTH ( [Date] ) = 12
                    ),
                    JahrTemp + 1,
                    IF (
                        AND (
                            WEEKNUM ( [Date], 21 ) >= 52,
                            MONTH ( [Date] ) = 1
                        ),
                        JahrTemp - 1,
                        JahrTemp
                    )
                )
            VAR Ergebnis = JahrAnteil & WocheAnteil
            RETURN
                Ergebnis,
        "Ist heute",
            -- Flag, ob Datum heute ist
            IF (
                [Date] = TODAY (),
                "Ja",
                "Nein"
            ),
        "Ist diese Woche",
            -- Flag, ob Datum in dieser Woche lieg
            IF (
                WEEKNUM ( [Date], 21 ) & "/"
                    & RIGHT (
                        YEAR ( [Date] ),
                        2
                    )
                    = WEEKNUM ( TODAY (), 21 ) & "/"
                        & RIGHT (
                            YEAR ( [Date] ),
                            2
                        ),
                "Ja",
                "Nein"
            ),
        "Ist dieses Jahr",
            -- Flag, ob Datum in diesem Jahr liegt
            IF (
                YEAR ( [Date] ) = YEAR ( TODAY () ),
                "Ja",
                "Nein"
            ),
        "Ist dieser Monat",
            -- Flag, ob Datum in diesem Monat liegt
            IF (
                FORMAT (
                    [Date],
                    "YYYY MMM"
                )
                    = FORMAT (
                        TODAY (),
                        "YYYY MMM"
                    ),
                "Ja",
                "Nein"
            ),
        "Ist Monatsanfang",
            -- Flag, ob Datum der Monatsanfang ist oder nicht
            IF (
                DAY ( [Date] ) = 1,
                "Ja",
                "Nein"
            ),
        "Ist Ultimo",
            -- Flag, ob Datum der Ultimo des Monats ist oder nicht
            IF (
                [Date] = EOMONTH ( [Date], 0 ),
                "Ja",
                "Nein"
            )
    ),
    // Spalten neu anordnen und 'Date' in 'Datum' umbenennen
    "Datum", [Date],
    "Jahr", [Jahr],
    "Halbjahr", [Halbjahr],
    "Halbjahr sort", [Halbjahr sort],
    "Quartal", [Quartal],
    "Quartal sort", [Quartal sort],
    "Monatszahl", [Monatszahl],
    "Monatsname", [Monatsname],
    "Monat eindeutig", [Monat eindeutig],
    "Monat eindeutig sort", [Monat eindeutig sort],
    "Woche Nr. ISO", [Woche Nr. ISO],
    "Woche eindeutig", [Woche eindeutig],
    "Woche eindeutig sort", [Woche eindeutig sort],
    "Ist heute", [Ist heute],
    "Ist diese Woche", [Ist diese Woche],
    "Ist dieses Jahr", [Ist dieses Jahr],
    "Ist dieser Monat", [Ist dieser Monat],
    "Ist Monatsanfang", [Ist Monatsanfang],
    "Ist Ultimo", [Ist Ultimo]
)

Mit dem unveränderten obigen Code wird ein Kalender vom 01.01.2023 bis zum 31.12.2025 erzeugt.
Wenn wir einen rollierenden Kalender basierend auf einer Datumsspalte haben wollen, können wir die beiden Zeilen 7 und 8 auskommentieren und stattdessen den darunter auskommentierten Code-Block de-kommentieren.
In diesem Code-Block habe ich nur ein Beispiel-Datum in einer Beispiel-Tabelle angegeben, das muss natürlich durch eine reale Datums-Spalte ersetzt werden.

Der Kalender enthält auch noch vier zusätzliche Spalten: ‚Ist heute‘ prüft, ob das Datum heute ist, ‚Ist Woche‘ zeigt „Ja“, wenn das Datum in dieser Woche liegt, sonst „Nein“. Dann gibt es noch analog die Spalten ‚Ist dieser Monat‘ und ‚Ist dieses Jahr‘.

Ja, es wäre schöner, wenn ich nicht mehrfach z.B. ‚WEEKNUM ( [Date], 21 )‘ verwenden würde, sondern den Wert für die Woche in der Variable ‚wochennummer‘ speichern könnte. Das ist aber nicht so ohne Weiteres möglich, da Variablen in DAX nur innerhalb der Funktion gültig sind, in der sie deklariert wurden.
Ich könnte nun die Variable immer wieder neu deklarieren – die Lesbarkeit würde das etwas verbessern. Am Kernproblem würde das aber nichts ändern.
Schreibt mir gerne, wenn ihr hierfür eine Lösung wisst!

Und zu guter Letzt möchte ich noch auf das Kalenderprojekt von Lars Schreiber hinweisen.
Lars bietet eine von ihm erstellte Funktion für Power Query zum Download und zur freien Verfügung an.
Diese Kalendertabelle ist noch um einiges umfangreicher und leistungsfähiger als meine hier vorgestellte Kalendertabelle.
Unter anderem bietet der Kalender von Lars auch die Möglichkeit, ein abweichendes Fiskaljahr zu definieren.
Und mit dem von ihm ebenfalls bereitgestellten C#-Skript lassen sich auch sämtliche Sortierungen der einzelnen Datumsspalten automatisch vornehmen.

Schaut euch die Funktion von Lars Schreiber gerne mal hier an.