10.08.2023 Dennis Kahlau
Kalendertabellen mit Time-Intelligence-Funktionen spielen vor allem im Finance & Controlling eine wichtige Rolle. Bei der Budget- und Absatzplanung beispielsweise sind sie unverzichtbar, um einzelne Kennzahlen über definierte Zeiträume hinweg vergleichen zu können. Steckt hinter einem solchen Reporting ein Data Warehouse, dann laufen die Prozesse der Datenintegration, -transformation und -bereitstellung weitestgehend automatisiert bzw. dynamisch ab. Gleiches gilt für die Aktualisierung der Tabellen, etwa zum Ende eines Geschäftsjahres.
Was aber, wenn im Unternehmen keine zentrale Anlaufstelle für die standardisierte Bereitstellung von konsolidierten Daten existiert? Oder spontan zusätzliche Quellen hinzugefügt werden sollen? Viele Anwender greifen in dieser Situation auf eine fest codierte Kalendertabelle zurück, die über Formeln mit den entsprechenden Daten verknüpft sind. Der Nachteil: Jede Aktualisierung muss mühevoll von Hand durchgeführt werden. Hinzu kommt die Zeit, die in die Auswahl und Erschließung der passenden Daten investiert werden muss.
Zwangsläufig stellt sich die Frage: Gibt es keine bessere Lösung? Für Power-BI-Nutzer auf jeden Fall. Im Folgenden möchte ich einen Weg beschreiben, über den Sie Berichtsaktualisierungen in Power BI – ohne Kalendertabelle im Backend – dynamisch gestalten können. Dabei sehen wir einerseits, wie eine vollständige Kalendertabelle erstellt und mit Time-Intelligence-Funktionen genutzt wird. Andererseits betrachten wir, wie sich Tabellen mit einzelnen Datensätzen generieren lassen.
Grundsätzliches zur Kalendertabelle
Beim Erstellen einer Kalendertabelle sind zunächst drei Punkte relevant:
- Das Datum, an dem die Tabelle beginnen soll
- Das Datum, an dem sie endet
- Die erforderliche Granularität (täglich, wöchentlich, monatlich)
Wir wollen nun erreichen, dass die Kalendertabelle in Power BI die entsprechenden Daten dynamisch aus den Quellen bezieht. So stellt eine Aktualisierung immer sicher, dass der Kalender die erforderlichen Grenzen aus Start- und Ende-Datum enthält. Der beschriebene Lösungsweg wird durch Einsatz des Power Query Editors ermöglicht.
Kalenderdaten automatisch generieren
Kalendergrenzen lassen sich mit dem Power Query Editor relativ einfach generieren. Folgende Dinge sind dabei zu beachten:
- Der Kalender umfasst das komplette Geschäftsjahr.
- Das Startdatum wird aus der Tabelle bezogen, die den frühestmöglichen Zeitpunkt enthält (z.B. aus einer Umsatz-Faktentabelle).
- Das Enddatum wird aus der Tabelle mit dem neuesten Datum bezogen (z.B. eine Budget-Tabelle).
Darüber hinaus ist sicherzustellen, dass die datentragende Spalte mit dem Namen „Datum“ versehen ist. Im Anschluss können Sie über die Benutzeroberfläche von Power Query die folgenden Schritte durchführen:
Alles in allem also ein recht einfacher Ablauf. Falls Sie sich fragen, warum wir im sechsten Schritt das Datum als Datentyp neu definieren: Dadurch gehen wir sicher, dass darauffolgend die Daten mithilfe des {0}-Formats korrekt aufgeschlüsselt werden.
Und noch eine Erläuterung zum achten Schritt: Beim Ausführen des Drilldowns ist es wichtig, dass Sie mit der rechten Maustaste auf das Datum und nicht die Spaltenüberschrift klicken. Das Datum wird daraufhin als extrahierter Wert angezeigt. Das StartDatum basiert auf {0}, dargestellt als Datumsdatentyp.
Erstellen der Kalendertabelle
Mit Hilfe der Start- und Endzeitpunkte können Sie nun eine Kalendertabelle aufbauen, die einen zusammenhängen Zeitstrahl mit täglicher Granularität enthält:
- Sie erstellen eine leere Abfrage.
- Sie fügen in die Formelleiste ein: { Number.From( StartDatum ) .. Number.From( EndeDatum ) }
Zur Erklärung: Sie können keine Liste von Datumswerten mit der { … }-Struktur erstellen. Beim Datentyp „Ganze Zahl“ ist das hingegen möglich. Daher konvertieren wir die Datumsangaben mit Hilfe der Funktion Number.From entsprechend. - Transformieren Sie das Ganze über Listentools zu einer Tabelle und bestätigen Sie mit „Okay“.
- Bennen Sie Spalte1 mit Datum um.
- Ändern Sie den Datentyp der Datumsspalte zu „Datum“.
- Geben Sie der Query einen passenden Namen, wie z.B. „Kalender“.
Weitere Datumsspalte ergänzen
Üblicherweise werden für weitere Analysezwecke zusätzliche Spalten wie „Monat“, „Monatsname“, „Quartal“, „Jahr“ o.ä. benötigt. Mit Power Query können Sie die erforderlichen Datentransformationen durchführen und zusätzlich auf die Kalendertabelle anwenden:
- Sie markieren die Spalte „Datum → Spalte hinzufügen → Datum“
- Wählen Sie die gewünschte Periode aus
- Kehren Sie zum ersten Schritt so oft wie benötigt zurück
Das Ergebnis könnte wie folgt aussehen:
Wollen Sie weitere geheime Funktionen von Power BI kennenlernen, die Ihre tägliche Arbeit effizienter gestalten? Dann schauen Sie doch mal auf der Seite Power BI vorbei oder informieren Sie sich über unser Training DAX in Power BI.
Kommentare (0)