Ein wichtiger Bestandteil eines jeden Datenmodells ist die Datumstabelle, die am besten dauerhaft angelegt wird und so in verschiedenen Modellen verwendet werden kann. Eine gute Datumstabelle ist relativ schnell mit herkömmlichen Excel-Methoden erstellt und genau darum soll es in diesem kurzen Beitrag gehen.
Hier erhalten Sie die vorgefertigte Version der Tabelle, wenn Sie diese nicht selbst erstellen wollen.
Was ist wichtig ? – Ein Durchgehendes Datum für weitere Ableitungen
Der Startpunkt der Kalendertabelle sollte ein Datumswert, vorzugsweise der 1. Januar eines ausgewählten Jahres sein. Wenn Sie Daten auswerten, die in der Vergangenheit liegen, sagen wir 10 Jahre um eventuell frühere Trends oder Saisonalitäten zu analysieren macht es Sinn die Datumstabelle dementsprechend auszulegen. Als Klimaforscher oder Historiker machen derlei kurze Zeitintervalle natürlich keinen Sinn. Da wir uns auf meiner Seite aber vorwiegend mit Verkaufs-, Lager- Distributions und anderen Supply Chain Management relevanten Zahlen beschäftigen, halte ich 10 Jahre für ein gutes Maß.
Genug der Rede, fangen wir an!
Wir beginnen damit die Überschrift Datumsschlüssel in Zelle A1 einer neuen Arbeitsmappe einzutragen und schreiben darunter unseren ersten Tag der Datumstabelle, den 01.01.2010. Unser vorläufiges Enddatum soll der 31.12.2030 sein. Excel bietet uns hier glücklicherweise die Option eine Datenreihe für uns zu erstellen, sodass wir nicht unsere Hand beim Scrollen verlieren. Befolgen Sie gerne die Anweisungen im Screenshot:
Der Datumsschlüssel ist nachher unser Bezugspunkt wenn es darum geht Beziehung zu anderen Datumswerten in Datentabellen herzustellen. Dies ermöglicht die Arbeit mit Time Intelligence Funktionen. In den nächsten Schritten werden wir das Datum langsam in seine Einzelteile zerlegen, sodass wir später in (Power) PivotTables verschiedene Aggregationsmöglichkeiten haben. Sobald die Reihe fertiggestellt wurde empfehle ich eine intelligente Tabelle anzulegen. Gehen Sie dazu auf Zelle A2, drücken Sie STRG+T und bestätigen danach, dass ihre Tabelle Überschriften hat. Jetzt können Sie einen Namen wie d_Kalender, d_Dates oder ähnliches vergeben, sodass es in der späteren Verformelung mit Power Pivot einfach sein wird, diese zu referenzieren.
Nun zerlegen wir das Datum und erweitern somit die Tabelle. Sie können dabei die gleiche Reihenfolge wie ich wählen oder eine Struktur für sich finden und weitere Intervalle/Parameter hinzufügen. Bei mir sehen die Formeln am Ende so aus:
| Jahr | =JAHR([@Datumsschlüssel]) |
| Halbjahr | =WENN([@Monat]<=6;”H1″;”H2″) |
| Quartal | =AUFRUNDEN(MONAT([@Datumsschlüssel])/3;0) |
| Quartalsname | =”Q”&[@Quartal] |
| Jahr-Quartalsname | =[@Jahr]&”-“&[@Quartalsname] |
| Monat | =MONAT([@Datumsschlüssel]) |
| Monatsname | =TEXT([@Datumsschlüssel];”MMMM”) |
| Jahr-Monatsname | =[@Jahr]&”-“&[@Monatsname] |
| Jahr-Monat | =[@Jahr]&”-“&[@Monat] |
| Monatstag | =TAG([@Datumsschlüssel]) |
| Wochentag | =WOCHENTAG([@Datumsschlüssel];2) |
| Name Wochentag | =TEXT([@Datumsschlüssel];”tttt”) |
| Kalenderwoche | =ISOKALENDERWOCHE([@Datumsschlüssel]) |
Laden der Datumstabelle in Power Pivot
Die Dimensionstabelle d_Kalender ist gespeichert und nun möchten wir diese in einer neuen Arbeitsmappe in ein Datenmodell von Power Pivot hieven. Dazu befolgen wir die Schritte 1 bis 8 des folgenden Screenshots:
In Schritt 7 und 8 sehen wir das Navigator-Fenster. Dies ist der Zugang zu Power Query. In neueren Excel-Versionen versteckt sich der Name etwas, aber wir befinden uns tatsächlich innerhalb des besten Datenvorbereitungstools, dass Excel passieren konnte! An dieser Stelle möchte ich eine kleine Empfehlung aussprechen: Jedes Mal, wenn Sie Tabellen in ein Datenmodell integrieren wollen, tun Sie dies bitte über Daten transformieren. Der Power Query Editor arbeitet zwar meist zuverlässig, aber manchmal auch etwas zu gründlich, sodass die ein oder andere Spalte verhunzt wird. Wie könnte es anders sein, tut der Editor genau das und verändert die Spalte Jahr-Monatsname und Jahr-Monat zum Schlechten und wir müssen diese ins gewünschte Format zurückholen.
Ganz oben links sehen wir, dass es sich tatsächlich um den Power Query Editor handelt. Schritte 1-5 befolgt und wir haben unser alten Spalten zurück. Zu guter Letzt müssen wir die Tabelle per Schließen & Laden in in unser Datenmodell übertragen.
Über Power Pivot -Verwalten, sehen wir, wie die Datumstabelle in der Rasteransicht des Tools aussieht. Falls sie Power Pivot in ihrer Excel-Oberfläche noch nicht finden, befolgen Sie doch einfach die Schritte des Videos am Boden meiner Startseite. Im Moment wissen wir, dass wir eine Datumstabelle in unser Modell geladen haben, wobei Power Pivot noch nicht so richtig unterscheiden kann ob es sich tatsächlich um eine solche handelt. Das ändern wir!
Klasse, jetzt weiß Power Pivot auch mit wem es hier zu tun hat. Jetzt fehlt nur noch ein letzter Kniff und dann lasse ich Sie in Ruhe. Wenn wir nun über Start-PivotTable in einem beliebigen Arbeitsblatt eine solche erstellen und die Monatsnamen in die Zeilensektion der Felder ziehen, merken wir, dass der April als erster Monat oben steht. Das darf und soll natürlich nicht sein. Also zurück in die Power Pivot Oberfläche und die Funktion Nach Spalten sortieren ausgeführt.
Der Vorher-Nachher Vergleich unter Bubble 4, zeigt, dass es funktioniert hat. Wenn Sie jetzt noch in Eigenarbeit das gleich für Jahr-Monatsname sortiert nach Jahr-Monat machen, erhalten Sie eine tadellose Datumstabelle, mit der Sie durchstarten können.
So, jetzt können Sie sich ein Kapitel meines E-Books sparen, ich würde mich aber dennoch freuen, wenn Sie sich das Verzeichnis anschauen und für sich entscheiden, ob Sie einen Grundkurs in Schriftform absolvieren möchten, sodass Sie auch anderen Blogposts mühelos folgen können.
Schönen Tag, ich empfehle mich.
No responses yet