Linearis Datumsdimension in Power BI dynamisch erzeugen

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, daher wird hier nur noch die M-basierte Lösung vorgestellt.

Ausgangssituation

Die Linearis Datumsdimension liegt in der Version 1.2 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 verfügbar, dieser wird im folgenden vorgestellt.

Dynamische Berechnung 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:

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]))),
    #"Column Quarter Long" = Table.AddColumn(#"Column Quarter", "Quarter Long", each Text.From(Date.QuarterOfYear([Date])) & ". Quarter"),
    #"Column Quarter (SORT)" = Table.AddColumn(#"Column Quarter Long", "Quarter (SORT)", each Date.QuarterOfYear([Date])),
    #"Column Quarter w/Year" = Table.AddColumn(#"Column Quarter (SORT)", "Quarter w/Year", each [Quarter] & " " & Text.From([Year])),
    #"Column Quarter w/Year Long" = Table.AddColumn(#"Column Quarter w/Year", "Quarter w/Year Long", each [Quarter Long] & " " & Text.From([Year])),
    #"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")),
    #"Column Month Long" = Table.AddColumn(#"Column Month", "Month Long", each Date.ToText([Date], "MMMM", "en-US")),
    #"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])),
    #"Column Month w/Year Long" = Table.AddColumn(#"Column Month w/Year", "Month w/Year Long", each [Month Long] & " " & Text.From([Year])),
    #"Column Month w/Year (SORT)" = Table.AddColumn(#"Column Month w/Year Long", "Month w/Year (SORT)", each [Year] * 100 + [#"Month (SORT)"]),
    #"Column Week" = Table.AddColumn(#"Column Month w/Year (SORT)", "Week", each "Week " & Text.PadStart(Text.From(Date.WeekOfYear([Date], Day.Monday)),2,"0")),
    #"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])),
    #"Column Week w/Year (SORT)" = Table.AddColumn(#"Column Week w/Year", "Week w/year (SORT)", each [Year] * 100 + [#"Week (SORT)"]),
    #"Column Week w/year (CALC)" = Table.AddColumn(#"Column Week w/Year (SORT)", "Week w/year (CALC)", each [Year] * 100 + [#"Week (SORT)"]),
    #"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),
    #"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),
    #"Column Day of Month" = Table.AddColumn(#"Column Day of Quarter", "Day of Month", each Date.Day([Date])),
    #"Column Weekday" = Table.AddColumn(#"Column Day of Month", "Weekday", each Date.ToText([Date], "ddd", "en-US")),
    #"Column Weekday Long" = Table.AddColumn(#"Column Weekday", "Weekday Long", each Date.ToText([Date], "dddd", "en-US")),
    #"Column Weekday (SORT)" = Table.AddColumn(#"Column Weekday Long", "Weekday (SORT)", each Date.DayOfWeek([Date]), Int64.Type),
    #"Changed Type" = Table.TransformColumnTypes(#"Column Weekday (SORT)",{{"Quarter", type text}, {"Quarter w/Year", type text}, {"Month Long", type text}, {"Month", type text}, {"Month w/Year Long", type text}, {"Month w/Year", type text}, {"Week", type text}, {"Week w/Year", type text}, {"Weekday Long", type text}, {"Weekday", type text}, {"Quarter Long", type text}, {"Quarter (SORT)", Int64.Type}, {"Month w/Year (SORT)", Int64.Type}, {"Day of Year", Int64.Type}, {"Day of Quarter", Int64.Type}, {"Day of Month", Int64.Type}, {"Quarter w/Year Long", type text}, {"Week w/year (CALC)", Int64.Type}})
in
    #"Changed Type"

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

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:

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.

Power BI Camp - Präsenztrainings in Wien und Nürnberg!

Dashboarding mit Power BI, DAX & Datenmodellierung und Power Query - als einzelnes Modul oder als ganze Trainingswoche!

Nürnberg 07. bis 11. Juni 2021
Wien 21. bis 25. Juni 2021

Jetzt buchen und Early Bird Rabatt sichern.
Early Bird!
Share on linkedin
Share on twitter
Share on facebook

Leave a Replay

Über den Autor

Blog auf Feedly abonnieren

Kategorien

Verwandte Beiträge

Kostenlos zum Newsletter anmelden

Ihre Anfrage

Schicken Sie uns Ihre Fragen und Anregungen!