Časové kalkulace v DAX problém otevřeného období
V květnu jsem měl na téma časových kalkulací přednášku na Techedu v Praze. Bohužel jsem s blogem prokrastinoval a dostávám se ke článku až nyní. Nicméně lepší pozdě, než nikdy :) Slíbil jsem, že na téma napíši související blog post. Samostatným časovým kalkulacím jsem se věnoval v následujícím článku http://www.neoral.cz/2015/06/powerpivot-dax-casove-funkce-1-cast.html Takže nyní přidám jen část o pokročilejších metodách. Konkrétně se bude jednat o práci s otevřeným obdobím. V následujícím blogu popíšu, jak se vypořádat s problémem, když se v čase mění den vůči kterému chci dělat meziroční srovnání.
Problém otevřeného období
Opět použiji datový model vytvořený nad datovým skladem Adventure Works. Soubor v Power Pivotu si můžete stáhnout zde: https://drive.google.com/open?id=0B9ZohZ1CALKZdTNxbTdKOE5XbTQ
Koukněme se na následující kontingenční tabulku
Internet sales je skutečnost, Internet LY minulý rok (last year), Internet VLY variance proti loňskému roku absolutní, hned vedle v procentech.
Rok 2008 je posledním rokem, který obsahuje data a neobsahuje data za kompletní rok. Dnes je 29.6 takže taky máme jen data za půl roku. No a je nesmysl na roční úrovni porovnávat uzavřený rok 2016 s půlkou roku 2017. Něco podobného právě dělá můj aktuální Excelový model.
Chtěl bych porovnávat jen proporciální část prodejů roku vztaženou ke dnešnímu dni.
V tabulce internet sales si můžu pomoci počítaným sloupcem, kde jednoduše skryji prodeje, které jsou po 29.6.
DAXový vzorec by vypadal následovně
=
IF (
VALUE ( MID ( [OrderDateKey], 5, 2 ) ) > MONTH ( TODAY () )
|| (
VALUE ( MID ( [OrderDateKey], 5, 2 ) ) = MONTH ( TODAY () )
&& VALUE ( RIGHT ( [OrderDateKey], 2 ) ) > DAY ( TODAY () )
),
BLANK (),
[SalesAmount]
)
OrderDateKey je datum ve formátu RRRRMMDD, funkcí mid řežu příslušnou část, kterou chci porovnat s měsícem/dnem z dneška.
Textově rozloženo VALUE ( MID ( [OrderDateKey], 5, 2 ) ) > MONTH ( TODAY () )
říká pokud je měsíc objednávky vyšší, než měsíc dnešního datumu
Nebo ||
(
VALUE ( MID ( [OrderDateKey], 5, 2 ) ) = MONTH ( TODAY () ) –měsíc je stejný
&& VALUE ( RIGHT ( [OrderDateKey], 2 ) ) > DAY ( TODAY () ) – den v měsíci je větší
)
vrátit prázdnou buňku, jinak vrátit skutečnost.
Pokud nad tímto vytvořím měřítko Internet Sales TD (To date /ke dnešku) jako
=SUM(‘FactInternetSales’[Sales TD detail])
A příslušné variance které umístím do následující kontingenční tabulky dostanu následující výstup
Sekce 1 mi zobrazuje prodeje prodeje za celé uzavřené období.
Sekce 2 jsou prodeje po dnešek, tedy 29.6.
Sekce 3 variance proti předchozímu roku v absolutních číslech za celé uzavřené období (VLY) a srovnání po dnešek VLY TD. Zde je to právě velmi zajímavé. Když kouknete na rok 2008, je rozdíl, jestli -20160 proti loňsku (způsobeno, neúplným rokem 2008). Nebo srovnávám srovnatelné se srovnatelným a zjišťuji, že jsem vlastně 6,7 milionu v plusu za stejné období od ledna do konce června.
Sekce 4 stejná jako 3, ale vyjádřená v procentech. Místo toho, aby firma byla -0,2 procenta ve ztrátě, je vlastně 221% v plusu a to je hodně velký rozdíl :)
Závěr
Pro potřeby analýzy je nutno srovnávat srovnatelné se srovnatelným. Nikoliv celý minulý rok s aktuálním rokem otevřeným. Logika by se dala aplikovat i na nižší úrovně časové hierarchie jako kvartály, měsíce, týdny dokonce i dny. V dnešním článku jsem Vám ukázal jak by se dalo vypořádat s tímto problémem pomocí počítaného sloupce. Dalo by se to vyřešit i přímo v Measure. Šlo mi v tuto fázi ale hlavně o onu myšlenku :) Příště se vrhnu na jiný problém při práci s časem a sice proměnnost vzorce pro meziroční srovnání.