Einfacher Wochenvergleich in Power BI mit der neuen OFFSET-Funktion

Bei der Datenanalyse besteht eine häufige Anforderung darin, die Werte einer Woche mit denen der Vorwoche zu vergleichen. Ein solcher Wochenvergleich liefert wichtige Erkenntnisse, da man einen größeren Zeitrahmen vergleicht und – ohne monatelang zu warten – immer noch sehen kann, wohin der Trend geht.

Wie man den Jahreswechsel am besten gestaltet

In der Regel kann man das Measures in einem bestimmten Zeitrahmen nehmen, z. B. Woche 28 des laufenden Jahres 2023, und es mit der Vorwoche, in diesem Fall Woche 27 des Jahres 2023, vergleichen. Ein solcher Vergleich ist jedoch eine Herausforderung, da man den Jahreswechsel irgendwie berücksichtigen muss. Man muss es irgendwie schaffen, dass Woche 1 im Jahr 2023 mit Woche 52 im Jahr 2022 verglichen wird.
Und das ist auch die größte Herausforderung im Wochenvergleich. Man kann nicht einfach nur die letzten Wochen vergleichen, als ob es sich um eine fortlaufende Zahl handeln würde.

Neuer Ansatz mit Window-Funktion OFFSET

In der Vergangenheit gab es einige Ansätze, wie man dieses Problem lösen kann, aber diese waren alle relativ komplex. Mit der Einführung der neuen Window-Funktionen können wir nun jedoch einen viel einfacheren Lösungsansatz verwenden.

Wie bereits erwähnt, werden wir die neue OFFSET-Funktion verwenden. Die Funktion OFFSET gibt eine Zeile relativ zur aktuellen Zeile zurück. Und das ist genau das, was wir hier brauchen. Wir wollen den Wert der letzten Zeile (= Vorwoche) mit dem der aktuellen vergleichen.

Als allgemeines Beispiel für die Funktion OFFSET verwende ich eine einfache Tabelle und zeige die Marken und den Umsatz an. Wenn ich nun die Funktion OFFSET in einem neuen Measure verwende, kann ich den Wert relativ zur aktuellen Position ermitteln. Im folgenden Beispiel habe ich definiert, dass ich das vorherige Element (-1) nach Marke haben möchte. Die Tabelle sieht also wie folgt aus, wobei ich immer die Daten aus der letzten Zeile im Measure OFFSET Brand erhalte:

Die Syntax ist sehr einfach, da wir nur CALCULATE verwenden, um den Filterkontext zu ändern, und OFFSET als Modifikator verwenden. Wir legen fest, um wie viel Elemente der Versatz erfolgen soll. Das Measure OFFSET Brand sieht dann folgendermaßen aus:

OFFSET Brand = 
CALCULATE(
    [Sales Amount],
    OFFSET(
        -1,
        ORDERBY( 'Product'[Brand], ASC )
    )
)

Zum Zeitpunkt der Erstellung dieses Artikels unterstützt der Syntax-Highlighter in Power BI die neuen Fensterfunktionen noch nicht vollständig. Obwohl das Measure also korrekt ist, könnte es einige rote Linien im Editor geben:

Hoffentlich unterstützt der Syntax-Highlighter die Fensterfunktionen vollständig, wenn du diesen Artikel liest.

Erstellung einer Spalte für die gesamte Woche

Um den neuen Ansatz zu implementieren, müssen wir in unserem Datenmodell lediglich eine Spalte mit der Woche und dem Jahr für jeden Tag haben. Diese Spalte wird für die Sortierung des Offsets verwendet, um die Vorwoche zu erhalten.

Wir wollen allerdings nur ganze Wochen vergleichen. Aus diesem Grund müssen wir eine Spalte hinzufügen, die den in Europa üblichen ISO 8601-Standard verwendet. Der normale US-amerikanische und australische Standard wird nicht funktionieren, da er durch den Jahreswechsel halbe Wochen haben kann. In dem Artikel “ISO 8601 Woche in DAX berechnen” beschreibe ich das ganze Szenario im Detail.

Hier die Kurzerklärung.

Im US-Kalender ist die Woche des 1. Januar immer die Woche 1 des neuen Jahres. Dies können wir nicht verwenden, da wir beispielsweise Woche 2 mit 7 Tagen mit Woche 1 mit nur 3 Tagen im Jahr 2021 vergleichen würden. Auch die Woche 53 im Jahr 2020 hätte nur 4 Tage. Dieser Kalender ist also nicht sinnvoll, da er nicht ganze Wochen vergleicht:

Der ISO 8601-Kalender bezieht sich auf ganze Wochen. Dementsprechend können wir diesen Kalender nutzen, da wir immer volle Wochen vergleichen können:

Aus diesem Grund werden wir eine neue Spalte mit der Kombination aus dem Jahr und der Wochennummer erstellen. Wir können dies in DAX mit der folgenden berechneten Spalte ganz einfach umsetzen:

Year Week = 
VAR _YearCurrentRow = YEAR ( 'Date'[Date] )
VAR _MonthCurrentRow = MONTH( 'Date'[Date] )
VAR _WeekCurrentRow = WEEKNUM ( 'Date'[Date], 21 )
VAR _YearReturn = IF( _WeekCurrentRow > 50 && _MonthCurrentRow = 1, _YearCurrentRow -1, _YearCurrentRow )
RETURN
    _YearReturn * 100 +_WeekCurrentRow

Beachte die Zeile 5 mit der Variablen _YearReturn. Wir müssen dafür sorgen, dass die ersten Tage des neuen Jahres noch das Jahr und die Woche des alten Jahres erhalten. Wie in dem letzten Bild sind im Jahr 2021 der 1. bis 3. Januar nach ISO-Norm noch in Woche 53 des Jahres 2020.

Auf diese Weise sieht die Spalte für Jahr und Woche wie folgt aus:

Verwendung der Funktion OFFSET, um die Werte der Vorwochen zu erhalten

Nachdem wir diese Spalte erstellt haben, ist der Rest ziemlich einfach. Wir können nun CALCULATE verwenden, um den Filterkontext zu ändern, und OFFSET, um die Vorwoche zu erhalten:

Sales Previous Week = 
CALCULATE(
    [Sales Amount],
    OFFSET(
        -1,
        ORDERBY( 'Date'[Year Week], ASC )
    )
)

Von da an erhalten wir die Werte der Vorwoche mit dem neuen Measure:

Das bedeutet auch, dass es von da an ziemlich einfach ist, alle Arten von Measures zu erstellen, die einen Vergleich von Woche zu Woche berücksichtigen. Zum Beispiel können wir jetzt leicht die prozentuale Veränderung von einer Woche zur nächsten berechnen:

Week over Week in % = 
DIVIDE( ([Sales Amount] - [Sales Previous Week] ) , [Sales Previous Week] )

Auf diese Weise erhalten wir die Entwicklung in Prozent:

Zusammenfassung Wochenvergleich mit OFFSET

Wir wissen, dass der Vergleich von Woche zu Woche eine Herausforderung darstellt, da wir irgendwie mit dem Jahreswechsel umgehen müssen. Mit einer korrekten Wochenspalte im ISO-Format können wir leicht eine Reihenfolge für alle Wochen und über mehrere Jahre erstellen.

Danach können wir die neue Funktion OFFSET verwenden, um immer das vorherige Element als Wert der Vorwoche zu erhalten. Dies gilt auch für den Jahreswechsel, da wir auf diese Weise immer das richtige Vorgängerelement erhalten.

Download Beispieldatei