Meine projekterprobte Kalendertabelle für Power BI und Power Pivot

Ich kann mich in meiner Arbeit als Berater und Entwickler für Power BI-Lösungen lediglich an ein einziges Projekt erinnern, in dem die vorhandenen Daten nicht im zeitlichen Verlauf analysiert werden sollten. In allen anderen Projekten war dies fester Bestandteil der Anforderung und somit war auch die Integration einer passenden Kalendertabelle einer der ersten Schritte im Projekt. Natürlich habe ich mir über die Jahre eine entsprechende Tabelle in einer Funktion gekapselt. In diesem Beitrag stelle ich Dir meine projekterprobte Kalendertabelle nicht nur vor, sondern auch zur Verfügung.

Als Abonnent meines Newsletters erhältst Du die Beispieldateien zu den Beiträgen dazu. Hier geht’s zum Abonnement des Newsletters!

Wieso eine Kalendertabelle?

Falls Du Dir die Frage stellst, wozu Du eine solche Tabelle überhaupt benötigst und was die Folgen sein könnten, wenn Du sie nicht (so) in Dein Datenmodell integrierst, lies bitte meine zweiteilige Reihe zu Kalendertabellen in Power BI und Power Pivot. Danach solltest Du ein Verständnis dafür haben, warum Du sie benötigst.

Meine Kalendertabellenfunktion

Um nicht in jedem Projekt wieder mit der Erstellung einer neuen Kalendertabelle starten zu müssen, habe ich mir die am häufigsten auftretenden Business-Anforderungen an eine Kalendertabelle in einer entsprechenden M-Funktion gekapselt. Ich behaupte nicht, dass diese Funktion für jedes Projekt ausreichend ist. Aber sie bietet auf jeden Fall einen sehr guten Startpunkt und kann durch Dich natürlich entsprechend erweitert werden, wenn der Bedarf bestehen sollte. In den allermeisten meiner Projekte war die Funktion jedoch vollkommen ausreichend.

Falls Du wissen möchtest, wie Du eine Funktion in Power BI wiederverwendest, findest Du hier eine kurze Anleitung dazu. Als nächstes möchte ich ein paar Worte zu den Parametern dieser Funktion verlieren.

Parameter

Um die Kalendertabelle erstellen zu können, benötigt meine Funktion ein paar wenige Parameter. Diese unterscheiden sich in notwendige und optionale Parameter.

Notwendige Parameter

In meiner oben erwähnten Reihe zu Kalendertabellen in Power BI habe ich detailliert die Ansprüche an eine solche Tabelle herausgearbeitet. Kurz zusammengefasst benötigt eine Kalendertabelle zwingend eine Spalte vom Typ Datum, die lückenlos – möglichst für komplette Jahre – genau einen Eintrag pro Kalendertag aufweist. Dies berücksichtigt meine Funktion und benötigt daher das StartJahr. Da sich Kalendertabellen bei fortschreitenden Jahren in der Regel an diese anpassen sollen, fragt der zweite Parameter JahreInDieZukunft danach, wie viele Jahre die Kalendertabelle – ausgehend vom Kalenderjahr des heutigen Tages – in die Zukunft gehen soll. Falls Dein Datenmodell also Budgetwerte für die kommenden 3 Jahre beinhaltet, sollte dieser Parameter 3 lauten. Beide Parameter werden als Ganzzahl übergeben. Hieraus wird eine Tabelle erzeugt, die vom 1. Januar des Startjahres bis zum 31. Dezember des Endjahres (=aktuelles Jahr + JahreInDieZukunft) geht.

Neben diesen beiden notwendigen Parametern, kann meine Funktion 2 weitere Parameter verarbeiten, die für die Funktionsweise jedoch nicht essentiell sind, weil sie nicht in jedem Projekt benötigt werden.

Optionale Parameter

Die optionalen Parameter sind für Sonderfälle gedacht, die nicht in jedem Projekt eine Rolle spielen. Hierbei handelt es sich um die Möglichkeit die sog. Culture von Deutsch auf eine andere Sprache zu ändern und ein eventuell vom Kalenderjahr abweichendes Geschäftsjahr zu berücksichtigen.

Culture (optional)

In der Programmiersprache M gibt es diverse Funktionen, die einen Parameter Culture akzeptieren. Für meine Kalendertabelle ist das für Textspalten sinnvoll. So kann ich über die Auswahl der Culture „en-US“ definieren, dass Tagesnamen und Monatsnamen auf Englisch abgebildet werden und nicht auf Deutsch. Meine Funktion bietet die Möglichkeit zwischen Deutsch („de-de“), Englisch („en-US“), Französisch („fr-FR“) und Spanisch („es-ES“) zu wählen. Die Integration weiterer Sprachen ist auf Basis der hier abgebildeten Auflistung von Cultures jedoch problemlos möglich. Wichtig ist es an dieser Stelle zu bemerken, dass sich die Culture lediglich auf den Inhalt der Spalten, nicht aber auf die Spaltenüberschriften auswirkt. Eine Mehrsprachigkeit meines Datenmodells kann ich hierüber nicht erzielen.

StartFiskaljahr (optional)

Nicht jedes Geschäftsjahr beginnt mit dem 01. Januar. Bei Microsoft ist der Geschäftsjahresanfang beispielsweise der 01. Juli eines Jahres. Dies führt dazu, dass der Januar des Jahres 2021 , der 7. Monat im Geschäftsjahr 2020 von Microsoft ist, obwohl es sich ja um das Kalenderjahr 2021 handelt. Sofern ich diese Logik in einem Kundenprojekt benötigt, kann ich den Startmonat des Geschäftsjahres mit angeben und bekomme ausschließlich dann weitere Spalten in meiner Kalendertabelle hinzugefügt, die diese Logik integrieren.

Solche Logiken können beispielsweise nützlich für die Sortierung von Monatsnamen in Slicern oder anderen Visualisierungen sein (man würde im genannten Fall vermutlich wollen, dass der erste Monat im Slicer der Juli ist und der letzte der Juni). Aber natürlich auch für die Nutzung in Time-Intelligence-Funktionen in DAX kann eine solche Tabelle sehr nützlich sein.

Erläuterungen zu den verschiedenen Spalten

Alle Spalten innerhalb der Tabelle habe ich – zumindest für meinen Begriff – sprechend benannt. Erklärungsbedürftig könnten dennoch die Spalten mit den Suffixen “ #“ und speziell „Key #“ sein.

“ #“-Spalten

Alle Spalten, die eine Raute „#“ aufweisen, sind nummerische Spalten. Der Unterschied zwischen den Spalten „Quartal“ und „Quartal #“ ist, dass das Quartal mit „Q1“ bis „Q4“ vom Typ Text ist und, die Spalte „Quartal #“ mit 1 bis 4 vom Typ Zahl ist. Nummerische Spalten können zum Rechnen in DAX-Ausdrücken benutzt werden, aber auch, um andere Textspalten danach zu sortieren. Hierfür stelle ich weiter unten ein C#-Skript zur Verfügung, das Du mit dem externen Tool Tabular Editor nutzen kannst, um Dir das Leben zu erleichtern.

„Key #“-Spalten

Time-Intelligence-Funktionalitäten sind eine geniale Sache in DAX. Einfach TOTALYTD() zu nutzen, um zur Laufzeit einen Year-to-date-Wert zu kalkulieren, ist großartig. Manchmal muss ich jedoch eigene TI-Funktionen in DAX schreiben, weil die Standardfunktionen das so nicht anbieten. Dies trifft beispielsweise zu, wenn ich mit Iso-Kalenderwochen arbeite. Um hier auch jahresübergreifend auf Vorwochen zugreifen zu können, gibt es die Spalte „IsoKWKey“, mit der z. B. folgendes Measure möglich wäre:

Umsatz Vorwoche =
CALCULATE (
    SUM ( Fact[Umsatz] ),
    FILTER (
        ALL ( Kalender ),
        Kalender[IsoKWKey]
            = MAX ( Kalender[IsoKWKey] ) – 1
    )
)

Diese „Key #“-Spalten sollten dem Berichtsersteller eher vorenthalten werden, weil es rein technische Spalten für die Erstellung von Measures oder das Sortieren von Textspalten sind.

„Relative #“-Spalten

Häufig wird in Projekte gefordert, einen Filtern in einen Bericht einzubauen, der dafür sorgt, dass mir alle Daten immer für den aktuellen Tag, die aktuelle Woche, den aktuellen Monat, das aktuelle Jahr usw. angezeigt werden, sobald die Daten aktualisiert sind. Marcus Wegener gab mir den Tipp, hier doch relative Spalten einzubauen, die mich auch den gestrigen Tag ([Relativer Tag #] = -1) oder das vorletzte Quartal ([Relatives Quartal #] = -2) auswählen lassen. Der folgende Screenshot zeigt, dass der 12.09.2022 der heutige Tag ist (an diesem aktualisiere ich diesen Artikel) und davon ausgehend die Vergangenheit mit negativen Zahlen und die Zukunft mit positiven Zahlen gekennzeichnet ist.

Ein C#-Skript für Tabular Editor zur automatischen Integration der Kalendertabelle ins Datenmodell

Um das Sortieren von Textspalten, das Ausblenden von Key-Spalten und das Nicht-Aggregieren von nummerischen Spalten nicht von Hand einstellen zu müssen, habe ich ein Skript geschrieben, dass dies in Tabular Editor für Dich automatisiert umsetzt.

Wie Du das Skript nutzen kannst, zeige ich Dir in meine Erklärvideo.

Mein Erklärvideo

Ich hoffe meine Tabelle ist Dir eine Hilfe. Wenn Du weitere Ideen hast, die in diese Tabelle integriert werden sollten, schick mir gerne eine Mail und ich sehe, was ich tun kann 😉

Bis zum nächsten Mal und denk dran: Sharing is caring. Wenn Dir der Beitrag gefallen hat, dann teile ihn gerne. Falls Du Anmerkungen hast, schreibe gerne einen Kommentar, oder schicke mir eine Mail an lars@ssbi-blog.de

Viele Grüße aus Hamburg,

Lars