Dynamischer Kalender in Power BI

Jedes Datenmodell benötigt eine Zeitdimension in Form eines Kalenders. Aber nicht immer steht ein solcher Kalender out-of-the-box zur Verfügung. Damit manuelle Arbeiten wegfallen, zeige ich Euch, wie ein dynamischer Kalender in Power BI oder Power Query für Excel einfach erstellen werden kann.

Ausgangspunkt

Ausgangspunkt ist eine stark vereinfachte Faktentabelle namens „Sales“.Salestabelle

Aufgrund dieser Tabelle erstellen wir nun einen Kalender, der mit dem ersten Eintrag (1.1.2010) beginnt und mit dem Monatsende des letzten Eintrags (29.2.2016) endet. Zudem soll der Kalender noch einige zusätzliche Angaben wie Jahr, Quartal, Monat, Tag, Wochentag aufweisen.

Erste Schritte

Als erstes laden wir als Quelle die Salestabelle in eine neue Query. Ausser der Datumsspalte werden alle anderen Spalten entfernt, die Datumsspalte noch um allfällige Duplikate bereinigt und das Datumsformat korrekt gesetzt.

Quelle = Sales,
Datum_behalten = Table.SelectColumns(Quelle,{"Datum"}),
Duplikate_entfernen = Table.Distinct(Datum_behalten),
In_Datumstyp_aendern = Table.TransformColumnTypes(Duplikate_entfernen,{{"Datum", type date}}),

Wir ihr sieht, geschieht bis hierhin noch nichts Spezielles.

Startdatum ermitteln

Als nächstes sortieren wir die Liste aufsteigend, setzen das Datum auf den Monatsbeginn mit “Date.StartOfMonth“ und holen das erste Datum mit [Datum]{0}“. Dieser Wert dient als Variable für die nachfolgenden Schritte.

Aufsteigend_sortieren = Table.Sort(In_Datumstyp_aendern,{{"Datum", Order.Ascending}}),
Monatsbeginn_berechnen = Table.TransformColumns(Aufsteigend_sortieren,{{"Datum", Date.StartOfMonth, type date}}),
Startdatum = Monatsbeginn_berechnen[Datum]{0},

Das Zwischenresultat sieht wie folgt aus:

Enddatum ermitteln

Als nächstes brauchen wir das Enddatum, aber wie soll das gehen, da ja nun die bisherige Datumstabelle weg ist? Ganz einfach, wir referenzieren auf einen vorherigen Schritt:
=In_Datumstyp_aendern

Und schon haben wir wieder unsere Tabelle wieder:
Liste
Nun folgen die notwendigen Codezeilen um das Monatsende des letzten Eintrages zu erhalten:
Monatsende_berechnen = Table.TransformColumns(Datumstabelle_holen,{{"Datum", Date.EndOfMonth, type date}}),
Absteigend_sortieren = Table.Sort(Monatsende_berechnen,{{"Datum", Order.Descending}}),
Enddatum = Absteigend_sortieren[Datum]{0},

Ergebnis:
Enddatum

Datumsliste erstellen

Mit List.Dates wird nun die ganze Datumsliste erstellt und in eine Tabelle umgewandelt:
Datumsliste_erstellen = List.Dates(Startdatum, Number.From(Enddatum) - Number.From(Startdatum)+1, #duration(1,0,0,0)),
Datumstabelle_erstellen = Table.FromList(Datumsliste_erstellen, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Datumspalte_umbenennen = Table.RenameColumns(Datumstabelle_erstellen,{{"Column1", "Datum"}}),

Zusätzliche Angaben

Da wir nun eine umfassende Datumstabelle haben, wollen wir diese noch mit nützlichen Angaben erweitern.

Jahr hinzufügen:
Jahr_einfuegen = Table.AddColumn(Datumspalte_umbenennen, "Jahr", each Date.Year([Datum]), type number),

Quartal hinzufügen:
Quartal_einfuegen = Table.AddColumn(Jahr_einfuegen, "Quartal", each Date.QuarterOfYear([Datum]), type number),

Monat hinzufügen:
Monat_einfuegen = Table.AddColumn(Quartal_einfuegen, "Monat", each Date.Month([Datum]), type number),

Tag hinzufügen:
Tag_einfuegen = Table.AddColumn(Monat_einfuegen, "Tag", each Date.Day([Datum]), type number),

Wochentag hinzufügen:
Wochentag_einfuegen = Table.AddColumn(Tag_einfuegen, "Wochentag", each Date.DayOfWeek([Datum], Day.Sunday), type number),

Die Funktion Date.DayOfWeek verfügt über zwei Argumente:
1. der zu wandelnde Wert, hier [Datum]
2. optional der Wochenstarttag, beginnend bei 0 = Sonntag bis 6 = Samstag

Dieser optionale Wert spielt dann eine wichtige Rolle, wenn Auswertungen z.B. über Bestelleingänge pro Wochentag ausgewertet werden müssen.
Mit dem zurückgegebenen Wert wird nun der Wochentag definiert.
Table.AddColumn(Wochentag_einfuegen, "Wochentagname", each
if [Wochentag] = 1 then "Montag" else
if [Wochentag] = 2 then "Dienstag" else
if [Wochentag] = 3 then "Mittwoch" else
if [Wochentag] = 4 then "Donnerstag" else
if [Wochentag] = 5 then "Freitag" else
if [Wochentag] = 6 then "Samstag" else
"Sonntag")

Zu guter Letzt sortieren wir noch Datumstabelle nach Datum.
Table.Sort(Wochentagname_einfuegen,{{"Datum", Order.Descending}})

…und fertig ist der Kalender

Mit einigen wenigen Schritten kann ein Kalender erzeugt werden, der sich selber auf eine andere Tabelle referenziert. Sobald die Salestabelle neu geladen wird, passt sich der Kalender automatisch an.

Nachstehend der ganze Code in M:
let
Quelle = Sales,
Datum_behalten = Table.SelectColumns(Quelle,{"Datum"}),
Duplikate_entfernen = Table.Distinct(Datum_behalten),
In_Datumstyp_aendern = Table.TransformColumnTypes(Duplikate_entfernen,{{"Datum", type date}}),
Aufsteigend_sortieren = Table.Sort(In_Datumstyp_aendern,{{"Datum", Order.Ascending}}),
Monatsbeginn_berechnen = Table.TransformColumns(Aufsteigend_sortieren,{{"Datum", Date.StartOfMonth, type date}}),
Startdatum = Monatsbeginn_berechnen[Datum]{0},
Datumstabelle_holen = In_Datumstyp_aendern,
Monatsende_berechnen = Table.TransformColumns(Datumstabelle_holen,{{"Datum", Date.EndOfMonth, type date}}),
Absteigend_sortieren = Table.Sort(Monatsende_berechnen,{{"Datum", Order.Descending}}),
Enddatum = Absteigend_sortieren[Datum]{0},
Datumsliste_erstellen = List.Dates(Startdatum, Number.From(Enddatum) - Number.From(Startdatum)+1, #duration(1,0,0,0)),
Datumstabelle_erstellen = Table.FromList(Datumsliste_erstellen, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Datumspalte_umbenennen = Table.RenameColumns(Datumstabelle_erstellen,{{"Column1", "Datum"}}),
Jahr_einfuegen = Table.AddColumn(Datumspalte_umbenennen, "Jahr", each Date.Year([Datum]), type number),
Quartal_einfuegen = Table.AddColumn(Jahr_einfuegen, "Quartal", each Date.QuarterOfYear([Datum]), type number),
Monat_einfuegen = Table.AddColumn(Quartal_einfuegen, "Monat", each Date.Month([Datum]), type number),
Tag_einfuegen = Table.AddColumn(Monat_einfuegen, "Tag", each Date.Day([Datum]), type number),
Wochentag_einfuegen = Table.AddColumn(Tag_einfuegen, "Wochentag", each Date.DayOfWeek([Datum], Day.Sunday), type number),
Wochentagname_einfuegen = Table.AddColumn(Wochentag_einfuegen, "Wochentagname", each
if [Wochentag] = 1 then "Montag" else
if [Wochentag] = 2 then "Dienstag" else
if [Wochentag] = 3 then "Mittwoch" else
if [Wochentag] = 4 then "Donnerstag" else
if [Wochentag] = 5 then "Freitag" else
if [Wochentag] = 6 then "Samstag" else
"Sonntag"),
#"Sortierte Zeilen" = Table.Sort(Wochentagname_einfuegen,{{"Datum", Order.Descending}})
in
#"Sortierte Zeilen"

Ausblick

Um Berechnungen auf der Basis von Arbeitstage durchführen zu können, benötigen wir noch die Arbeitstage und Feiertage. Mehr dazu im nächsten Blogbeitrag.
Mehr Artikel über Power Query findet Ihr im Power Query Online Buch.

Happy Quering!

3 Gedanken zu „Dynamischer Kalender in Power BI“

  1. If you are dynamically reloading the Calendar, is there a way of preserving a sort order for Month Names … so that they appear in chronological order?

    1. Hi Ted
      Power BI sorts attribute values alphabetically by default, so for example the names of the months would be sorted like this: April, August, December and so on. But you can easily fix this.
      In the Modelling Layer, click on the “Sort By Column” button and choose the appropriate column to sort by. For MonthNameShort this would be MonthNumber.
      Happy Quering

Kommentare sind geschlossen.