Wir haben bereits in diesem Blogbeitrag aus dem Jahr 2017 gezeigt, wie eine Datumsdimension in Power BI entweder mit M oder mit DAX dynamisch erzeugt werden kann. In der Zwischenzeit hat sich in unserer Trainings- und Beratungspraxis eine einfache und effektive Standardstruktur für die Datumsdimension etabliert, die wir hier vorstellen möchten. Weiters hat sich die Erzeugung mittels Power Query gegenüber der DAX Variante für uns als überlegen herausgestellt, dennoch werden in diesem Blogbeitrag beide Varianten vorgestellt.

Update vom 14.11.2022

Die Linearis Datumsdimension wurde auf die (geringfügig überarbeitete) Version 1.3 erweitert, die M-Codes dieses Blogbeitrags wurden gfg. aktualisiert und die DAX-basierten Lösungen wurden hinzugefügt.

Ausgangssituation

Die Linearis Datumsdimension liegt in der Version 1.3 vor und ist eine einfache und effektive Datumsdimension mit folgenden Feldern und Inhalten:

Die Linearis Datumsdimension liegt einerseits als Excel Datei vor, die Formeln für die Inhalte können bei Bedarf natürlich leicht an die eigenen Bedürfnisse angepasst werden:

Andererseits ist die Linearis Datumsdimension auch als M-Code für Power Query als DAX-Code für einen Calculated Table verfügbar, diese werden im folgenden vorgestellt.

1. Dynamische Berechnung mit Power Query / M

1.1 Berechnete Datumstabelle mit Power Query / M

Die dynamische Berechnung kann sehr einfach durch Erstellen einer Blank Query ...

... und Einfügen des folgenden M-Codes in das Advanced Editor Fenster erstellt werden.

Englischsprachige Datumstabelle:

let
    StartDate = #date(2017,1,1),
    EndDate = #date(2020,12,31),
    Source = List.Dates(StartDate, Number.From(EndDate)- Number.From(StartDate) + 1 ,#duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Column DATE" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type DATE" = Table.TransformColumnTypes(#"Renamed Column DATE",{{"Date", type date}}),
    #"Column Year" = Table.AddColumn(#"Changed Type DATE", "Year", each Date.Year([Date]), Int64.Type),
    #"Column Quarter" = Table.AddColumn(#"Column Year", "Quarter", each "Q" & Text.From(Date.QuarterOfYear([Date])), type text),
    #"Column Quarter Long" = Table.AddColumn(#"Column Quarter", "Quarter Long", each Text.From(Date.QuarterOfYear([Date])) & ". Quarter", type text),
    #"Column Quarter (SORT)" = Table.AddColumn(#"Column Quarter Long", "Quarter (SORT)", each Date.QuarterOfYear([Date]), Int64.Type),
    #"Column Quarter w/Year" = Table.AddColumn(#"Column Quarter (SORT)", "Quarter w/Year", each [Quarter] & " " & Text.From([Year]), type text),
    #"Column Quarter w/Year Long" = Table.AddColumn(#"Column Quarter w/Year", "Quarter w/Year Long", each [Quarter Long] & " " & Text.From([Year]), type text),
    #"Column Quarter w/Year (SORT)" = Table.AddColumn(#"Column Quarter w/Year Long", "Quarter w/Year (SORT)", each [Year] * 10 + Date.QuarterOfYear([Date]), Int64.Type),
    #"Column Month" = Table.AddColumn(#"Column Quarter w/Year (SORT)", "Month", each Date.ToText([Date], "MMM", "en-US"), type text),
    #"Column Month Long" = Table.AddColumn(#"Column Month", "Month Long", each Date.ToText([Date], "MMMM", "en-US"), type text),
    #"Column Month (SORT)" = Table.AddColumn(#"Column Month Long", "Month (SORT)", each Date.Month([Date]), Int64.Type),
    #"Column Month w/Year" = Table.AddColumn(#"Column Month (SORT)", "Month w/Year", each [Month] & " " & Text.From([Year]), type text),
    #"Column Month w/Year Long" = Table.AddColumn(#"Column Month w/Year", "Month w/Year Long", each [Month Long] & " " & Text.From([Year]), type text),
    #"Column Month w/Year (SORT)" = Table.AddColumn(#"Column Month w/Year Long", "Month w/Year (SORT)", each [Year] * 100 + [#"Month (SORT)"], Int64.Type),
    #"Column Week" = Table.AddColumn(#"Column Month w/Year (SORT)", "Week", each "Week " & Text.PadStart(Text.From(Date.WeekOfYear([Date], Day.Monday)),2,"0"), type text),
    #"Column Week (SORT)" = Table.AddColumn(#"Column Week", "Week (SORT)", each Date.WeekOfYear([Date], Day.Monday), Int64.Type),
    #"Column Week w/Year" = Table.AddColumn(#"Column Week (SORT)", "Week w/Year", each [Week] & " " & Text.From([Year]), type text),
    #"Column Week w/Year (SORT)" = Table.AddColumn(#"Column Week w/Year", "Week w/year (SORT)", each [Year] * 100 + [#"Week (SORT)"], Int64.Type),
    #"Column Week w/year (CALC)" = Table.AddColumn(#"Column Week w/Year (SORT)", "Week w/year (CALC)", each [Year] * 100 + [#"Week (SORT)"], Int64.Type),
    #"Column Day of Year" = Table.AddColumn(#"Column Week w/year (CALC)", "Day of Year", each (Number.From([Date]) - Number.From(#date([Year],1,1))) + 1, Int64.Type),
    #"Column Day of Quarter" = Table.AddColumn(#"Column Day of Year", "Day of Quarter", each Number.From([Date])- Number.From(#date([Year], [#"Quarter (SORT)"] * 3 -2, 1)) + 1, Int64.Type),
    #"Column Day of Month" = Table.AddColumn(#"Column Day of Quarter", "Day of Month", each Date.Day([Date]), Int64.Type),
    #"Column Weekday" = Table.AddColumn(#"Column Day of Month", "Weekday", each Date.ToText([Date], "ddd", "en-US"), type text),
    #"Column Weekday Long" = Table.AddColumn(#"Column Weekday", "Weekday Long", each Date.ToText([Date], "dddd", "en-US"), type text),
    #"Column Weekday (SORT)" = Table.AddColumn(#"Column Weekday Long", "Weekday (SORT)", each Date.DayOfWeek([Date],1), Int64.Type)
in
    #"Column Weekday (SORT)"

Deutschsprachige Datumstabelle:

let
    StartDatum = #date(2017,1,1),
    EndDatum = #date(2020,12,31),
    Quelle = List.Dates(StartDatum, Number.From(EndDatum)- Number.From(StartDatum) + 1 ,#duration(1,0,0,0)),
    #"Konvertiert in Tabelle" = Table.FromList(Quelle, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Umbenannte Spalte DATUM" = Table.RenameColumns(#"Konvertiert in Tabelle",{{"Column1", "Datum"}}),
    #"Datentyp DATUM" = Table.TransformColumnTypes(#"Umbenannte Spalte DATUM",{{"Datum", type date}}),
    #"Spalte Jahr" = Table.AddColumn(#"Datentyp DATUM", "Jahr", each Date.Year([Datum]), Int64.Type),
    #"Spalte Quartal" = Table.AddColumn(#"Spalte Jahr", "Quartal", each "Q" & Text.From(Date.QuarterOfYear([Datum])), type text),
    #"Spalte Quartal Lang" = Table.AddColumn(#"Spalte Quartal", "Quartal Lang", each Text.From(Date.QuarterOfYear([Datum])) & ". Quartal", type text),
    #"Spalte Quartal (SORT)" = Table.AddColumn(#"Spalte Quartal Lang", "Quartal (SORT)", each Date.QuarterOfYear([Datum]), Int64.Type),
    #"Spalte Quartal m/Jahr" = Table.AddColumn(#"Spalte Quartal (SORT)", "Quartal m/Jahr", each [Quartal] & " " & Text.From([Jahr]), type text),
    #"Spalte Quartal m/Jahr Lang" = Table.AddColumn(#"Spalte Quartal m/Jahr", "Quartal m/Jahr Lang", each [Quartal Lang] & " " & Text.From([Jahr]), type text),
    #"Spalte Quartal m/Jahr (SORT)" = Table.AddColumn(#"Spalte Quartal m/Jahr Lang", "Quartal m/Jahr (SORT)", each [Jahr] * 10 + Date.QuarterOfYear([Datum]), Int64.Type),
    #"Spalte Monat" = Table.AddColumn(#"Spalte Quartal m/Jahr (SORT)", "Monat", each Date.ToText([Datum], "MMM", "de-DE"), type text),
    #"Spalte Monat Lang" = Table.AddColumn(#"Spalte Monat", "Monat Lang", each Date.ToText([Datum], "MMMM", "de-DE"), type text),
    #"Spalte Monat (SORT)" = Table.AddColumn(#"Spalte Monat Lang", "Monat (SORT)", each Date.Month([Datum]), Int64.Type),
    #"Spalte Monat m/Jahr" = Table.AddColumn(#"Spalte Monat (SORT)", "Monat m/Jahr", each [Monat] & " " & Text.From([Jahr]), type text),
    #"Spalte Monat m/Jahr Lang" = Table.AddColumn(#"Spalte Monat m/Jahr", "Monat m/Jahr Lang", each [Monat Lang] & " " & Text.From([Jahr]), type text),
    #"Spalte Monat m/Jahr (SORT)" = Table.AddColumn(#"Spalte Monat m/Jahr Lang", "Monat m/Jahr (SORT)", each [Jahr] * 100 + [#"Monat (SORT)"], Int64.Type),
    #"Spalte Woche" = Table.AddColumn(#"Spalte Monat m/Jahr (SORT)", "Woche", each "KW " & Text.PadStart(Text.From(Date.WeekOfYear([Datum], Day.Monday)),2,"0"), type text),
    #"Spalte Woche (SORT)" = Table.AddColumn(#"Spalte Woche", "Woche (SORT)", each Date.WeekOfYear([Datum], Day.Monday), Int64.Type),
    #"Spalte Woche m/Jahr" = Table.AddColumn(#"Spalte Woche (SORT)", "Woche m/Jahr", each [Woche] & " " & Text.From([Jahr]), type text),
    #"Spalte Woche m/Jahr (SORT)" = Table.AddColumn(#"Spalte Woche m/Jahr", "Woche m/Jahr (SORT)", each [Jahr] * 100 + [#"Woche (SORT)"], Int64.Type),
    #"Spalte Woche m/Jahr (CALC)" = Table.AddColumn(#"Spalte Woche m/Jahr (SORT)", "Woche m/Jahr (CALC)", each [Jahr] * 100 + [#"Woche (SORT)"], Int64.Type),
    #"Spalte Tag des Jahres" = Table.AddColumn(#"Spalte Woche m/Jahr (CALC)", "Tag des Jahres", each (Number.From([Datum]) - Number.From(#date([Jahr],1,1))) + 1, Int64.Type),
    #"Spalte Tag des Quartals" = Table.AddColumn(#"Spalte Tag des Jahres", "Tag des Quartals", each Number.From([Datum])- Number.From(#date([Jahr], [#"Quartal (SORT)"] * 3 -2, 1)) + 1, Int64.Type),
    #"Spalte Tag des Monats" = Table.AddColumn(#"Spalte Tag des Quartals", "Tag des Monats", each Date.Day([Datum]), Int64.Type),
    #"Spalte Wochentag" = Table.AddColumn(#"Spalte Tag des Monats", "Wochentag", each Date.ToText([Datum], "ddd", "de-DE"), type text),
    #"Spalte Wochentag Lang" = Table.AddColumn(#"Spalte Wochentag", "Wochentag Lang", each Date.ToText([Datum], "dddd", "de-DE"), type text),
    #"Spalte Wochentag (SORT)" = Table.AddColumn(#"Spalte Wochentag Lang", "Wochentag (SORT)", each Date.DayOfWeek([Datum],1), Int64.Type)
in
    #"Spalte Wochentag (SORT)"

Der gewünschte Zeitraum wird in Zeile 2 und 3 über das StartDate und EndDate definiert. Nach der Bestätigung des Advanced Editors wird die Query erstellt und die Codezeilen werden als Steps im Step-Fenster rechts angezeigt, danach kann gleich noch der gewünschte Tabellenname (hier: Dim Date) eingesetzt werden:

Nach dem Laden in das Power BI Datamodel sind zur Finalisierung der Datumsdimension folgende Settings zu treffen:

• Definition der Tabelle als Date Table (= Voraussetzung für die Nutzung der DAX Time Intelligence Funktionen)
• Anzeigeformat für die Datumsspalte
• Default Summarization für die Wertspalten deaktivieren
• Sort-by-Column für alle Textspalten definieren
• SORT-Spalten ausblenden
• Hierarchien nach Bedarf definieren
• Display Folder nach Bedarf definieren

1.2 Dynamisierung des Zeitraums im Star und im Multi-Faktenschema

Anstelle der statischen Hinterlegung des Zeitraums über das StartDate und das EndDate in der Query sollte der Zeitraum besser dynamisch an die Faktentabelle(n) gebunden werden, damit es zu keinen undefinierten Datumswerten in der Beziehung zwischen Fakten- und Datumsdimension kommen kann.

Im Star Schema - hier gibt es genau 1 Faktentabelle - kann die Dynamisierung des StartDate und des EndDate mit der Table.Column Funktion erfolgen. Die Aggregationsfunktionen List.Min bzw. List.Max ermitteln das kleinste bzw. das größte Datum in der angegebenen Datumssspalte der Faktentabelle und die Funktionen Date.StartOfYear bzw. Date.EndOfYear normalisieren den Zeitraum der Datumsdimension auf den Jahresanfang (1.1.) und auf den Jahresultimo (31.12.), da die Datumsdimension immer nur aus ganzen Jahren bestehen sollte (und keine Rumpfjahre beinhalten sollte):

    StartDate = Date.StartOfYear(
        List.Min(Table.Column(#"Fact Sales","Order Date"))
        ),
    EndDate = Date.EndOfYear(
        List.Max(Table.Column(#"Fact Sales","Order Date"))
        ),

Das Statement wird in die Query im Advanced Editor an die folgende Stelle eingesetzt:

Im Multi-Fakten Schema - hier gibt es mehrere Faktentabellen - kann die Dynamisierung des StartDate und des EndDate mit der List.Combine Funktion erfolgen:

    StartDate = Date.StartOfYear(List.Min(List.Combine(
        {
            #"Fakten Arbeitsstunden"[Datum],
            #"Fakten Gehälter"[Datum],
            #"Fakten Headcount"[Datum]
        }
        ))),
    EndDate = Date.EndOfYear(List.Max(List.Combine(
        {
            #"Fakten Arbeitsstunden"[Datum],
            #"Fakten Gehälter"[Datum],
            #"Fakten Headcount"[Datum]
        }
        ))),

Das Statement wird in die Query im Advanced Editor an die folgende Stelle eingesetzt:

2. Dynamische Berechnung mit DAX

2.1 Berechnete Datumstabelle mit DAX

Bitte beachten Sie, daß die Referenz auf die Quellspalte [Date] fallweise angepaßt werden muß, je nachdem mit welchem Spaltennamen die erste Spalte (resultierend aus der CALENDAR()-Funktion) in Ihrer PBI Anwendung erzeugt wird. Bei Problemen erzeugen Sie eine Probetabelle nur mit dem CALENDAR() Abschnitt der folgenden DAX-Statements.

Englischsprachige Datumsdimension:

Dim Dates (en / DAX) = 
ADDCOLUMNS (
    CALENDAR(
        DATE(2017;1;1);
        DATE(2023;12;31)
    );
    "Year"; YEAR ( [Date] );
    "Quarter"; "Q" & QUARTER( [Date] );
    "Quarter Long"; QUARTER( [Date] ) & ". Quarter";
    "Quarter (SORT)"; QUARTER( [Date] );
    "Quarter w/Year"; "Q" & QUARTER( [Date] ) & " " & YEAR ( [Date] );
    "Quarter w/Year Long"; QUARTER( [Date] ) & ". Quarter " & YEAR ( [Date] );
    "Quarter w/Year (SORT)"; YEAR ( [Date] )*10 + QUARTER( [Date] );
    "Month"; FORMAT( [Date] ;"MMM";"en-US");
    "Month Long"; FORMAT( [Date] ;"MMMM";"en-US");
    "Month (SORT)"; MONTH( [Date] );
    "Month w/Year"; FORMAT( [Date] ;"MMM";"en-US") & " " & YEAR ( [Date] );
    "Month w/Year Long"; FORMAT( [Date] ;"MMMM";"en-US") & " " & YEAR ( [Date] );
    "Month w/Year (SORT)"; YEAR ( [Date] )*100 + MONTH( [Date] );
    "Week"; "Week " & FORMAT(WEEKNUM( [Date]; 2);"00");
    "Week (SORT)"; WEEKNUM( [Date]; 2);
    "Week w/Year"; "Week " & FORMAT(WEEKNUM( [Date]; 2);"00") & " " & YEAR ( [Date] );
    "Week w/Year (SORT)"; YEAR ( [Date] )*100 + WEEKNUM( [Date]; 2);
    "Week w/Year (CALC)"; YEAR ( [Date] )*100 + WEEKNUM( [Date]; 2);
    "Day of Year"; VALUE( [Date] - DATE(YEAR( [Date] );1;1) + 1 );
    "Day of Quarter"; VALUE( [Date] - DATE(YEAR( [Date] );QUARTER( [Date] )*3 -2;1) + 1 );
    "Day of Month"; DAY( [Date] );
    "Weekday"; FORMAT( [Date] ;"DDD";"en-US");
    "Weekday Long"; FORMAT( [Date] ;"DDDD";"en-US");
    "Weekday (SORT)"; WEEKDAY( [Date] ;2)
)

Deutschsprachige Datumsdimension:

Dim Datum (de / DAX) = 
ADDCOLUMNS (
    CALENDAR(
        DATE(2017;1;1);
        DATE(2023;12;31)
    );
    "Jahr"; YEAR ( [Date] );
    "Quartal"; "Q" & QUARTER( [Date] );
    "Quartal Lang"; QUARTER( [Date] ) & ". Quartal";
    "Quartal (SORT)"; QUARTER( [Date] );
    "Quartal m/Jahr"; "Q" & QUARTER( [Date] ) & " " & YEAR ( [Date] );
    "Quartal m/Jahr Lang"; QUARTER( [Date] ) & ". Quartal " & YEAR ( [Date] );
    "Quartal m/Jahr (SORT)"; YEAR ( [Date] )*10 + QUARTER( [Date] );
    "Monat"; FORMAT( [Date] ;"MMM";"de-de");
    "Monat Lang"; FORMAT( [Date] ;"MMMM";"de-de");
    "Monat (SORT)"; MONTH( [Date] );
    "Monat m/Jahr"; FORMAT( [Date] ;"MMM";"de-de") & " " & YEAR ( [Date] );
    "Monat m/Jahr Lang"; FORMAT( [Date] ;"MMMM";"de-de") & " " & YEAR ( [Date] );
    "Monat m/Jahr (SORT)"; YEAR ( [Date] )*100 + MONTH( [Date] );
    "Woche"; "KW " & FORMAT(WEEKNUM( [Date]; 2);"00");
    "Woche (SORT)"; WEEKNUM( [Date]; 2);
    "Woche m/Jahr"; "KW " & FORMAT(WEEKNUM( [Date]; 2);"00") & " " & YEAR ( [Date] );
    "Woche m/Jahr (SORT)"; YEAR ( [Date] )*100 + WEEKNUM( [Date]; 2);
    "Woche m/Jahr (CALC)"; YEAR ( [Date] )*100 + WEEKNUM( [Date]; 2);
    "Tag des Jahres"; VALUE( [Date] - DATE(YEAR( [Date] );1;1) + 1 );
    "Tag des Quartals"; VALUE( [Date] - DATE(YEAR( [Date] );QUARTER( [Date] )*3 -2;1) + 1 );
    "Tag des Monats"; DAY( [Date] );
    "Wochentag"; FORMAT( [Date] ;"DDD";"de-de");
    "Wochentag Lang"; FORMAT( [Date] ;"DDDD";"de-de");
    "Wochentag (SORT)"; WEEKDAY( [Date] ;2)
)

2.2 Dynamisierung des Zeitraums im Star und im Multi-Faktenschema

Im Star Schema - hier gibt es genau 1 Faktentabelle - kann die Dynamisierung des StartDate und des EndDate mit der MIN()- und MAX()-Funktion erfolgen.

Dim Datum (de / DAX) = 
ADDCOLUMNS (
    CALENDAR(
        DATE( YEAR( MIN('Fact Sales'[Order Date]) );1 ;1 );
        DATE( YEAR( MAX('Fact Sales'[Order Date]) );12 ; 31 )
    );

Im Multi-Fakten Schema - hier gibt es mehrere Faktentabellen - kann die Dynamisierung des StartDate und des EndDate mit der UNION()-Funktion erfolgen:

Dim Dates (de / DAX) = 
VAR varMinDate = MINX(
    UNION (
        ROW ("Value"; MIN('Fakten Arbeitsstunden'[Datum]));
        ROW ("Value"; MIN('Fakten Gehälter'[Datum]));
        ROW ("Value"; MIN('Fakten Headcount'[Datum]))
    );
    [Value]
)
VAR varMaxDate = MAXX(
    UNION (
        ROW ("Value"; MAX('Fakten Arbeitsstunden'[Datum]));
        ROW ("Value"; MAX('Fakten Gehälter'[Datum]));
        ROW ("Value"; MAX('Fakten Headcount'[Datum]))
    );
    [Value]
)

RETURN
ADDCOLUMNS (
    CALENDAR(
        DATE(YEAR(varMinDate);1;1);
        DATE(YEAR(varMaxDate);12;31)
    );

Ausbaumöglichkeiten

Die Formeln in den einzelnen Steps der Query sollten für die eigenen Zwecke adaptiert werden und bspw. das Gebietsschema von en-US auf de-DE geändert werden, um bei Bedarf deutschsprachige Periodentexte zu erzeugen. Auch sollten die Bezeichnungen der Spalten und die Schreibweisen der Perioden an die firmenspezifischen Anforderungen angepaßt werden.

Unternehmen mit abweichendem Wirtschaftsjahr erweitern die Datumsdimension um ein Set an Feldern zur Abbildung des Fiscal Year, Renate Schwenken liefert für diese Aufgabe im Rahmen unseres Power BI Camps ein entsprechendes Pattern zur effizienten Berechnung dieser Felder in Abhängigkeit vom individuellen Bilanzstichtag. Weiters können Felder für ein Reporting Year (4-4-5 Reporting), zur Abbildung von (länderspezifischen) Feiertagen, Berechnungsfelder für rollierende Zeitfilter oder tabellenbasierte Time Intelligence und vieles mehr ergänzt werden.