Power QueryDirekter Vergleich mit dem Vorjahresmonat
Warum der Vergleich mit dem Vorjahresmonat wichtig ist
In der Datenanalyse ist es unerlässlich, eine Kennzahl im Zeitverlauf zu betrachten. Häufig greifen wir dabei auf den Vergleich mit dem Vormonat zurück – ein schneller und oft hilfreicher Indikator für kurzfristige Veränderungen.
Doch dieser Vergleich stößt an seine Grenzen, wenn saisonale Effekte eine Rolle spielen:
- Warum sind die Umsätze im Juli höher als im Juni?
- Ist der Rückgang im Januar ein echtes Problem – oder einfach typisch für die Jahreszeit?
In solchen Fällen liefert ein Vergleich mit dem gleichen Monat des Vorjahres wesentlich fundiertere Aussagen. Denn so berücksichtigen Sie wiederkehrende Schwankungen wie Sommer- oder Weihnachtsgeschäft, Urlaubszeiten oder witterungsbedingte Effekte.
Gerade im Vertriebs- oder Finanzcontrolling wird dieser sogenannte Year-over-Year-Vergleich (YoY) regelmäßig eingesetzt, um Entwicklungen realistisch einordnen zu können.
In diesem Beitrag erfahren Sie, wie Sie mit Power Query in Excel automatisiert die Differenz zum entsprechenden Vorjahresmonat berechnen können – ohne manuelles Suchen, Kopieren oder Filtern.
Damit sind Sie bestens gerüstet für eine aussagekräftige Zeitreihenanalyse – sei es im Monatsreporting, in Dashboards oder bei der Budgetkontrolle.
Beispiel: Wie entwickelt sich der Umsatz?
In der folgenden Abbildung sehen Sie eine Umsatzaufstellung, die als Beispiel dienen soll. Neben dem Umsatz des aktuellen Jahres sollen hier auch der Umsatz des Vorjahres und die entsprechende Differenz (Umsatz – Umsatz Vorjahr) dargestellt werden.
Hilfsspalte einfügen
Um einen Verweis zum Vorjahresumsatz herstellen zu können, benötigen Sie eine Hilfsspalte. In der Hilfsspalte muss die Jahreszahl des entsprechenden Vorjahresumsatzes stehen.
Aktivieren Sie im Menüband die Befehlsfolge Spalte hinzufügen > Benutzerdefinierte Spalte.
Es öffnet sich das Dialogfeld Benutzerdefinierte Spalte.
Erfassen Sie als Formel die folgende Zeichenfolge =[Jahr]-1 und bestätigen Sie die Formel, indem Sie das Dialogfeld durch Klick auf OK schließen.
Beachten Sie: Der Spaltentitel mit der Angabe des „Umsatzjahres“ ist dabei mit „Jahr“ bezeichnet. Das erkennen Sie auch in der rechten Box, in der die verfügbaren Spalten für Ihre Berechnungen benannt sind.
Sie erhalten in der neuen Spalte die Jahreszahl des entsprechenden Vorjahresumsatzes dargestellt.
Passen Sie als Nächstes den Spaltennamen an, indem Sie doppelt mit der linken Maustaste auf den Spaltennamen klicken. Überschreiben Sie jetzt einfach den Spaltentitel mit Vorjahr.
Abfrage mit sich selbst kombinieren
Aktivieren Sie jetzt im Menüband die Befehlsfolge Start > Kombinieren > Abfragen zusammenführen.
Es öffnet sich das Dialogfeld Zusammenführen. Die bestehende Abfrage Tabelle1_1 ist als erste Abfrage ausgewählt.
Sie verknüpfen jetzt diese Abfrage mit sich selbst. Wählen Sie dazu als zweite Abfrage ebenfalls Tabelle1_1 aus.
Sie verknüpfen die beiden Tabellen über die folgenden Spalten:
- Vorjahr (erste Tabelle) zu Jahr (zweite Tabelle)
- Monat (erste Tabelle) zu Monat (zweite Tabelle)
Aktivieren Sie die entsprechenden Spalten (siehe folgende Abbildung). Wählen Sie als Join-Art: Linker äußerer Join.
Bestätigen Sie die Einstellungen, indem Sie das Dialogfeld durch Klick auf OK schließen.
Es wird nun eine neue Spalte am rechten Rand eingefügt. Klicken Sie hier am rechten Rand des Spaltentitels auf die zwei Pfeilchen, um den Inhalt (Spalten) der zweiten Tabelle auszuwählen.
Sie bekommen hier die Spalten der Abfrage angezeigt. Wählen Sie hier nur die Spalte Umsatz aus.
Sie bekommen jetzt den Umsatz des Vorjahres in dieser Spalte dargestellt.
Sie haben die Abfrage mit sich selbst über die Spalte Vorjahr verknüpft. Diese Spalte (Vorjahr) enthält die Jahreszahl der aktuellen Zeile verringert um den Wert 1. Über diese Spalte verknüpfen Sie die gleiche Abfrage mit der Jahreszahl der jeweiligen Zeile. Sie erhalten somit den Umsatz des Vorjahres in der jeweiligen Zeile.
Beachten Sie: Für das erste Jahr in der Tabelle mit den Umsatzzahlen gibt es keine Umsatzwerte für das Vorjahr. In der Power-Query-Tabelle steht deshalb in den entsprechenden Zeilen der Wert „null“.
Benennen Sie noch die Spalte um, indem Sie doppelt mit der linken Maustaste auf den Spaltentitel klicken. Sie können den Namen jetzt einfach überschreiben in Vorjahresumsatz.
Differenz berechnen und Umsatz vergleichen
Jetzt können Sie die Veränderung zum Vorjahr berechnen.
Markieren Sie bei gedrückt gehaltener Strg-Taste die Spalten Umsatz und Vorjahresumsatz und aktivieren Sie dann im Menüband die Befehlsfolge Spalte hinzufügen > Standard > Subtrahieren.
Power Query erstellt daraufhin eine neue Spalte mit der Differenz zwischen Umsatz und Vorjahresumsatz.
Benennen Sie als Nächstes die neue Spalte noch in Veränderung zu VJ um, damit die Spalte einen aussagekräftigen Titel besitzt.
Entfernen Sie jetzt noch die Spalte Vorjahr, indem Sie mit der rechten Maustaste auf den Spaltentitel klicken und im Kontextmenü den Eintrag Entfernen auswählen.
Diese Power-Query-Tabelle können Sie abschließend in Ihre Excel-Tabelle übernehmen und dort weitere Analysen durchführen oder den Umsatzverlauf und den Vergleich in Diagrammen visualisieren.
Anmerkung: Dies ist eine einfache und schnelle Möglichkeit, um die Umsätze eines Jahres mit den entsprechenden Umsätzen des Vorjahres zu vergleichen. Weitergehende Vergleichsmöglichkeiten bieten die sogenannten DAX-Funktionen in Excel; zum Beispiel die Funktion SAMEPERIODLASTYEAR(<Datum>).
