In einem wochenbasierten Reporting in Excel und Power BI ist das anzuwendende Kalenderwochensystem eine zentrale Vorfrage. Die Week-over-Week Ermittlung einer KPI in einer Scorecard hängt ebenso wie der Mehrjahresvergleich auf Wochenbasis vom verwendeten Kalenderwochensystem ab.
Übersicht über die Kalenderwochensysteme
Die in diesem Artikel vorgestellten 3 Systeme zur Ermittlung der Kalenderwochen können hier am Beispiel der Jahre 2018 und 2019 direkt miteinander verglichen werden ...
... und hier anhand des besonderen Jahres 2012, in dem es im sogenannten "System 1" auch eine KW 54 gibt:
Beachten Sie bitte, daß durch die verschiedenen Systeme zur KW auch die Nummern der nachfolgenden Kalenderwochen des Jahres häufig versetzt sind.
System 1 ("Standard"): die KW 1 beginnt am 1. Januar
Definition:
- Laut Excel Online-Hilfe zur Formel Kalenderwoche: "The week containing January 1 is the first week of the year, and is numbered week 1."
Ermittlung in Excel:
- Die Ermittlung erfolgt mit der KALENDERWOCHE-Formel, die auf ein Datum bezogen wird, mittels Parameter (1, 2, 11 bis 17) wird der Beginn der Wochen ab KW 2 definiert:
Eigenschaften:
- Die KW 1 beginnt immer am 1. Januar eines Jahres und besteht aus einer variablen Anzahl von Tagen (zwischen 1 und 7)
- Der Beginn der Kalenderwochen kann flexibel an allen 7 Wochentagen von Montag bis Sonntag definiert werden
- Die KW 53 besteht aus einer variablen Anzahl von Tagen (zwischen 1 und 7), fallweise gibt es auch eine KW54 (zuletzt im Jahr 2012)
- Jahresprofil (2000 bis 2030):
Evaluierung:
- Pro: Die Ermittlung ist sehr einfach, da es dafür in fast allen Systemen (wie auch in Excel) fertige Funktionen gibt.
- Contra: Der Vergleich einer KW, die nur wenige oder gar nur 1 Tag umfasst, ist in einem Wochenreporting in den allermeisten Fällen unbrauchbar. Die seltene KW 54 ist besonders störend bei Week-over-Week Berechnungen und Mehrjahresvergleichen.
System 2 ("ISO-Wochen"): die KW 1 enthält den ersten Donnerstag im Jahr
Definition:
- Laut Excel Online-Hilfe zur Formel Kalenderwoche: "The week containing the first Thursday of the year is the first week of the year, and is numbered as week 1. This system is the methodology specified in ISO 8601, which is commonly known as the European week numbering system."
Ermittlung in Excel:
- Die Ermittlung kann mit der KALENDERWOCHE-Formel mit dem Parameter 21 oder alternativ mit der Formel ISOKALENDERWOCHE (beide mit Bezug auf ein Datum) erfolgen:
Eigenschaften:
- Eine KW beginnt immer am Montag und umfasst immer 7 Tage
- Die ersten Tage des Jahres gehören damit häufig zur letzten Woche des Vorjahres und die letzten Tage des Jahres häufig zur ersten Woche des Folgejahres
- Das Jahr hat nicht 365/366 Tage sondern entweder 364 oder 371 Tage, es handelt sich also um ein kalenderjahr-übergreifendes Wochensystem
- Jahresprofil (2000 bis 2030):
Evaluierung:
- Pro: Perfekte Vergleichbarkeit der Wochen, da diese immer aus gleich vielen Wochen- und gleich vielen Werktagen bestehen (die Öffnungstage-/Feiertagsthematik wird damit aber noch nicht gelöst)
- Contra: Durchbrechung des Kalenderjahrsystems erschwert den Abgleich der Wochenergebnisse mit dem Jahresergebnis
System 3 ("benutzerdefiniert"): Woche muß mindestens x-Tage umfassen
Als benutzerdefiniertes Kalenderwochensystem kann grundsätzlich jedes beliebige (firmenspezifische) System definiert werden. Systeme nach dem Muster "KW 1 hat mind. x Kalender- oder Werktage" sind dabei sehr gängig, wir zeigen hier eine Variante bei der sowohl die erste als auch die letzte KW mind. 3 Kalendertage haben muß.
Definition:
- Eine Kalenderwoche beginnt immer am Montag und besteht aus mindestens 3 Kalendertagen
- Kürzere Wochen am Jahresanfang werden mit der darauffolgenden ersten ganzen Woche zur KW 1, kürzere Wochen am Jahresende werden mit der vorhergehenden letzten ganzen Woche zur KW 52/53
Ermittlung in Excel:
- Eine Formel gibt es unseres Wissens nach nicht, die Ermittlung muß daher mit individuellen Wenn-Dann-Bedingungen erfolgen
Eigenschaften:
- Die KW 1 beginnt am 1. Januar eines Jahres und kann bis zu 9 Kalendertage enthalten
- Die KW 52 besteht aus 6 bis 9 Kalendertagen, die KW 53 aus 3 bis 6 Tagen; eine KW 54 gibt es in diesem System nicht
- Wie beim System 1 kann als Wochenbeginn jeder Wochentag definiert werden und das Wochensystem arbeitet ebenfalls immer innerhalb des Kalenderjahrs
- Jahresprofil (2000 bis 2030):
Evaluierung:
- Pro: das Kalenderjahr wird nicht durchbrochen und der Wochenbeginn kann flexibel definiert werden. Die Vergleichbarkeit der KW ist gegenüber dem System 1 weniger stark verzerrt, da es keine Rumpfwochen mit 1 oder 2 Tagen gibt.
- Contra: die Vergleichbarkeit der ersten und letzten Kalenderwoche ist bei einer variablen Anzahl von Tagen (zwischen 3 und 9) gegenüber dem System 2 natürlich trotzdem verzerrt. Die Ermittlung ist auch vergleichsweise aufwendig, da es keine fertigen Formeln gibt.
Vergleich: Kalenderwochen-Systeme in Microsoft Outlook
In Microsoft Outlook werden ebenfalls 3 Systeme angeboten:
- Beginnt am 1. Januar: entspricht dem "System 1" (in Kombination mit der Option für den "Ersten Wochentag")
- Erste 4-Tage-Woche: entspricht sinngemäß dem "System 3", wobei sich die Einstellung hier nur auf die KW 1 und nicht auch auf die letzte Woche im Jahr bezieht (und auf 4 Tage fixiert ist)
- Erste Volle Woche: entspricht dem "System 2" (allerdings hier mit der Möglichkeit einer Variation des "Ersten Wochentags")
Anzumerken ist hier, daß die Ermittlung der Kalenderwoche in MS Outlook nur eine sehr untergeordnete Relevanz hat und daher beispielsweise in der Option "Beginnt am 1. Januar" nicht differenziert wird, daß der 31.12.2018 nicht zur KW 1 des Jahres 2019 (wie angezeigt) gehört sondern zur KW 53 des Jahres 2018 (dafür ist aber kein Platz in der Anzeige). Daher kann die Ermittlung der Kalenderwochen in MS Outlook nur eine Orientierungshilfe für das Kalenderwochensystem in einem BI-/Reportingsystem sein.
Ausblick: Kalenderwochen und Fiscal Calendar im "DAX Date Template"
Das DAX Date Template von sqlbi.com ist ein sehr mächtiges Template in Power BI für den parameter-gesteuerten Aufbau einer Datumsdimension in den 3 großen Abschnitten
- Solar Calendar ("Gregorian")
- Fiscal Monthly Calendar ("Fiscal Gregorian")
- Fiscal Weekly Calendar ("4-4-5")
Hinsichtlich der Definition der Kalenderwochen nach oben genannten Ausführungen ist folgendes festzustellen:
- Die Basisdefinition des Beginns einer Kalenderwoche erfolgt mit dem Parameter FirstDayOfWeek (0 bis 6 für Sonntag bis Samstag)
- Das Feld Calendar Week im Abschnitt "Solar Calendar" sowie alle davon abgeleiteten Week-Felder dieses Abschnitts werden in Abhängigkeit vom Parameter FirstDayOfWeek (0 bis 6) ausschließlich nach dem System 1 ermittelt. Die KW1 beginnt dabei am 01.01. des Jahres.
- Das Feld Fiscal Week im Abschnitt "Fiscal Monthly Calendar" sowie alle davon abgeleiteten Week-Felder dieses Abschnitts werden in Abhängigkeit von den Parametern FirstDayOfWeek (0 bis 6) und FiscalCalendarFirstMonth (1 bis 12) ausschließlich nach dem System 1 ermittelt. Die KW1 beginnt am 1. Tag des Wirtschaftsjahres, bspw. am 01.07. des Jahres.
- Das Feld FW Week im Abschnitt "Fiscal Weekly Calendar" sowie alle davon abgeleiteten Week-Felder dieses Abschnitts werden in Abhängigkeit von den Parametern FirstDayOfWeek (0 bis 6), WeeklyCalendarType ("Weekly" oder "Custom") und WeeklyType ("Last" oder "Nearest") ermittelt. Die KW1 beginnt bspw. am ersten Montag der KW in der auch der Beginn des Wirtschaftsjahres fällt. Die KW1 enthält daher bis zu 6 Tage, die vor dem Beginn des Wirtschaftsjahres liegen. Das Jahr hat im Normalfall 364 Tage (= 52 Wochen) und fallweise gibt es eine zusätzliche KW 53 als Ausgleich.
Weiterführende Quellen:
Kalenderwochensysteme:
- support.office.com/en-us/article/weeknum-function-e5c43a03-b4ab-426c-b411-b18c13c75340
- www.timeanddate.de/datum/kalenderwoche
Fiscal Calender ("445"):