Klouzavý průměr v DAX

Vložil Petr Tkadlčík

Klouzavý průměr v DAX
Tento článek obsahuje příklad s výpočtem klouzavého průměru pomocí jazyka DAX v Power BI. Příklady jsou vytvořeny ve cvičném Power BI souboru Adventure Works DW 2020.pbix, soubor s řešením je dostupný ke stažení níže pod tímto příspěvkem.

První část příspěvku obsahuje jednoduchý postup, jak vytvořit měřítko s výpočtem klouzavého průměru bez ošetření otevřeného časového období. Následně si ukážeme nedostatky tohoto jednoduchého výpočtu a jeden z možných přístupů, jak zobrazovat výpočet pouze pro relevantní dny. V příkladech budeme počítat klouzavý průměr za posledních 50 dní a 200 dní. Období pro výpočet ale může být jednoduše upraveno změnou hodnoty jedné proměnné.

Výpočet klouzavého průměru v jazyku DAX

V příkladech budeme pracovat s měřítkem [Prodeje], které počítá sumu prodejů z tabulky 'Sales' v aktuálním kontextu vyhodnocení.

Měřítko:

Prodeje = SUM(Sales[Sales Amount])

Měřítko [Prodeje] můžeme vložit do vizuálu Matrix, spolu s měsíci a dny v řádcích.

Klouzavý průměr v DAX 2

Měřítko [Prodeje] vrací v každém řádku tabulky sumu za prodané produkty v daném období. Jednoduchý výpočet 50-ti denního klouzavého průměru může vypadat následovně.

Měřítko:

Klouzavý průměr 50denní (jednoduché) =
VAR MaxDateAktualniKontext = MAX('Date'[Date])
VAR Obdobi50Dni =
    DATESINPERIOD
    (
        'Date'[Date],
        MaxDateAktualniKontext,
        -50,
        DAY
    )
VAR Vysledek =
    AVERAGEX
    (
        Obdobi50Dni,
        [Prodeje]
    )
RETURN
    Vysledek

Do první proměnné MaxDateAktualniKontext si uložíme nejvyšší datum v aktuálním kontextu vyhodnocení. Funkce DATESINPERIOD() pak v každém řádku tabulky obsahuje tabulku s padesáti předchozími dny. Tuto tabulku použijeme pro výpočet průměru v iterační funkci AVERAGEX(). Nové měřítko můžeme vložit do připraveného vizuálu a  zobrazit si výsledek.

Klouzavý průměr v DAX 3

Takto vytvořené měřítko sice vrací správné hodnoty, ale výpočet probíhá také pro dny, ve kterých nedává smysl. V použitém modelu jsou k dispozici data za prodeje produktů od 1. července 2017. Na začátku tohoto období proto nemáme k dispozici data za předchozích padesát dnů.

Klouzavý průměr v DAX 4

Měřítko [Klouzavý průměr 50 dní jednoduché] počítá 1. července 2017 průměr pouze za tento den, protože dřívější data nejsou k dispozici. 2. července 2017 jde o průměr za dva dny, atd. Padesáti denní klouzavý průměr proto chceme obvykle zobrazovat až padesátý den od prvního dne s prodeji. V případě 200 denního průměru budeme zobrazovat klouzavý průměr až 200 dnů od prvního dostupného dne s prodeji. Dále budeme muset ošetřit, aby se výpočet nezobrazoval v budoucí dny, ve kterých ještě nedošlo k žádným prodejům.

Klouzavý průměr v DAX 5

V použitém modelu jsou k dispozici prodeje pouze do 15. června 2020. To by měl být poslední den, pro který chceme výpočet klouzavého průměry zobrazovat. 

Klouzavý průměr (50denní)

Ve výpočtu 50denního klouzavého průměru si nejdříve definujeme proměnou, do které budeme vkládat délku období pro průměr. Dále budeme potřebovat čtyři proměnné s  různými dny. První dva dny budeme potřebovat pro ohraničení období, pro které budeme výpočet zobrazovat. Další dva dny budeme potřebovat pro ohraničení období pro samotný výpočet klouzavého průměru. Data pro ohraničení výpočtu průměru se budou dynamicky měnit v každém řádku tabulky, s ohledem na kontext vyhodnocení. Na základě těchto hodnot si vytvoříme tabulku s 50 dny, kterou použijeme pro výpočet klouzavého průměru. Samotný výpočet může vypadat následovně.

Měřítko:

Klouzavý průměr 50denní (ošetřený začátek a konec období) =
VAR DelkaObdobiDny = 50
VAR MaxDateCelkem =
    CALCULATE
    (
        MAX(Sales[Order Date]),
        REMOVEFILTERS()
    )
VAR MinDateCelkem =
    CALCULATE
    (
        MIN(Sales[Order Date]),
        REMOVEFILTERS()
    )
VAR MaxDateProPrumer =
    CALCULATE
    (
        MAX('Date'[Date]),
        FILTER
        (
            VALUES('Date'[Date]),
            'Date'[Date] <= MaxDateCelkem
        )
    )
VAR MinDateProPrumer = MaxDateProPrumer - DelkaObdobiDny
VAR DnyProKlouzavyPrumer =
    FILTER
    (  
        ALL('Date'[Date]),
        AND
        (
            'Date'[Date] <= MaxDateProPrumer,
            'Date'[Date] > MinDateProPrumer
        )
    )
VAR VypocetKlouzavyPrumer =
    AVERAGEX
    (
        DnyProKlouzavyPrumer,
        [Prodeje]
    )
VAR Vysledek =
    IF
    (
        MinDateCelkem <= MinDateProPrumer + 1,
        VypocetKlouzavyPrumer,
        BLANK()
    )
RETURN
    Vysledek

Nové měřítko můžeme vložit do původního vizuálu a porovnat tak rozdíl s původním jednoduchým výpočtem klouzavého průměru.

Klouzavý průměr v DAX 6

Jak je možné vidět na obrázku výše, měřítko [Klouzavý průměr 50denní (ošetřený začátek a konec období)] vrací hodnoty až padesátý den od prvního dne s prodeji. Na následujícím obrázku pak můžeme vidět rozdíl mezi měřítky na konci období.

Klouzavý průměr v DAX 7

Klouzavý průměr si můžeme zobrazit v grafu a vidět tak trend prodejů, oproti kolísajícím prodejům v jednotlivých dnech.

Klouzavý průměr v DAX 8

Podobným způsobem si můžeme vytvořit výpočet klouzavého průměru také pro 200denní období.

Klouzavý průměr (200denní)

Pro výpočet 200denního klouzavého průměru můžeme použít podobný postup, jako pro výpočet 50denního klouzavého průměru. Jedinou změnou bude hodnota první proměnné DelkaObdobiDny, která bude obsahovat číslo 200.

Měřítko:

Klouzavý průměr 200denní (ošetřený začátek a konec období) =
VAR DelkaObdobiDny = 200
VAR MaxDateCelkem =
    CALCULATE
    (
        MAX(Sales[Order Date]),
        REMOVEFILTERS()
    )
VAR MinDateCelkem =
    CALCULATE
    (
        MIN(Sales[Order Date]),
        REMOVEFILTERS()
    )
VAR MaxDateProPrumer =
    CALCULATE
    (
        MAX('Date'[Date]),
        FILTER
        (
            VALUES('Date'[Date]),
            'Date'[Date] <= MaxDateCelkem
        )
    )
VAR MinDateProPrumer = MaxDateProPrumer - DelkaObdobiDny
VAR DnyProKlouzavyPrumer =
    FILTER
    (  
        ALL('Date'[Date]),
        AND
        (
            'Date'[Date] <= MaxDateProPrumer,
            'Date'[Date] > MinDateProPrumer
        )
    )
VAR VypocetKlouzavyPrumer =
    AVERAGEX
    (
        DnyProKlouzavyPrumer,
        [Prodeje]
    )
VAR Vysledek =
    IF
    (
        MinDateCelkem <= MinDateProPrumer + 1,
        VypocetKlouzavyPrumer,
        BLANK()
    )
RETURN
    Vysledek

Pokud vložíme měřítko [Klouzavý průměr 200denní (ošetřený začátek a konec období)] do grafu, spolu s výpočtem 50denního klouzavého průměru a s hodnotami prodejů v jednotlivých dnech, můžeme lépe pozorovat trend, jakým se objem prodejů vyvíjí v čase.

Klouzavý průměr v DAX 9

Na obrázku výše je také možné vidět, že měřítko s 50denním klouzavý průměrem je vykresleno dříve, v porovnání s 200denním klouzavým průměrem. Obě měřítka vrací hodnoty až v době, kdy mají k dispozici dostatečný počet dnů pro výpočet klouzavého průměru. 

Shrnutí

Díky klouzavým průměrům můžeme lépe pozorovat trendy a sledovat tak vývoj jednotlivých ukazatelů. Příklady uvedené v tomto příspěvku jsou pouze jednou z možností, jak dosáhnout stejného výsledku. Při výpočtu klouzavých průměrů bychom ale vždy měli brát v úvahu otevřené časové období a zobrazovat výsledek pouze v relevantní dny. 

Stáhnout soubor s řešením.
č. 39

Komentáře

Okomentovat