Office-Blog

Seit Excel 2016 lassen sich Datumsangaben auch in Pivot-Tabellen, die auf dem Datenmodell aufbauen, per Rechtsklick nach Jahren, Quartalen und Monaten gruppieren. »Endlich!« werden viele von Ihnen sagen. »Bloß nicht!« sage ich, denn die Gruppierung hat zwei große Nachteile. Welche das sind und wie Sie sie umgehen, erfahren Sie in diesem Beitrag.

Bei der Verwendung des Datenmodells ist die automatische Datumsgruppierung hinderlich

Was bei der Datumsgruppierung unter der Haube im Datenmodell passiert

Bei der Gruppierung einer Datumsspalte werden meiner Tabelle im Datenmodell vier neue berechnete Spalten hinzugefügt. Mit Power Pivot kann ich diese Spalten und die darin verwendeten Formeln sogar sehen.

Nachteil 1: Die Datenbasis wird aufgebläht

Die neuen Spalten machen das Datenmodell und somit die Datei größer. Für jede Umsatzzeile werden vier neue Werte berechnet. Darunter sind natürlich viele Duplikate, denn bei Umsätzen desselben Datums sind die Ergebnisse in den neuen Spalten identisch.

Die 4 automatisch hinzugefügten Datumsspalten blähen die Datenbasis unnötig auf

Nachteil 2: DAX Time-Intelligence Funktionen liefern falsche Ergebnisse

Möchte ich in meiner Pivot-Tabelle mit DAX-Measures eigene Berechnungen hinzufügen, liefern diese unter Umständen falsche Ergebnisse. Um den kumulierten Umsatz seit Jahresanfang (year-to-date) zu berechnen, verwende ich die DAX-Funktion TOTALYTD. Leider liefert die Funktion nicht das korrekte Ergebnis:

Bei der Verwendung der Datumsspalte aus der Tabelle »Bestellungen« liefert TOTALYTD falsche Ergebnisse

Warum ist das Ergebnis von TOTALYTD falsch?

Um korrekt zu rechnen, benötigen TOTALYTD und alle anderen DAX-Zeitintelligenz-Funktionen eine Datumsspalte, die folgende Kriterien erfüllt:

• Jedes Datum kommt genau einmal vor.
• Die Datumsspalte muss lückenlos sein, d. h. kein Tag darf fehlen.
• Es sind alle Tage eines Kalenderjahres enthalten.

Diese Kriterien erfüllt die Spalte »Datum« nicht.

Die Lösung: Eine Kalendertabelle verwenden

Beide Nachteile umgehe ich durch die Verwendung einer Kalendertabelle. Genau wie mein Kalender an der Wand oder in Outlook ist eine Kalendertabelle lückenlos und enthält jeden Tag nur ein einziges Mal.

Dem Datenmodell eine Kalendertabelle hinzufügen

Nachdem ich die Gruppierung aufgehoben habe, sind auch die zusätzlichen Spalten aus meinem Datenmodell wieder verschwunden. Anschließend füge ich meinem Datenmodell mit Power Pivot über Entwurf > Kalender > Datumstabelle > Neu eine Kalendertabelle hinzu.

Mit Power Pivot dem Datenmodell per Mausklick eine Kalendertabelle hinzufügen

Zum Schluss erstelle ich noch eine Beziehung zwischen der Tabelle »Bestellungen« und der neuen Kalendertabelle.

Über die Datumsspalten werden die Tabellen »Kalender« und »Bestellungen« miteinander verknüpft

Die Kalendertabelle im DAX-Measure verwenden

Nun passe ich noch die Formel meines DAX-Measures an. Anstatt der Spalte »Datum« der Bestellungen-Tabelle verwende ich in TOTALYTD die Datumsspalte der Kalendertabelle.

Bei der Verwendung der Kalendertabelle liefert TOTALYTD das richtige Ergebnis

Tipp: Automatische Datumsgruppierung dauerhaft ausschalten

Seit Excel 2016 werden Datumswerte sogar automatisch gruppiert. Meine Kollegin Hildegard Hügemann hat in ihrem Blogbeitrag sehr gut erklärt, was dabei in »klassischen« Pivot-Tabellen (also ohne Datenmodell) zu beachten ist. Wenn Sie (wie ich) die automatische Datumsgruppierung nicht möchten, schalten Sie sie in den Optionen wie folgt aus:

Die Datumsgruppierung dauerhaft ausschalten und fehlerhafte Ergebnisse zukünftig verhindern

Mehr lernen zu Power Pivot & Co.

Möchten Sie mehr zum Datenmodell, Power Pivot und DAX lernen? Dann kommen Sie am 23. und 24. Oktober 2018 zu den Power BI Kompetenztagen nach Fulda!