Mit dem folgenden Code könnt Ihr eine Kalendertabelle aus eueren Daten in Excel oder Power BI erzeugen. Ich habe hier in diesem Fall eine Datei mit einer Tabelle namens factSales. Diese hat eine Spalte namens Datum.

Der Clou an dieser Formel ist, dass nicht nur der Kalender dynamisch erzeugt wird, sondern auch Kalenderwoche, Quartal und Monat zusammen für die Auswertung erzeugt werden.

Ich verlasse mich nicht auf die Kalendertabelle, die man automatisch erzeugen kann, da hier ich einfach mehr Kontrolle über die Daten habe.

In einem späteren Video werde ich den Code erläutern und dann noch per API-Abfrage um die Feiertage eines Bundeslandes erzeugen.

Diesen Code nun einfach Kopieren und in den Power Query-Editor einer leeren Abfrage einfügen.

Code startet hier:

let

Quelle = Excel.CurrentWorkbook(){[Name="factSales"]}[Content],

DatumZuDate = Table.TransformColumnTypes(Quelle,{"Datum", type date}),

// greift auf Tabelle factSales zu. Dort ist nur die Spalte Datum interessant und wird als Datum formatiert

BerechneterJahresbeginn = Table.TransformColumns(DatumZuDate,{{"Datum", Date.StartOfYear, type date}}),

JahresbeginnZuZahl = Table.TransformColumnTypes(BerechneterJahresbeginn,{{"Datum", Int64.Type}}),

DatumStart = List.Min(JahresbeginnZuZahl[Datum]),

/* Jedes Datum wird auf Jahresanfang gesetzt. Somit erhält man das kleineste Datum des frühesten Jahres
Es resultiert dann ein Wert, der als Listenwert abgerufen werden kann*/

BerechnetesJahresende = Table.TransformColumns(DatumZuDate,{{"Datum", Date.EndOfYear, type date}}),

JahresendeZuZahl = Table.TransformColumnTypes(BerechnetesJahresende,{{"Datum", Int64.Type}}),

DatumEnde = List.Max(JahresendeZuZahl[Datum]),

/* Jedes Datum wird auf Jahresende gesetzt. Somit erhält man das größte Datum des letzten Jahres
Es resultiert dann ebenfallsein Wert, der als Listenwert abgerufen werden kann*/

Liste = {DatumStart .. DatumEnde},

ListeInTabelleKonvertiert = Table.FromList(Liste, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

// Aus DatumStart und Datumende wird eine Zahlenliste erzeugt, die dann in eine Tabelle gewandelt wird

ZahlenZuDatum = Table.TransformColumnTypes(ListeInTabelleKonvertiert,{{"Column1", type date}}),

DatumSpalte = Table.RenameColumns(ZahlenZuDatum,{{"Column1", "Datum"}}),
// Die Zahlen werden in ein Datum verwandelt, somit erhält man komplette Tage aller notwendigen Jahre

JahrEingefügt = Table.AddColumn(DatumSpalte, "Jahr", each Date.Year([Datum]), Int64.Type),

MonatEingefügt = Table.AddColumn(JahrEingefügt, "Monat", each Date.Month([Datum]), Int64.Type),

QuartalEingefügt = Table.AddColumn(MonatEingefügt, "Quartal", each Date.QuarterOfYear([Datum]), Int64.Type),

// Jahr, Monat und Quartal werden als Zahlen eingefügt

JahrQuartal = Table.AddColumn(QuartalEingefügt, "Jahr Quartal", each Text.Combine({Text.From([Jahr], "de-DE"),
Text.From([Quartal], "de-DE")}, "-"), type text),

// Jahr und Quartal werden für spätere Auswertungen vereint

MonatZweistellig = Table.AddColumn(JahrQuartal, "Jahr Monat",
each if [Monat] < 10 then Text.Combine( {Text.From([Jahr], "de-DE"), "-0", Text.From([Monat], "de-DE")} )
else Text.Combine( {Text.From([Jahr], "de-DE"), "-", Text.From([Monat], "de-DE")} ) ),

/*Jahr, Bindestrich und zweistellige Monatsnummer werden als neue Spalte ergänzt.
Die WENN-Funktion prüft, ob das Jahr einstellig ist */

Kalenderwoche = Table.AddColumn(MonatZweistellig, "KW", each if
Number.RoundDown((Date.DayOfYear([Datum])-(Date.DayOfWeek([Datum], Day.Monday))+9)/7)=0
then
Number.RoundDown((Date.DayOfYear(#date(Date.Year([Datum])-1,12,31))-(Date.DayOfWeek(#date(Date.Year([Datum])-1,12,31), Day.Monday))+9)/7)
else if
(Number.RoundDown((Date.DayOfYear([Datum])-(Date.DayOfWeek([Datum], Day.Monday))+9)/7)=53 and (Date.DayOfWeek(#date(Date.Year([Datum]),12,31), Day.Monday)<3))
then 1
else Number.RoundDown((Date.DayOfYear([Datum])-(Date.DayOfWeek([Datum], Day.Monday))+9)/7)),

// Die KW nach ISO 8601 wird ermittelt

JahrKWCalc = Table.AddColumn(Kalenderwoche , "JahrKW", each
if [Monat]=1 and [KW]>51
then [Jahr]-1
else if [Monat]=12 and [KW] = 1 then
[Jahr]+1
else [Jahr]),

// Das Jahr der entsprechenden KW wird ermittelt. Dies weicht eventuell vom Kalenderjahr ab.

ZahlenZuText = Table.TransformColumnTypes(JahrKWCalc ,{{"JahrKW", type text}, {"KW", type text}}),

JahrKWZusammen = Table.AddColumn(ZahlenZuText , "Jahr KW", each [JahrKW]&"-"& Text.End("0"&[KW], 2), type text),

// Jahr und KW werden vereint. Hier mal nicht die TextCombine-Formel von oben

SpaltenRaus = Table.RemoveColumns(JahrKWZusammen ,{"KW", "JahrKW"}),

// Überflüssige Spalten raus und noch

Wochentag = Table.AddColumn(SpaltenRaus, "Tag", each Date.DayOfWeekName([Datum]), type text),

WochentagZahl = Table.AddColumn(Wochentag, "Tag der Woche", each Date.DayOfWeek([Datum], 1) + 1, Int64.Type),

Monatsname = Table.AddColumn(WochentagZahl, "Monatsname", each Date.MonthName([Datum]), type text)

// Noch ein paar Informationen ergänzt

in

Monatsname

Code endet hier!

Hier ist die Datei zum Download mit ein paar Demodaten:

E1031 Verkäufe Kalendertabelle

Freue mich auf Feedback und Danke für die Unterstützung des Kanals!
Sonnige Grüße

Andreas