Výpočet PY, PQ, PM, YOY, QOQ, MOM a YOY% v DAX

Článek obsahuje příklady výpočtů hodnot za aktuální období v porovnání s hodnotami za stejné období v předchozím roce (PY), předchozím čtvrtletí (PQ) a předchozím měsíci (PM), včetně porovnání těchto hodnot s  aktuálním obdobím v absolutním a relativním vyjádření (YOY, QOQ, MOM, YOY %, QOQ %, MOM %).  Postup pro vytvoření výpočtů je u všech časových kalkulací podobný. Pomocí časových funkcí budeme ovlivňovat filtry ve funkci CALCULATE() tak, abychom v aktuálním období dostali hodnoty za zvolené předcházející období. Při práci s vestavěnými časovými funkcemi bychom měli mít na paměti základními předpoklady, které jsou nezbytné pro správné fungování vestavěných časových funkcí. Tyto předpoklady jsou popsány v samostatném příspěvku.

Příklady v tomto příspěvku jsou vytvořeny ve cvičném Power BI souboru Adventure Works DW 2020.pbix , který je volně dostupný ke stažení na stránkách Microsoftu. Soubor s řešením můžete najít ke stažení níže pod tímto příspěvkem.

Porovnání prodejů s prodeji v předchozím období v DAX

V příkladech uvedených v tomto článku budeme porovnávat hodnoty prodejů z tabulky 'Sales' v aktuálním období s prodeji za vybrané předcházející období. Měřítko, které vrací hodnotu prodejů za aktuální období, má následující definici.

Měřítko:

Prodeje = SUM(Sales[Sales Amount])

Pro zobrazení výsledků si v Power BI souboru připravíme vizuál Matrix, do kterého přidáme do řádku roky, čtvrtletí , měsíce a dny z tabulky 'Date'. 

Měřítko [Prodeje] v hodnotách vizuálu Matrix zobrazuje sumu prodejů za aktuální období, v kontextu filtru jednotlivých období v řádcích vizuálu.

Prodeje v předchozím roce (PY)

Pro získání hodnoty prodejů za stejné období v předcházejícím roce použijeme kombinaci DAX funkcí CALCULATE() a DATEADD(). Prvním argumentem funkce CALCULATE() bude výpočet, který chceme vyhodnotit v kontextu filtru, upraveném funkcí DATEADD() ve druhém argumentu funkce CALCULATE(). 

Funkce DATEADD() má tři povinné argumenty. Prvním argumentem je odkaz na sloupec z datumové tabulky, který obsahuje hodnoty ve formátu DATE nebo DATETIME. Druhým argumentem funkce je počet intervalů, o kolik se chceme posunout. Třetím argumentem funkce DATEADD() je samotný interval, který může nabývat hodnot YEAR, QUARTER, MONTH nebo DAY. Výsledkem funkce DATEADD() je tabulka, která obsahuje všechny dny odpovídající dnům v aktuálním kontextu vyhodnocení, posunuté o zadaný počet intervalů zpět nebo vpřed. 

Tato tabulka s jedním sloupcem a hodnotami ve formátu DATE bude součástí nového filtru při vyhodnocení výpočtu v prvním argumentu funkce CALCULATE(). Definice měřítka vracejícího prodeje v předcházejícím roce může vypadat následovně.

Měřítko:

Prodeje PY =

CALCULATE

(

[Prodeje],

DATEADD('Date'[Date], -1, YEAR)

)

Měřítko [Prodeje PY] můžeme vložit do připraveného vizuálu Matrix a porovnat si hodnoty s měřítkem [Prodeje].

Na obrázku výše můžeme vidět, že výsledek měřítka [Prodeje PY] odpovídá v každém řádku zobrazené tabulky hodnotě měřítka [Prodeje] v předcházejícím roce. Obdobně si můžeme vytvořit měřítka pro prodeje v předcházejících čtvrtletích nebo měsících, pouze změníme interval ve třetím argumentu funkce DATEADD(). Nejdříve si ale ukážeme situaci, kdy takto definované měřítko nemusí fungovat podle očekávání a také postup, jak toto chování ošetřit pomocí přidání dalšího filtru.

Prodeje v předchozím roce (PY) s ošetřením otevřeného časového období

Měřítko [Prodeje PY] nemusí vracet očekávané výsledky v případě, kdy pracujeme s otevřeným časovým obdobím. S otevřeným časovým obdobím pracujeme po většinu roku, a proto je dobré ukázat si, kde může vzniknout problém a jak tento problém odstranit. 

Pokud porovnáme výsledek měřítka [Prodeje PY] v roce 2020 hodnotou měřítka [Prodeje] v roce 2019, výsledky obou měřítek jsou shodné. Problém je, že v použitém cvičném modelu jsou dostupné hodnoty za prodeje pouze do 15. června. 

Tento den si můžeme v reálném a pravidelně aktualizovaném modelu zaměnit za aktuální den, pro který máme k dispozici nejnovější data. Přesnější by tedy bylo, pokud bychom porovnávali hodnoty v aktuálním roce, které jsou dostupné pouze do 15. června 2020, se stejným obdobím v roce předcházejícím, tedy s hodnotami prodejů do 15. června 2019. 

Jedním ze způsobů, jak dosáhnout "spravedlivého" porovnání aktuálních prodejů s prodeji za předcházející období, je přidání dalšího filtru k aktuálnímu kontextu vyhodnocení, ve kterém je vyhodnocena funkce DATEADD(). Tento filtr bude omezovat hodnoty ze sloupce 'Date'[Date], dostupné v aktuálním kontextu vyhodnocení, pouze na hodnoty menší nebo rovné než je maximální dostupné datum, pro který máme k dispozici data v modelu. Definice posledního dostupného dne pak může být různá, v závislosti na mnoha faktorech. Může se například jednat o aktuální den, který můžeme získat pomocí funkce TODAY(). Nebo může jít o den, ve kterém došlo k poslední aktualizaci dat. Také může jít poslední den, pro který jsou k dispozici záznamy o prodejích, atd. Jakmile dokážeme zjistit maximální dostupný den, můžeme omezit kontext vyhodnocení funkce DATEADD() tím, že tuto funkci vložíme do funkce CALCULATETABLE() a přidáme filtr obsahující pouze dny, které jsou dostupné v aktuálním kontextu vyhodnocení a současně jsou menší nebo rovny námi zvolenému maximálnímu dnu. V tomto příkladu si jako maximální dostupný den zvolíme konstantu, protože pracujeme s historickými a neaktualizovanými daty. Některé možnosti, jak můžeme definovat maximální den v pravidelně aktualizovaném modelu, jsou naznačeny v zakomentovaných řádcích v následující definici měřítka.

Měřítko:

Prodeje PY (skryté budoucí) =

VAR MaxDate = DATE(2020,6,15)

--VAR MaxDate = TODAY()

--VAR MaxDate = TODAY() - 1

--VAR MaxDate = CALCULATE(MAX(Sales[Order Date]),REMOVEFILTERS())

VAR Vypocet =

CALCULATE

(

[Prodeje],

CALCULATETABLE

(

DATEADD('Date'[Date], -1, YEAR),

FILTER

(

VALUES('Date'[Date]),

'Date'[Date] <= MaxDate

)

)

)

RETURN

Vypocet

Měřítko [Prodeje PY (skryté budoucí)] vložíme pro porovnání do dříve připraveného vizuálu.

Hodnota měřítka [Prodeje PY (skryté budoucí)] v řádku roku 2020 nyní odpovídá hodnotě prodejů roku 2019, ale pouze do 15. června 2019. Pokud se podíváme na výsledky ve větším detailu, můžeme například vidět, že hodnota měřítka [Prodeje PY (skryté budoucí)] v měsíci květen 2020 odpovídá hodnotě měřítka [Prodeje] v měsíci květen 2019. 

V měsíci červen 2020 již ale měřítko [Prodeje PY (skryté budoucí)]  vrací menší hodnotu než měřítko [Prodeje] v měsíci červen 2019, protože v tomto období již  pracujeme s neúplným měsícem.

Nyní již tedy dokážeme vytvořit "spravedlivé" porovnání v otevřeném časovém období. Díky tomu si můžeme vytvořit další, již velice jednoduché výpočty, ve kterých budeme přímo porovnávat rozdíly mezi prodeji ve vybraných časových úsecích v porovnání s prodeji za stejné období v přechozích letech.

Meziroční srovnání prodejů (YOY, YOY%)

K meziročnímu porovnání prodejů můžeme použít dříve vytvořená měřítka. Meziroční porovnání prodejů v absolutních hodnotách vytvoříme jednoduše odečtením hodnoty prodejů v aktuálním roce od hodnot prodejů v předchozím roce.

Měřítko:

Prodeje YOY absolutně =

[Prodeje] - [Prodeje PY (skryté budoucí)]

Výpočet procentuálního rozdílu mezi prodeji v aktuálním roce v porovnání s předchozím rokem můžeme použít následující výpočet.

Měřítko:

YOY % =

DIVIDE

(

[Prodeje] - [Prodeje PY (skryté budoucí)],

[Prodeje PY (skryté budoucí)]

)

Měřítku [YOY %] nastavíme formát procenta. Obě nová měřítka můžeme opět vložit do vizuálu Matrix.

Na obrázku výše můžeme například vidět, že prodeje v roce 2019 byly o 40,56 % vyšší, než v roce 2018.

Prodeje v předchozím čtvrtletí (PQ)

K získání hodnot prodejů za předcházející čtvrtletí můžeme použít podobný postup, jako u výpočtu hodnot prodejů za předcházející rok. Jediný rozdíl bude v třetím argumentu funkce DATEADD(), kde jako interval zvolíme možnost QUARTER. Níže proto uvádím pouze definici měřítek, bez dalšího komentáře.

Měřítko:

Prodeje PQ =

CALCULATE

(

[Prodeje],

DATEADD('Date'[Date], -1, QUARTER)

)

Měřítko:

Prodeje PQ (skryté budoucí) =

VAR MaxDate = DATE(2020,6,15)

--VAR MaxDate = TODAY()

--VAR MaxDate = TODAY() - 1

--VAR MaxDate = CALCULATE(MAX(Sales[Order Date]),REMOVEFILTERS())

VAR Vypocet =

CALCULATE

(

[Prodeje],

CALCULATETABLE

(

DATEADD('Date'[Date], -1, QUARTER),

FILTER

(

VALUES('Date'[Date]),

'Date'[Date] <= MaxDate

)

)

)

RETURN

Vypocet

Obě nová měřítka si opět můžeme zobrazit Power BI vizuálu.

Výsledky měřítek [Prodeje PQ] a [Prodeje PQ (skryté budoucí)] jsou shodné až do druhého čtvrtletí roku 2020. Měřítko [Prodeje PQ (skryté budoucí)] vrací v tomto období nižší hodnotu, protože dochází k tzv. spravedlivému porovnání na základě stejného období, omezeného na poslední dostupný den s prodeji v aktuálním období. 

Mezičtvrtletní srovnání prodejů (QOQ, QOQ%)

Stejně jako u meziročního srovnání, můžeme pro mezičtvrtletní srovnání použít již dříve definovaná měřítka. Postup výpočtu je podobný jako u výpočtu meziročního porovnání. Níže proto uvádím pouze definici měřítek, bez dalšího komentáře.

Měřítko:

Prodeje QOQ absolutně =

[Prodeje] - [Prodeje PQ (skryté budoucí)]

Měřítko:

QOQ % =

DIVIDE

(

[Prodeje] - [Prodeje PQ (skryté budoucí)],

[Prodeje PQ (skryté budoucí)]

)

Nově definovaná měřítka můžeme opět vložit do dříve připraveného reportu v Power BI.

Na obrázku výše můžeme vidět absolutní a procentuální rozdíly mezi prodeji v jednotlivých čtvrtletích.

Prodeje v předchozím měsíci (PM)

K získání hodnot prodejů za předcházející měsíc můžeme použít podobný postup, jako u výpočtu hodnot prodejů za předcházející a předcházející čtvrtletí rok. Jediný rozdíl bude v třetím argumentu funkce DATEADD(), kde jako interval zvolíme možnost MONTH. Níže proto uvádím pouze definici měřítek, bez dalšího komentáře.

Měřítko:

Prodeje PM =

CALCULATE

(

[Prodeje],

DATEADD('Date'[Date], -1, MONTH)

)

Měřítko:

Prodeje PM (skryté budoucí) =

VAR MaxDate = DATE(2020,6,15)

--VAR MaxDate = TODAY()

--VAR MaxDate = TODAY() - 1

--VAR MaxDate = CALCULATE(MAX(Sales[Order Date]),REMOVEFILTERS())

VAR Vypocet =

CALCULATE

(

[Prodeje],

CALCULATETABLE

(

DATEADD('Date'[Date], -1, MONTH),

FILTER

(

VALUES('Date'[Date]),

'Date'[Date] <= MaxDate

)

)

)

RETURN

Vypocet

Obě nově definovaná měřítka můžeme vyhodnotit ve dříve připraveném vizuálu.

Na výše uvedeném obrázku můžeme vidět výsledek nově vytvořených měřítek [Prodeje PM] a [Prodeje PM (skryté budoucí)], včetně jejich rozdílů od června 2020.

Meziměsíční srovnání prodejů (MOM, MOM%)

Stejně jako u meziročního a mezičtvrtletního srovnání, můžeme pro meziměsíční srovnání použít již dříve definovaná měřítka. Postup výpočtu je podobný jako u výpočtu meziročního a mezičtvrtletního porovnání. Níže proto uvádím pouze definici měřítek, bez dalšího komentáře.

Měřítko:

Prodeje MOM absolutně =

[Prodeje] - [Prodeje PM (skryté budoucí)]

Měřítko:

MOM % =

DIVIDE

(

[Prodeje] - [Prodeje PM (skryté budoucí)],

[Prodeje PM (skryté budoucí)]

)

Nově definovaná měřítka opět vložíme pro porovnání do vizuálu Matrix.

Na obrázku výše můžeme vidět absolutní a procentuální rozdíly mezi prodeji v jednotlivých měsících.

Shrnutí

Všechny ukázky v tomto příspěvku mohou sloužit jako vzor pro vytvoření vlastních časových kalkulací. Nejedná se ovšem o jediné možné řešení výše uvedených příkladů. Způsobů, jak dosáhnout stejných výsledků, je obvykle v jazyku DAX více, a jinak tomu není ani u porovnávání prodejů s prodeji za zvolené předcházející období. Další příklady časových kalkulací můžete najít na stránce DAX - Příklady.

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