Ereignisse in Unternehmen, seien es Verkäufe, Bestellungen oder beispielsweise Rechnungseingänge, finden zu einem bestimmten Zeitpunkt statt. Da Datenmodelle in Power BI und Power Pivot zu Analysezwecken erstellt werden, ist es nützlich den Zeitbezug auch in den Datenmodellen abzubilden. Aus diesem Grund ist eine Kalendertabelle als Dimension, fast immer ein fixer Bestandteil innerhalb eines solchen Datenmodells. Doch diese spezielle Dimensionstabelle stellt besondere Anforderungen. Daher beschäftigt sich dieser Artikel damit, technische Anforderungen an eine Kalendertabelle in Power BI und Power Pivot zu beleuchten.

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

ACHTUNG: Ich benutze in diesem Beitrag durchgehend den Begriff der Kalendertabelle. In der Literatur und auch in Power BI selbst, werden auch andere Begrifflichkeiten wie Datumstabelle, Datums-Dimension, etc. verwendet. Lass Dich durch die unterschiedlichen Begriffe nicht verwirren: Sie sind alle Synonyme für dieselbe Tabelle im Datenmodell!

Was ist eine Kalendertabelle

Die Kalendertabelle ist eine spezielle Dimensionstabelle innerhalb eines Power BI Datenmodells. Jede Zeile innerhalb dieser Tabelle repräsentiert einen einzelnen Tag und jede Spalte innerhalb dieser Tabelle bezieht sich auf diesen repräsentierten Tag der jeweiligen Zeile. Ein Beispiel für eine solche Kalendertabelle siehst Du im folgenden Screenshot:

Aus dieser Darstellung wird deutlich, dass jede Zeile einen einzelnen Tag repräsentiert, der als Datum in Spalte Datum vermerkt ist. Die Bezeichnung der Spalte ist hierbei irrelevant für die Funktionalität. Alle anderen Spalten beziehen sich auf die Spalte Datum und ordnen diesen entsprechenden Tag beispielsweise einem Monat, einem Kalenderjahr, oder auch einem Wochentag zu. Doch wozu benötige ich überhaupt eine Kalendertabelle in meinem Datenmodell?

Wozu benötigt ein Power BI Datenmodell eine Kalendertabelle?

In den meisten Fällen möchte ich zeitbezogene Analysen meiner Daten vornehmen. Dies erleichtern mir in DAX die sog. Time Intelligence-Funktionen. Zu diesen Funktionen gehört beispielsweise die Funktion TOTALYTD(), die ich in diesem Artikel behandelt habe.

Eine Kalendertabelle benötige ich im Wesentlichen aus zwei Gründen:

1. Alle Time Intelligence-Funktionen in DAX müssen auf Basis einer Kalendertabelle kalkuliert werden, um fehlerfrei funktioniere zu können. Zeitbezogene Kalkulationen auf Basis von Datumsspalten in Faktentabellen (die Tabelle, in der die Transaktionen/ Ereignisse stehen) sollten hier unbedingt vermieden werden!
2. Das gleichzeitige Filtern mehrerer Faktentabellen hinsichtlich einer zeitlichen Dimension ist nur dann einfach möglich (d. h. ohne das Schreiben komplexer DAX-Formeln), wenn diese Faktentabellen über Beziehungen mit einer entsprechenden Kalendertabelle verknüpft sind.

Doch welche Anforderungen muss eine Kalendertabelle nun erfüllen? Ich unterscheide die Anforderungen an eine Kalendertabelle in zwei Kategorien: in technische und inhaltliche Anforderungen. Diesen Beitrag widme ich den technischen Anforderungen.

Technische Anforderungen an eine Kalendertabelle in Power BI

Die technischen Anforderungen an eine Kalendertabelle lassen sich im Wesentlichen von den Anforderungen der Time Intelligence-Funktionen in DAX ableiten. Damit diese DAX-Funktionen korrekte Ergebnisse liefern können, muss die Kalendertabelle gewisse Anforderungen erfüllen. Auf diese gehe ich jetzt ein.

Die datetime-Spalte

Alle Time Intelligence-Funktionen in DAX enthalten einen <dates>-Parameter. Hier einmal beispielhaft die TOTALYTD()-Funktion:

= TOTALYTD(<expression>,<dates>[,<filter>][,<year_end_date>])

Über diesen <dates>-Parameter wird die Datumsspalte aus der Kalendertabelle an die Funktion übergeben. Diese übergebene Spalte muss vom Typ datetime sein. Nur so kalkulieren Time Intelligence-Funktionen richtig. Der Typ datetime suggeriert, dass es sich hierbei um eine Spalte handeln kann, die nicht nur Datumsangaben, sondern auch Uhrzeitangaben beinhalten darf. Dass dies nicht so ist, verdeutlicht der nächste Abschnitt.

Ein Datensatz pro Tag

Sofern ich Kalender- und Faktentabelle über die Datumsspalte miteinander verknüpfen möchte, ist es für die Erstellung der Beziehung unerheblich, ob in der Datumsspalte nur eindeutige Datumswerte stehen, oder ob es sich um eindeutige Datums- und Uhrzeitangaben handelt, bei denen pro Tag auch mehrere Datensätze existieren. Hauptsache die Einträge sind eindeutig. Ob mit, oder ohne Uhrzeit spielt für die Datenmodellierung keine Rolle. Eine Beziehung kann problemlos auf Basis einer solchen Spalte erstellt werden.

Auch das Erstellen eines DAX-Measures mit einer Time Intelligence-Funktion ist kein Problem. Das folgende Measure kann problemlos geschrieben und gespeichert werden:

= TOTALYTD ( SUM ( Tabelle[Spalte] ); Kalendertabelle[Datum] )

Problematisch wird es jedoch, wenn ich dieses Measure für Analysen in Pivottabellen oder Visualisierungen nutzen möchte. Die entsprechenden Fehlermeldungen aus Excel und Power BI Desktop sehen wie folgt aus:

Ein einzelner Tag darf in der Kalendertabelle also nur ein einziges Mal enthalten sein, da die Time Intelligence-Funktionen sonst ihre Arbeit verweigern 😉 Sollte es für Dein Datenmodell notwendig sein, auch die Uhrzeiten zu berücksichtigen, so solltest Du Dir speziell für die Uhrzeiten eine separate Dimensionstabelle erzeugen. Dies ist jedoch nicht Gegenstand dieses Beitrags.

Datensätze müssen lückenlos aufgeführt sein

Bis hier steht also fest, dass die Kalendertabelle eine Datumsspalte haben muss, in der pro Tag genau ein Datensatz enthalten ist. Eine weitere Notwendigkeit für korrekt rechnende Time Intelligence-Funktionen ist, dass es zwischen den einzelnen Tagen in der Kalendertabelle keine Lücken geben darf.

Tage, die inmitten der Kalendertabelle fehlen, können nicht als Ergebnis einer Time Intelligence-Funktion zurückgegeben werden. Dies ist unabhängig davon, ob für die entsprechenden Tage Werte in den Faktentabellen vorliegen. Ein Beispiel macht dies plastischer. Für mein Beispiel liegen folgende Tabellen vor:

Die Kalendertabelle ist lückenlos. Die Faktentabelle weist der Einfachheit halber für jeden Tag im Januar 2018 den Wert 1 aus. Das Datenmodell ist das folgende:

Basierend auf diesem Datenmodell erstelle ich folgendes Measure, das den Year-to-date-Wert der Spalte Faktentabelle[Wert] kalkuliert:

= TOTALYTD ( SUM ( Faktentabelle[Wert] ); Kalender[Datum] )

Die folgende Pivottabelle verwendet die Spalte Datum der Kalendertabelle als Zeilenbeschriftung und zeigt das korrekt kalkulierte Ergebnis je Tag:

Wie wirkt es sich auf das Ergebnis in der Pivottabelle aus, wenn ich (vollkommen willkürlich) den 14.01.2018 aus der Kalendertabelle entferne?! ACHTUNG: Auch hier wird die Spalte Datum der Kalendertabelle als Zeilenbeschriftung der Pivot genutzt!

Dass der 14.01.2018 in der Pivottabelle fehlen würde, entsprach meiner Erwartung. Dennoch wäre es nicht abwegig gewesen, dass der YTD-Wert am 15.01.2018 trotzdem den Wert 15 ausweist. Schließlich fehlt der 14.01.2018 ja nur in der Kalendertabelle. In der Faktentabelle ist diesem Tag jedoch ein Wert (1€) zugeordnet. Dennoch fällt dieser Wert komplett aus der Kalkulation heraus, so dass zum 31.01.2018 nur 30€ zusammenkommen und nicht, wie es korrekt wäre, 31€. Time Intelligence-Funktionen scannen vor der Kalkulation die in der Kalendertabelle vorhandenen Tage und berechnen auch nur für diese die gewünschten Werte. Fehlt ein Tag in der Kalendertabelle, dann wird der entsprechende Wert aus der Faktentabelle in der Kalkulation nicht berücksichtigt!

Beziehung zwischen Kalender- und Faktentabelle

Ich hatte bereits unter Ein Datensatz pro Tag das Erstellen von Beziehungen mit der Kalendertabelle thematisiert. Damit die Faktentabelle durch die Kalendertabelle gefiltert werden kann, muss zuvor eine Beziehung zwischen diesen Tabelle hergestellt werden. Diese Beziehung kann auf Basis der Datumsspalte erzeugt werden, muss es aber nicht. Notwendig für eine funktionierende Verknüpfung ist, dass die Werte in der entsprechenden Schlüsselspalte in der Kalendertabelle eindeutig sind. Ob der Datentyp der Schlüsselspalte datetime, integer, oder text ist, spielt keine Rolle. Denkbar wäre beispielsweise folgender Aufbau des Schlüsselkriteriums: 20180215 für den 15.02.2018. Es ist an dieser Stelle wichtig zu erwähnen, dass diese Spalte lediglich als Schlüsselspalte zum Verknüpfen der Tabellen fungieren darf, nicht jedoch als Parameter in den Time Intelligence-Funktionen. In Time Intelligence-Funktionen muss eine Spalte vom Typ datetime genutzt werden, damit die Kalkulationen korrekt funktionieren.

Kalender- bzw. Geschäftsjahre müssen vollständig in der Kalendertabelle enthalten sein

An dieser Stelle herrscht nun Klarheit darüber, warum die Kalendertabelle keine Lücken in den Tagen haben darf und es ist auch klar, welche Optionen für Beziehungen zwischen den Tabellen bestehen. Aber müssen Geschäftsjahr bzw. Kalenderjahre auch komplett sein? Ich habe schon einige Entwickler von Datenmodellen erlebt, die die Kalendertabelle nur bis zum letzten vorhandenen Tag in der Faktentabelle fortgeschrieben hatten. Schließlich betrachtet man sowieso meist die Vergangenheit und nicht die Zukunft. Je kleiner die Kalendertabelle, desto weniger wird das Datenmodell damit belastet. Das war zumindest die gutgemeinte Idee dahinter. Lass mich dir zeigen, was passieren kann, wenn Du eine unvollständige Kalendertabelle in Time Intelligence-Funktionen nutzt. Ich habe das vorherige Beispiel etwas angepasst:

In der Faktentabelle sind die Werte vom 01.01.2016 bis zum 15.02.2018 enthalten. Da liegt es doch nahe, die Kalendertabelle auch nur bis zum 15.02.2018 zu erstellen und dabei darauf zu achten, dass sie lückenlos ist. Das sollte man jedoch besser vermeiden. Ich nutze die folgenden zwei Measures, um die Problematik aufzuzeigen:

YTD = TOTALYTD ( SUM ( Faktentabelle[Wert] ); Kalender[Datum] )

und

YTD_PY = CALCULATE ( [YTD]; DATEADD ( Kalender[Datum]; -1; YEAR ) )

Das Measure YTD kalkuliert den kumulierten Jahreswert der aktuellen Periode, während YTD_PY den kumulierten Jahreswert derselben Periode des Vorjahres kalkuliert. Ich ziehe beide Measures in die bereits bekannte Pivottabelle. Hierbei fokussiere ich aus didaktischen Gründen auf den Monat Februar der drei Jahre:

Beim ersten Jahr (1) handelt es sich um ein Schaltjahr. Der Februar hat also 29 Tage, wodurch sich der kumulierte Jahreswert (Jan+Feb 2016) mit 60 € ergibt. Im darauffolgenden Jahr 2017 (2) hat der Februar 28 Tage. Damit ergibt sich der kumulierte Jahreswert zum 28.02.2017 mit 59 €. Was an dieser Stelle interessant ist, ist die Tatsache, dass der YTD_PY-Wert am 28.02.2017 59€ beträgt, auf Monatsebene (oben im Zwischenergebnis) jedoch 60€ ausweist. Gleich vorweg: Beide Werte sind richtig. In 2016 gab es den 29.02., in 2017 jedoch nicht. Deswegen kann dieser auf Tagesebene im Jahr 2017 auch nicht ausgewiesen werden. Auf Monatsebene gibt die Funktion DATEADD ( Kalender[Datum]; -1; YEAR ) jedoch den gesamten Monat des Vorjahres zurück, so dass immer ganze Monate miteinander verglichen werden, unabhängig davon, wie viele Tage sie haben. Daher ist der YTD_PY-Wert am 28.02.2017 verschieden vom YTD_PY-Wert des gesamten Kalendermonats. Dieses Verhalten muss man kennen, um das Ergebnis richtig zu interpretieren.

Nun aber zu dem Punkt, an dem eine vollständige Kalendertabelle wichtig wird. Betrachten wir den Februar für 2018 (3), so fällt auch hier auf, dass der YTD_PY-Wert (46€) am 15.02.2018 verschieden ist vom Wert des gesamten Februar (59€). Ein solches Ergebnis verwundert den Betrachter eines Berichtes häufig, denn es sieht nicht korrekt aus. Diesmal liegt das Ergebnis jedoch nicht am Schaltjahr, sondern daran, dass meine Kalendertabelle lediglich bis zum 15.02.2018 geht. Es ist also vermeidbar. Zwar habe ich in meiner Faktentabelle bisher nur Werte bis zum 15.02.2018, aber meine Kalendertabelle sollte das gesamte Kalender- bzw. Geschäftsjahr abbilden. Ich erweitere nun meine Kalendertabelle und aktualisiere das Datenmodell:

Auch, wenn ich noch nicht alle Werte für Februar 2018 in meiner Faktentabelle habe, bin ich in der Lage, für in der Zukunft liegende Tage (rot gerahmt), Vorjahreswerte zu kalkulieren. Dies geschieht in meinem Beispiel automatisch, wenn ich die Kalendertabelle vollständig erstelle. Es gibt dem Betrachter ein größeres Vertrauen in die erzeugten Zahlen, wenn ein Year-to-date-Wert am letzten Tag des Monats auch gleichzeitig dem Gesamtwert des Monats entspricht, denn das wäre auch meine Erwartung 🙂

Sortierspalten für Textspalten

Die nächste technische Anforderung an eine Kalenderatbelle, die ich in diesem Beitrag erwähnen möchte, beschäftigt sich mit dem Sortieren von Textspalten. Textspalten innerhalb einer Kalendertabelle, wie beispielsweise die Spalte „Name des Tages“, mit Ihren Werten „Montag, Dienstag, …“ werden in Visualisierungen automatisch alphabetisch sortiert. Für die Tage der Woche ergäbe sich also folgende Darstellung:

Da eine alphabetische Sortierung in den seltesten Fällen gewünscht ist, ist es sinnvoll zusätzlich zu der entsprechenden Textspalte eine weitere nummerische Spalte zu erzeugen. Wichtig ist hierbei, dass es je Textwert genau einen eindeutigen nummerischen Wert geben muss:

Indem ich im Datenmodell definiere, dass die Spalte „Name des Tages“ nach der Spalte „Tag der Woche (Sort.)“ sortiert werden soll, werden die Tagesnamen danach in allen Visualisierungen korrekt sortiert.

Die Kalendertabelle als Datumstabelle markieren

Sowohl Power Pivot, als auch Power BI Desktop verfügen über die Möglichkeiten, eine Kalendertabelle als Datumstabelle zu markieren. Nachfolgend siehst Du, wie dies in Power BI Desktop und Power Pivot bewerkstelligt werden kann:

 

Wichtig ist diese Festlegung aus zwei Gründen:

1. Gilt für Power Pivot/ Power BI Desktop: Nur, wenn die Datumsspalte der Kalendertabelle explizit als solche definiert ist, kann zweifelsfrei davon ausgegangen werden, dass die Time Intelligence-Funktionen in DAX einwandtfrei funktionieren. Marco Russo hat hierzu einen detaillierten Artikel verfasst, den ich Dir für das tiefere Verständnis sehr ans Herz legen kann.
2. Gilt für Power BI Desktop: Power BI Desktop erstellt grundsätzlich für jede im Datenmodell existierende Datumsspalte eine versteckte, sehr einfache Kalendertabelle, bestehend aus Jahr, Quartal Monat und Tag. Dies bläht das Datenmodell unnötig auf. Definiert man in Power BI Desktop eine Datumstabelle , werden diese versteckten Datumstabellen nicht mehr erzeugt und bereits bestehende entfernt. ACHTUNG: Falls Du bereits Kalkulationen basierend auf diesen automatisch erzeugten Tabellen vorgenommen hast (was nicht best practise wäre), werden deine bestehenden Measures danach nicht mehr funktionieren.

Fazit

Ich habe mich in diesem Beitrag bemüht aufzuzeigen, welche Regeln beim Erstellen einer Kalendertabelle für Power BI und Power Pivot beachtet werden sollten. Mein Anliegen war es dabei, nicht nur die Regeln zu nennen, sondern auch aufzuzeigen, warum diese Regeln notwendig sind, oder anders: Welche Konsequenzen es hat, diese Regeln nicht einzuhalten.

Das Entwicklerteam der DAX-Engine, hat einmal Grundregeln formuliert, die sich auf die Nutzung von Time Intelligence-Funktionen in DAX beziehen:

1. Nutze niemals datime-Spalten aus Faktentabellen in Deinen Time Intelligence-Funktionen
2. Erzeuge immer eine separate Kalendertabelle
3. Versichere Dich, dass Deine Kalendertabelle einen lückenlosen Zeitraum abdeckt
4. Erzeuge Beziehungen zwischen den Faktentabellen und Deiner Kalendertabelle
5. Die datetime-Spalte in der Kalendertabelle muss die Granularität Tag haben und darf nicht kleiner sein (keine Uhrzeiten)
6. Markiere die Kalendertabelle als Datumstabelle und definiere die Datumsspalte.

Ich hoffe mein Beitrag hat dazu beigetragen, dass Du diese Regeln verstehen kannst 🙂

Nachdem ich in diesem Beitrag über die technischen Anforderungen einer Kalendertabelle geschrieben habe, wird sich im nächsten Beitrag alles um die inhaltlichen Anforderungen einer Kalendertabelle drehen.

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

Ich schreibe meine Beiträge für Dich, den Leser. Bitte schenke mir eine Minute Deiner Zeit und bewerte die folgenden Kategorien, um mir zu helfen meine Beiträge so gut wie möglich zu schreiben. Danke 🙂