V tomto příspěvku věnovanému časovým kalkulacím si ukážeme výpočet klouzavých ročních prodejů (MAT) a všech souvisejících výpočtů. Jako související výpočty můžeme uvažovat například klouzavé roční prodeje v předchozím roce (MAT PY), rozdíl mezi klouzavými ročními prodeji v aktuálním a předchozím roce v absolutních hodnotách (MAT YOY) anebo rozdíl mezi aktuálním a předchozím rokem vyjádřený v relativních hodnotách (MAT YOY %).
Poznámka:
MAT - Moving Annual Total - klouzavý roční úhrn,
PY - Previous Year - předchozí rok,
YOY - Year Over Year - meziroční.
K tomuto tématu je k dispozici také video:
Výpočet klouzavých ročních prodejů spočívá v zobrazení součtu prodejů za posledních dvanáct měsíců v aktuálním období. Pokud bychom jako aktuální období brali například měsíc únor 2024, tak v tomto měsíci budou klouzavé roční prodeje představovat prodeje za období od začátku března 2023 do konce února 2024. Pro měsíc březen 2024 pak použijeme období od dubna 2023 do března 2024 atd. Slovo klouzavé proto vyjadřuje kontinuální posun dvanáctiměsíčních období pro každý atribut zobrazený v řádcích nebo na osách vizuálů.
Roční klouzavé součty se obvykle používají pro vykreslení trendů v grafech, a to buď na úrovni měsíců nebo na nižších úrovních. Klouzavé roční prodeje můžeme také porovnávat s klouzavými ročními prodeji za stejné období v předchozím roce, a to ať už absolutních hodnotách, nebo v relativních hodnotách.
Všechny tyto výpočty si vytvoříme ve cvičném Power BI souboru Adventure Works DW 2020.pbix. V tomto souboru je navíc měřítko [Prodeje], které má následující definici.
Měřítko:
Pokud měřítko [Prodeje] vložíme do vizuálu matice spolu s měsíci a roky z kalendářní tabulky v řádcích, výchozí vizuál se kterým budeme pracovat může vypadat například následovně.
Měřítko [Prodeje] vrací sumu za prodeje produktů v aktuální měsíci nebo roce. V následující kapitolách si postupně vytvoříme výpočet klouzavých ročních prodejů, výpočet klouzavých ročních prodejů za předchozí rok a výpočet meziročních rozdílů v absolutních a relativních hodnotách.
Výpočet klouzavých ročních prodejů (MAT)
Pro vytvoření klouzavých ročních prodejů budeme potřebovat dostat do filtru funkce CALCULATE() období, které bude zahrnovat v každé buňce vizuálu dvanáct měsíců. Toto období bude začínat dnem, který získáme odečtením dvanácti měsíců od posledního dne dostupného v aktuálním kontextu vyhodnocení a končit bude právě posledním dnem dostupným v aktuálním kontextu vyhodnocení. K tomuto účelu můžeme použít Time intelligence funkci DATESINPERIOD().
Ve funkci DATESINPERIOD() definujeme poslední den v aktuálním kontextu vyhodnocení jednoduše pomocí funkce MAX(). Následně v dalších argumentech zadáme o kolik intervalů se chceme od tohoto dne posunout a jaký chceme použít typ intervalu. V našem příkladu můžeme zadat buď mínus dvanáct měsíců nebo mínus jeden rok. Před vypsáním výsledku si ještě ověříme pomocí funkce IF() zda jsou v aktuálním kontextu vyhodnocení dostupné záznamy v tabulce 'Sales' a zda je měřítko vyhodnoceno v kontextu pouze jednoho měsíce, abychom výsledky nezobrazovaly na vyšších úrovních než je úroveň měsíců, protože například na úrovni let už mohou být výsledky tohoto výpočtu za určitých okolností obtížně interpretovatelné. Celý takto popsaný výpočet může vypadat například následovně.
Měřítko:
Nové měřítko si můžeme vložit do vizuálu Matice a zobrazit si výsledky.
Pokud se zaměříme například na měsíc březen 2020, tak v tomto měsíci vrací měřítko [Prodeje MAT] hodnotu 47 860 687,25. Tato částka odpovídá součtu hodnot, které vrací měřítko [Prodeje] za období od dubna 2019 do března 2020 včetně. Obdobně bychom mohli interpretovat hodnoty měřítka [Prodeje MAT] pro jakýkoliv jiný měsíc v řádcích vizuálu.
Než se přesuneme k dalším variacím výpočtů, podívejme se ještě na první řádky vizuálu Matice a na hodnoty měřítka [Prodeje MAT] v těchto řádcích.
Měřítko [Prodeje MAT] vrací pro měsíc červenec 2017 hodnotu prodejů pouze za tento konkrétní měsíc. V měsíci srpen 2017 pak vrací měřítko [Prodeje MAT] prodeje za měsíce červenec a srpen 2017. Prodeje za dvanáct měsíců pak měřítko [Prodeje MAT] vrací až v měsíci červen 2018, což je první měsíc, ve kterém máme v použitém modelu k dispozici historii prodejů za dvanáct měsíců.
Pokud bychom nechtěli zobrazovat hodnoty měřítka [Prodeje MAT] v obdobích, ve kterých ještě není k dispozici dostatečná historie, můžeme použít následující variantu výpočtu.
Měřítko:
V měřítku [Prodeje MAT (2)] je výsledek funkce DATESINPERIOD() uložen v samostatné proměnné nazvané TabulkaDoFiltru. Tato proměnná je pak dále ve výpočtu použita na dvou místech. V prvním případě pro zafiltrování měsíců s prodeji v proměnné PocetMesicuSProdeji, a v druhém případě v proměnné Vypocet před vyhodnocením měřítka [Prodeje]. Další změna oproti původnímu výpočtu je v proměnné Vysledek, kde je navíc v prvním argumentu funkce IF() podmínka, ve které ověřujeme zda proměnná PocetMesicuSProdeji vrací hodnotu 12. Jinak řečeno ve funkci IF() navíc ověřujeme, zda máme pro výpočet k dispozici dvanáct měsíců s prodeji. Pokud by tato podmínka nebyla splněna, výsledkem měřítka [Prodeje MAT(2)] bude prázdná hodnota BLANK. Měřítko [Prodeje MAT (2)] již tedy nebude vracet výsledky pro měsíce ve kterých nemáme k dispozici dostatečnou historii pro výpočet klouzavých ročních prodejů.
Rozdíl mezi měřítkem [Prodeje MAT] a měřítkem [Prodeje MAT (2)] je tedy pouze v tom, že hodnoty měřítka [Prodeje MAT (2)] se zobrazují až v případě kdy máme k dispozici historii prodejů za dvanáct předchozích měsíců. Tento efekt může mít přínos zejména v grafech, ve kterých se obvykle klouzavé roční součty zobrazují z důvodů analýzy trendů, anebo při porovnávání klouzavých ročních prodejů v aktuálním roce s rokem předchozím, kde by mohli být meziroční rozdíly v prvních obdobích prodejů zkresleny z důvodu nedostatečné historie.
Výpočet klouzavých ročních prodejů za předchozí rok (MAT PY)
Jak už jsme si řekli v úvodu, výkonnost za posledních dvanáct měsíců se často porovnává s výkonností za stejné období v předchozím roce. Z pohledu jazyka DAX můžeme klouzavé roční prodeje za předchozí rok získat více různými způsoby. Jednou z možností je použít výpočet ročních klouzavých prodejů v aktuálním roce a před vyhodnocením tohoto výpočtu upravit kontext filtru tak, že všechny dny v aktuálním kontextu posuneme o jeden rok zpět pomocí funkce DATEADD().
Měřítko:
Nové měřítko si můžeme vložit do vizuálu a zobrazit si výsledky.
Měřítko [Prodeje MAT PY] nyní vrací v každé buňce vizuálu stejné hodnoty jako měřítko [Prodeje MAT] v předchozím roce. Úplně stejně jako v případě měřítka [Prodeje MAT PY] i zde si můžeme vytvořit druhou variantu tohoto výpočtu, která bude vracet výsledky až v případě kdy je k dispozici historie prodejů za dvanáct měsíců.
Měřítko:
Pokud si novou verzi výpočtu vložíme do původního vizuálu tak výsledek bude vypadat následovně.
Jak je nyní možné vidět na obrázku výše, nová verze měřítka vrací klouzavé roční prodeje v předchozím roce až v případě, kdy je máme k dispozici požadovaných dvanáct měsíců s prodeji. Na základě měřítek s klouzavými ročními prodeji v aktuálním a předchozím roce již můžeme jednoduchými výpočty vytvořit další měřítka, která budou porovnávat meziroční výkonnost ať už v absolutních nebo v relativních hodnotách.
Meziroční porovnání ročních klouzavých prodejů (MAT YOY a MAT YOY %)
Pro meziroční porovnání klouzavých ročních prodejů stačí od klouzavých ročních prodejů za předchozí rok odečíst klouzavé roční prodeje v aktuálním období. Výsledek tohoto výpočtu budeme zobrazovat pouze v případě že máme v aktuálním kontextu k dispozici obě hodnoty potřebné pro výpočet, což můžeme ověřit jednoduše v prvním argumentu funkce IF() například následujícím způsobem.
Měřítko:
Pokud bychom chtěli pro výpočet meziročních rozdílů použít druhé varianty výpočtů, které se zobrazují pouze v situacích kdy je k dispozici dostatečná historie pro výpočet ročních klouzavých prodejů, stačí změnit definici prvních dvou proměnných následujícím způsobem.
Měřítko:
Pro výpočet rozdílu v relativních hodnotách pak můžeme opět definovat dvě varianty výpočtu.
Měřítka:
Pokud si nyní do vizuálu vložíme všechna měřítka bez ošetření dostupnosti dostatečné historie pro výpočet dvanáctiměsíčních ročních prodejů, výsledky mohou být v prvních měsících překvapivé.
Na druhou stranu varianty výpočtů, ve kterých máme ošetřeno aby se zobrazovali pouze v obdobích, pro která máme dostatečnou historii, tento problém již nevidíme.
Další příklady, včetně jiných variant Time intelligence výpočtů, můžete najít na stránce DAX příklady. Všechna videa jsou pak k dispozici na mém Youtubovém kanále pod tímto odkazem.
Komentáře
Okomentovat