Excel 2016/2013: Pivot-Berichte mit Datenmodell verbessern

Vorjahresvergleiche mit SAMEPERIODLASTYEAR

LinkedIn Learning kostenlos und unverbindlich testen!

Jetzt testen Alle Abonnements anzeigen
Wie hoch war der Umsatz in der gleichen Vorjahresperiode? Um wie viel Prozent ist er gestiegen oder gefallen? Auch Vorjahresvergleiche werden mit den DAX-Time-Intelligence-Funktionen zum Kinderspiel.
06:45

Transkript

Wie hoch war der Umsatz im letzten Jahr, oder im entsprechenden Quartal des Vorjahres? Um wieviel Prozent ist er seitdem gestiegen oder gefallen? Vorjahresvergleiche werden mit den DAX-Time-Intelligence-Funktionen zum Kinderspiel. Überzeugen Sie sich selbst davon in diesem Video. Zur Berechnung des Umsatzes haben Sie bereits ein Measure mit Hilfe der DAX-Funktion "SUMX" erstellt. Schauen wir uns das Measure noch einmal gemeinsam an. Sie finden es im PowerPivot-Fenster in der Tabelle "Bestellungen" im Berechnungsbereich. Wird der Berechnungsbereich bei Ihnen nicht angezeigt, schalten Sie ihn auf die Registerkarte "Home", in der Gruppe "Ansicht" mit einem Klick auf die Schaltfläche "Berechnungsbereich" ein. Klicken Sie das Umsatzmeasure im Berechnungsbereich an, können Sie die Formel in der Bearbeitungsleiste einsehen. Die "SUMX"-Funktion multipliziert für jede Zeile der Tabelle Bestellungen, die Menge mit dem Stückpreis und summiert anschließend alle Ergebnisse. Das Formelergebnis lautet "30.363.494€". Es handelt sich dabei, um den Gesamtumsatz über den gesamten Betrachtungszeitraum von 2014 bis 2017. Wie kommt es aber, dass das Measure in der Pivot-Tabelle für die einzelnen Jahre unterschiedliche Werte liefert. Es wird doch immer dieselbe Formel verwendet, nämlich die, die Sie gerade in der Bearbeitungsleiste gesehen haben. Die Erklärung ist recht einfach. Die Jahreszahl in der Zeilenbeschriftung der Pivot-Tabelle wirkt wie ein Filter. Bevor die "SUMX"-Funktion zeilenweise die die Bestellungentabelle durchläuft und die Menge mit dem Stückpreis multipliziert, wird die Bestellungentabelle gefiltert. Zur Berechnung des Umsatzes für 2015, werden nur die Bestellungen berücksichtigt, deren Bestelldatum zwischen dem 01.01. und dem 31.12.2015 liegt. Um dies zu simulieren, filtere ich nun die Bestellungentabelle im PowerPivot-Fenster. Dazu setze ich in der Spalte "Bestelldatum" einen entsprechenden Datumsfilter. Es sollen nur alle Daten angezeigt werden zwischen dem 01.01.2015 und dem 31.12.2015. Siehe da, das Measure zeigt im Berechnungsbereich nun nicht mehr das Gesamtergebnis von etwas 30.000.000€ an, sondern nur noch knapp 5,9.000.000€ und damit genau die Zahl, die auch in der Pivot-Tabelle für 2015 ausgewiesen wird. Um den entsprechenden Vorjahresumsatz zu berechnen, muss der Filter, der die Zeilenbeschriftung in der Pivot-Tabelle mitbringt, verändert werden. Legen Sie hierzu über die Registerkarte "PowerPivot" ein neues Measure an. Die "DAX"-Funktion zum Ändern des Filterkontexts heißt "CALCULATE". Sie wählen einen Ausdruck in einem durch Filter geänderten Kontext aus. Als erstes Argument möchte die "CALCULATE"-Funktion den Ausdruck, den Sie im geänderten Filterkontext ermitteln möchten, also des Umsatzmeasure. Das zweite Argument ist der zu setzende Filter. Um den bestehenden Datumsfilter um genau ein Jahr in die Vergangenheit zu schieben, gibt es sogar eine eigene Funktion: "SAMEPERIODLASTYEAR". Auch diese Time-Intelligence-Funktion verlangt wieder den Parameter "Dates". Also die Spalte ihrer Kalendertabelle, die vom Datentyp "Date" ist. Legen Sie nun noch das gewünschte Zahlenformat fest und beenden Sie die Measureerstellung mit einem Klick auf "OK". Das wars schon. Sehen Sie sich das Ergebnis in der Pivot-Tabelle an. Zu jedem Jahr, wird der zugehörige Vorjahreswert ausgegeben. Jetzt kommt der Clou. Diese Berechnung funktioniert nicht nur auf Jahresebene, sondern auch auf Quartalsebene und Monatsebene. Damit haben Sie ihr Zwischenziel erreicht. Sie kennen den Umsatz der gleichen Vorjahresperiode. Nun berechnen Sie die Umsatzveränderung in Prozent. Dazu legen Sie ein weiteres Measure an und dividieren die Umsatzsteigerung durch den Vorjahresumsatz. Nennen Sie das Measure "Umsatz % gegenüber Vorjahr". Die Umsatzsteigerung ergibt sich aus der Differenz von Umsatz und Umsatzvorjahr. Dies dividieren Sie durch den Vorjahresumsatz. Das Ergebnis soll in Prozent mit zwei Dezimalstellen angezeigt werden. Wie Sie sehen, funktioniert diese Berechnung ebenfalls auf sämtlichen Hierarchieebenen, aber das Measure hat noch einen kleinen Schönheitsfehler. Falls der Vorjahresumsatz nicht existiert, schlägt die Division fehl. Passen Sie ihr Measure daher noch an. Wie würden Sie diesen Fehler in Excel abfangen? Richtig, mit "WENN", beziehungsweise "WENN Fehler". "DAX" bietet Ihnen aber noch eine bessere Lösung: die Funktion "DIVIDE". Dort ist die Fehlerbehandlung für Division durch 0 bereits eingebaut. Die Funktion "DIVIDE" möchte lediglich den Zähler als erstes Argument und den Nenner als zweites Argument. Das wars und schon ist ihr Fehler verschwunden. Hätten Sie gedacht, dass die Berechnung des jeweiligen Vorjahresumsatzes so einfach ist und das für eine Jahres-, Quartals- oder Monatsbetrachtung die Formel nicht einmal angepasst werden muss, sondern unabhängig von Zeitinterval immer funktioniert In diesem Video haben Sie einen ersten Einblick erhalten, wie leistungsfähig DAX-Measures sind und vor allem, wie sie funktionieren. Merken Sie sich, bevor die Measure-Formel ausgewertet wird, werden sämtliche betroffenen Tabellen gefiltert. Benötigen Sie einen davon abweichenden Filterkontext, verwenden Sie die Funktion "CALCULATE". Mit "SAMEPERIODLASTYEAR" haben Sie automatisch Zugriff auf die entsprechende Vorjahresperiode. Ob es sich dabei um ein ganzes Jahr, ein Quartal oder einen Monat handelt, spielt keine Rolle. Die Funktion "DIVIDE" sorgt für eine fehlerfreie Division und nimmt Ihnen die Prüfung ab, ob der Nenner "0" ist.

Excel 2016/2013: Pivot-Berichte mit Datenmodell verbessern

Revolutionieren Sie Ihre Pivot-Auswertungen mit einem Datenmodell, Power Pivot und DAX-Funktionen

1 Std. 46 min (33 Videos)
Derzeit sind keine Feedbacks vorhanden...

Dieser Online-Kurs ist als Download und als Streaming-Video verfügbar. Die gute Nachricht: Sie müssen sich nicht entscheiden - sobald Sie das Training erwerben, erhalten Sie Zugang zu beiden Optionen!

Der Download ermöglicht Ihnen die Offline-Nutzung des Trainings und bietet die Vorteile einer benutzerfreundlichen Abspielumgebung. Wenn Sie an verschiedenen Computern arbeiten, oder nicht den ganzen Kurs auf einmal herunterladen möchten, loggen Sie sich auf dieser Seite ein, um alle Videos des Trainings als Streaming-Video anzusehen.

Wir hoffen, dass Sie viel Freude und Erfolg mit diesem Video-Training haben werden. Falls Sie irgendwelche Fragen haben, zögern Sie nicht uns zu kontaktieren!