Tento příspěvek obsahuje časové kalkulace v jazyku DAX založené na ISO týdenním kalendáři. Výsledkem výpočtů jsou hodnoty za stejné období v předchozím ISO roce (PY), v předchozím ISO čtvrtletí (PQ) a v předchozím ISO týdnu (PW). Na základě těchto výpočtů si pak můžeme snadno vytvořit další měřítka, která budou porovnávat hodnoty v aktuálním období s hodnotami za stejné období v předchozím roce (YOY, YOY %), v předchozím čtvrtletí (QOQ, QOQ %) a v předchozím týdnu (WOW, WOW %). Měřítka v tomto příspěvku jsou vytvořená pomocí základních DAX funkcí, protože při práci s ISO týdenním kalendářem se nemůžeme spolehnout na funkce časového měřítka (Time intelligence funkce), které můžeme v současné době používat pouze se standardním Gregoriánským kalendářem. Postup pro vytvoření vlastního ISO týdenního kalendáře, který je použitý v příkladech v tomto příspěvku, včetně všech pomocných sloupců použitých ve výpočtech, můžete najít v samostatném článku pod tímto odkazem. Power BI soubor s vyřešenými příklady je dostupný ke stažení níže pod tímto příspěvkem.
Prodeje v předchozím období s ISO týdenním kalendářem v jazyku DAX a v Power BI
V příkladech uvedených v tomto příspěvku budeme pracovat s ISO roky, ISO čtvrtletními a ISO týdny z kalendářní tabulky. Ve výpočtech budeme používat měřítko [Prodeje], které vrací sumu za prodané produkty v aktuálním kontextu vyhodnocení a má následující definici.
Měřítko:
Měřítko [Prodeje] může být nahrazeno jakýmkoliv jiným aditivním měřítkem, tedy měřítkem, které můžeme sčítat napříč časovými obdobími. Pokud měřítko [Prodeje] vložíme do vizuálu Matrix, spolu s ISO roky, ISO čtvrtletími, ISO týdny a dny, výchozí vizuál v Power BI reportu může vypadat například následovně.
Pozn.: PY – Previous Year, PQ – Previous Quarter, PW – Previous Week, YOY – Year Over Year, QOQ – Quarter Over Quarter, WOW – Week Over Week.
Při tvorbě měřítka zobrazujícího prodeje v předchozím roce (PY) a měřítka zobrazujícího prodeje v předchozím čtvrtletí (PQ) narazíme na komplikaci, která spočívá v rozdílném počtu týdnů v některých ISO letech. Před vytvořením měřítek, která budou porovnávat prodeje v aktuálním období s prodeji v předcházejícím období, ať už půjde o absolutní (YOY) nebo relativní porovnání (YOY %), si ještě ukážeme jednu z možností, jak zahrnout do výpočtů také 53 týden právě v přestupných ISO letech.
Pozn.: ISO týdenní kalendář nezná termín přestupný rok. Jedná se o neformální vyjádření ISO roku, který má 53 týdnů, namísto standardních 52 týdnů.
Prodeje v minulém roce (PY)
Měřítko prodejů za předcházející rok bude vracet sumu za prodané produkty v období, které bude shodné s aktuálně vybraným obdobím, pouze bude posunuté o jeden rok zpět. Ve výpočtu proto budeme pracovat s aktuálně vybraným rokem, který bude ve filtru posunut o jeden rok zpět. Dále budeme muset přidat do filtru dny v roce, které jsou vybrané v aktuálním kontextu vyhodnocení, a ty pak použít pro výpočet prodejů v roce předcházejícím. K tomuto účelu je v ISO týdenním kalendáři k dispozici sloupec 'Date'[ISO den v roce], který jednoduše vložíme do funkce VALUES(). Kombinace filtru nastaveného na předcházející rok a filtru vybraných dnů v roce dosáhneme požadovaného výsledku. Samotný výpočet může vypadat následovně.
Měřítko:
Nové měřítko si můžeme vložit do vizuálu a zobrazit si jeho výsledky.
Začněme přestupným ISO rokem, který nastává každých 5 let a v použitém modelu připadá na ISO rok 2020. Na obrázku níže jsou zvýrazněné dvě buňky, které by měly obsahovat stejné hodnoty, ale jsou rozdílné.
Prodeje v ISO roce 2020 dosáhly částky 39 379 122,04. Měřítko [Prodeje PY (jednoduché)] ale vrací nižší částku, protože ve výpočtu není zahrnut 53 týden, který je v ISO roce 2020 navíc, v porovnání s ISO rokem 2021. Měřítko [Prodeje PY (jednoduché)] obsahuje ve funkci CALCULATE() dva filtry. První filtr načte v roce 2021 aktuální ISO rok, ze kterého jednoduše odečítáme číslo 1 a dostaneme tak rok 2020. Druhý filtr ve funkci CALCULATE() obsahuje všechny dny ve vybraném období. V řádku s ISO rokem 2021 ve vizuálu na obrázku výše jsou to všechny dny v ISO roce 2021, tedy dny s pořadovými čísly 1 až 364. Výsledkem filtru ve funkci CALCULATE() je proto rok 2020 (2021 – 1), a ISO dny 1 až 364. ISO rok 2020 ale obsahuje 371 dnů, protože se jedná o přestupný rok. Rozdíl ve zvýrazněných buňkách na obrázku výše je dán právě těmito chybějícími 7 dny.
Řešením by v tomto případě mohlo být vracet na úrovni let hodnotu prodejů za celý předchozí rok, bez ohledu na filtr dnů. Museli bychom ale v měřítku vyhodnotit, jestli se při výpočtu nacházíme na úrovni let a jestli se jedná o rok, ve kterém máme k dispozici prodeje za všechny dny v roce, tedy jestli nepracujeme s otevřeným časovým obdobím. To samé bychom museli ověřovat také na úrovni ISO čtvrtletí, protože čtvrté čtvrtletí v přestupném ISO roce obsahuje také jeden týden navíc.
Druhou možností (nikoliv jedinou) je přiřadit všem dnům v týdnu číslo 53 v přestupném ISO roce číslo dne, které je dostupné také v běžném ISO roce, tedy v roce, který není přestupný. Jinak řečeno, v datumové tabulce použijeme pomocný sloupec, který obsahuje pro 365 až 371 den v přestupných letech číslo 364.
Jak můžeme vidět na obrázku výše, použitím sloupce 'Date'[ISO den v roce (do 364)], namísto původního sloupce s pořadovými čísly dnů v ISO roce, dostaneme správný výsledek na úrovni let i na úrovni čtvrtletí i pro přestupnů ISO rok. Pokud se ale podíváme na následující obrázek, zjistíme že na úrovni týdnů a dnů již měřítko nevrací očekávané výsledky.
Na obrázku výše můžeme vidět, že výsledkem měřítka [Prodeje PY (do 364 – špatně)] v řádku s „ISO T52-R2021“ je hodnota, která odpovídá prodejům v předchozím roce za dva týdny, týdny s pořadovým číslem 52 a 53. Stejný problém můžeme vidět také na úrovni dnů.
Zvýrazněná buňka na obrázku výše zobrazuje výrazně vyšší hodnotu, než je hodnota prodejů v ostatních dnech zobrazeného týdne. 2. ledna 2022 je den s pořadovým číslem 364. Všechny dny v minulém roce s pořadovým čísle 364 jsou proto přiřazeny k tomuto datu. Zvýrazněná buňka obsahuje prodeje za posledních 8 dní v ISO roce 2020, právě díky použitému pomocnému sloupci 'Date'[ISO den v roce (do 364)], který obsahuje číslo 364 také pro dny s pořadovým číslem 365 až 371 v přestuném ISO roce.
Finální výpočet proto bude obsahovat ještě podmínku, na základě které vybereme ze dvou typů výpočtů. V případě vyhodnocení měřítka na úrovni dnů a týdnů použijeme pro načtení dnů v roce sloupec 'Date'[ISO den v roce]. Na úrovni čtvrtletí a let pak použijeme pro načtení dnů v roce sloupce 'Date'[ISO den v roce(do 364)].
Než se dostaneme k finální definici měřítka prodejů v předchozím roce, zbývá ještě vyřešit problém otevřeného časového období. Problém spravedlivého porovnání v otevřeném časovém období je typický jev, který nastává vždy v aktuálním roce, ve kterém ještě nemáme k dispozici prodeje za celé období (celý rok). Abychom mohli spravedlivě porovnávat prodeje v aktuálním roce, který ještě není uzavřený, potřebujeme prodeje v aktuálním roce porovnávat pouze s prodeji v minulém roce, které budou obsahovat stejný počet dnů, pro které máme údaje také v roce aktuálním. Ve výpočtu proto budeme načítat poslední den s prodeji, a ve filtru dnů v minulém roce pak použijeme pouze ty dny, které jsou menší nebo rovno než je poslední dostupný den s prodeji v roce aktuálním. Jinak řečeno, v použitém modelu jsou dostupné prodeje pouze do 17. 7. 2022. Pokud budeme porovnávat prodeje v roce 2022 s prodeji v roce 2021, budeme v roce 2021 brát v úvahu pouze prodeje za dny do 17. 7. 2021, včetně. Pokud bychom pro porovnání použili prodeje za celý rok 2021, porovnání by nebylo spravedlivé, protože v aktuálním roce (2022) ještě nemáme k dispozici kompletní data za celý rok.
Řešení je v tomto případě úplně stejné, jako u standartních časových kalkulací založených na Time intelligence funkcích. Vybrané dny dostupné v aktuálním kontextu vyhodnocení omezíme pouze na ty dny, které jsou menší nebo rovny poslednímu dnu, ke kterému máme k dispozici data o prodejích.
Celý výpočet může vypadat například následovně.
Měřítko:
Celý výpočet může vypadat na první pohled složitěji, což je dáno především opakováním dvou podobných výpočtů, jednou pro roky a čtvrtletí, a podruhé pro dny a týdny. Logika výpočtu je ale jednoduchá a výsledkem je měřítko, které vrací očekávané hodnoty v každé buňce vizuálu.
Porovnání prodejů s prodeji v minulém roce (YOY, YOY %)
K porovnání aktuálních prodejů s prodeji v minulém roce v absolutních hodnotách (YOY) stačí odečíst hodnotu měřítka prodejů v předchozím roce od hodnoty měřítka prodejů v aktuálním roce. Celý výpočet, doplnění o ověření zda měřítka vrací v aktuálním kontextu hodnoty, může vypadat následovně.
Měřítko:
Následně můžeme doplnit model o měřítko s relativním porovnáním prodejů napříč roky (YOY %).
Měřítko:
Pokud nová měřítka vložíme do dříve použitého vizuálu, výsledek může vypadat následovně.
Prodeje v minulém čtvrtletí (PQ)
Princip výpočtu prodejů v předchozím čtvrtletí je velmi podobný výpočtu prodejů v předchozím roce. Rozdíl bude pouze v použitých sloupcích z datumové tabulky. Pro posun do předchozího čtvrtletí budeme používat sloupec s pořadovým číslem čtvrtletích, a pro načtení dnů dostupných v aktuálním kontextu vyhodnocení budeme používat pořadí dnů ve čtvrtletí. Stejně jako v případě výpočtu prodejů v předchozím roce, i v případě prodejů v přechozím čtvrtletí musíme brát v úvahu přestupné ISO roky. V přestupném ISO roce obsahuje čtvrté čtvrtletí 98 dní, namísto standardních 91 dní. Na úrovni čtvrtletí proto budeme používat sloupec se dny ve čtvrtletí, který bude mít pro 92 až 98 den ve čtvrtletí hodnotu 91. K tomuto účelu je v datumové tabulce k dispozici pomocný sloupec 'Date'[ISO den ve čtvrtletí (do 91)]. Na úrovni týdnů a dnů pak použijeme standardní sloupec s pořadím dnů ve čtvrtletí, který najdeme v datumové tabulce pod názvem 'Date'[ISO den ve čtvrtletí]. Výpočet prodejů v předchozím čtvrtletí tak může vypadat následovně.
Měřítko:
Nové měřítko si můžeme vložit do původního vizuálu.
Na základě měřítek [Prodeje] a [Prodeje PQ] si můžeme vytvořit nová měřítka, ve kterých budeme přímo porovnávat prodeje v aktuálním čtvrtletí s prodeji v předcházejícím čtvrtletí jak v absolutních (QOQ), tak v relativních hodnotách (QOQ %).
Porovnání prodejů s prodeji v minulém čtvrtletí (QOQ, QOQ %)
Měřítko porovnávající aktuální prodeje s prodeji v předcházejícím čtvrtletí v absolutních hodnotách následuje stejnou logiku, jako v případě porovnání prodejů na úrovni let, a jeho definice vypadá následovně.
Měřítko:
Měřítko porovnávající prodeje s prodeji v předcházejícím období v relativních hodnotách vypadá následovně.
Měřítko:
Nová měřítka můžeme vložit do vizuálu Matrix, a porovnat si tak výsledky.
Prodeje v minulém týdnu (PW)
Týdny v ISO týdenním kalendáři mají vždy 7 dní. Proto již nemusíme při porovnání aktuálních prodejů s prodeji v minulém týdnu používat různé varianty výpočtů podle toho, zda bude měřítko vyhodnoceno na úrovní týdnů nebo dnů. K posunu do předchozího týdne použijeme sloupec s pořadovým číslem týdnů napříč roky, od kterého jednoduše odečteme číslo jedna. Pro načtení vybraných dnů v akutálním kontextu pak použijeme sloupec s pořadovým číslem dne v týdnu. Celý výpočet může vypadat následovně.
Měřítko:
Měřítko [Prodeje PW] vrací sumu za prodané produkty v minulém týdnu, a to buď na úrovni týdnů, nebo na úrovni dnů.
Porovnání prodejů s prodeji v minulém týdnu (WOW, WOW %)
Měřítko porovnávající prodeje v aktuálním týdnu s prodeji v minulém týdnu v absolutních hodnotách má následující definici.
Měřítko:
Měřítko porovnávající prodeje v aktuálním týdnu s prodeji v minulém týdnu v relativních hodnotách má následující definici.
Měřítko:
Pokud nová měřítka použijeme ve dříve vytvořeném vizuálu, výsledek může vypadat následovně.
Další příklady, včetně časových kalkulací s použitím vestavěných Time intelligence funkcí, které jsou určené pro práci se standardním kalendářem, můžete najít na stránce DAX příklady.
Komentáře
Okomentovat