Aufrufe: 139529

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.

Willst du Power BI lernen?
Auf unseren neuen Seite Power BI Coach haben wir einen Onlinekurs zusammengestellt.

Ausgangspunkt

Ausgangspunkt ist eine stark vereinfachte Faktentabelle namens „Sales“.

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:

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:

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!