Es sind diese Tage, an denen man feststellt, dass der gleitende Durchschnitt zwar als Power BI Quickmeasure vorhanden ist, aber dieses nur die von Power BI bereitgestellte Datumshierarchie unterstützt. Zum Glück gibt es auch ein entsprechendes DAX Pattern von SQLBI, jedoch passt auch dieses nicht ganz zu der vorhandenen Datumstabelle. Dieser Blogbeitrag zeigt, wie ich das DAX Pattern für die Berechnung des gleitenden Durchschnitt auf meine Datumstabelle angepasst habe.
Doch kurz zurück zum „Gleitender Durchschnitt“ Quickmeasure Problem. Es gehört zu den meistbesprochenen Themen, dass ein gutes Datenmodell über eine eigene Datumstabelle verfügen sollte und nicht die von Power BI bereitgestellte „automatische Datums-/Uhrzeittabelle“ (Datumshierarchie) verwenden sollte. Jedoch kann Power BI bei einer benutzerdefinierten Datumstabelle nicht erkennen, was z.B. die Spalten für die Aggregation auf Ebene des Monats oder des Jahres sind. Daher unterstützt der Quickmeasure Assistent für den „Gleitenden Durchschnitt“ nur die von Power BI bereitgestellte Datumshierarchie, um genau diesen „Zeitraum“ definieren zu können.
Eine weitere gute Quelle für gängige DAX-Formeln ist die Seite DAX Patterns von SQLBI, auf der man den gleitenden Durchschnitt unter „Moving averages“ findet. Jedoch benötigt dieses DAX-Pattern die dazugehörige SQLBI Datumstabelle bzw. die fortlaufende Spalte „Year Month Number“. Da ich aber weiterhin meine bereits vorhandene Datumstabelle verwenden wollte, habe ich die DAX-Formel wie folgt angepasst.
Verkaufsbetrag AVG 12M =
-- Anzahl der Monate für den gleitenden Durchschnitt definieren
VAR _MonthsInRange = 12
-- Letzten Tag des gefilterten Monat ermitteln
VAR _LastDateRange =
ENDOFMONTH ( Datum[Datum] )
-- Ersten Tag des gefilterten Monatsbereich (-12 + 1 = -11) ermitteln
VAR _FirstDateRange =
STARTOFMONTH ( DATEADD ( _LastDateRange, - _MonthsInRange + 1, MONTH ) )
-- Gefilterter Monatsbereich von Tage auf Monatsebene zusammenfassen
VAR _MonthRange =
SUMMARIZE (
FILTER (
ALLNOBLANKROW ( Datum[Monat], Datum[Datum] ),
Datum[Datum] >= _FirstDateRange
&& Datum[Datum] <= _LastDateRange
),
Datum[Monat]
)
VAR _Result =
-- Prüfung ob die gewählte Anzahl an Monaten (in der Datumstabelle) vorhanden sind
IF (
COUNTROWS ( _MonthRange ) >= _MonthsInRange,
-- Berechnung des gleitenden Durchschnitts auf Basis des Monatsbereichs
CALCULATE (
AVERAGEX ( _MonthRange, CALCULATE(SUM ( Verkaufsbeleg[Verkaufsbetrag] )) ),
ALLNOBLANKROW( Datum )
)
)
RETURN
_Result
Alternativ dazu habe ich auch den DAX-Code für den gleitenden Durchschnitt vom Quickmeasure mit der Power BI bereitgestellten Datumshierarchie auf meine eigene Datumstabelle umgestellt.
Verkaufsbetrag AVG 12M =
VAR __LAST_DATE =
ENDOFMONTH ( Datum[Datum] )
VAR __DATE_PERIOD =
DATESBETWEEN (
Datum[Datum],
STARTOFMONTH ( DATEADD ( __LAST_DATE, -11, MONTH ) ),
__LAST_DATE
)
RETURN
AVERAGEX (
CALCULATETABLE (
SUMMARIZE ( VALUES ( Verkaufsbeleg ), Datum[Monat] ),
__DATE_PERIOD
),
CALCULATE (
SUM ( Verkaufsbeleg[Verkaufsbetrag] ),
ALLNOBLANKROW ( Datum[Datum] )
)
)
Falls du also auf der Suche nach einer DAX-Formel zu Berechnung des gleitenden Durchschnitts in Power BI bist, hoffe ich, dass dir dieser Blogartikel weiterhilft. Bei weiteren Fragen hinterlasse doch einfach einen Kommentar oder schreib mich über LinkedIn an.
Links:
