V niektorých prípadoch má firma odlišný fiškálny rok, resp. účtovný rok oproti kalendárnemu roku. Dôvodov je viacero, ale tie teraz nie sú podstatné. Podstatné je to, že aj v takýchto prípadoch potrebujete vypočítať ukazovatele za fiškálny rok, ktoré ako začiatok roka berú iný dátum ako 1.1.. A pomocou jazyka DAX je to celkom jednoduché.
Riešenia si opäť ukážeme na našom vzorovom súbore PowerPivotu, resp. Power BI. Vytvoríme si takúto kontingenčku:
- do oblasti hodnôt dáme merítko Obrat z tabuľky Objednávky,
- do oblasti riadkov dáme stĺpec CalendarYear z tabuľky Čas.
Výsledná kontingenčka bude vyzerať takto:
Zobrazuje ukazovateľ Obrat za každý kalendárny rok. A teraz by sme v takejto kontingenčke chceli ukázať ukazovatele za fiškálny rok.
Najjednoduchšie riešenie
Najjednoduchším riešením je použiť klasickú časovú tabuľku, a dogenerovať si do nej stĺpec s fiškálnym rokom, ak ho tam teda už nemáte. V našom vzorovom súbore už máme v tabuľke Čas taký stĺpec – je to stĺpec FiscalYear. Je vygenerovaný tak, že fiškálny rok začína 1.7. v prechádzajúcom kalendárnom roku. Ak by sme si taký stĺpec chceli vytvoriť sami, tak to vieme spraviť napríklad cez takýto vypočítaný stĺpec:
Fiškálny rok = IF('Čas'[DateKey] < DATE('Čas'[CalendarYear]; 7; 1); 'Čas'[CalendarYear]; 'Čas'[CalendarYear] + 1)
V skratke – je to podmienka, ktorá vráti číslo kalendárneho roku, ak je dátum v danom roku menší ako 1.7., inak vráti nasledujúce číslo kalendárneho roku.
Potom už stačí spraviť iba jednoduchú kontingenčku, ktorá nám zobrazí Obrat po fiškálnych rokoch. Vytvoríme ju teda takto:
- do oblasti hodnôt dáme merítko Obrat z tabuľky Objednávky,
- do oblasti riadkov dáme stĺpce FiscalYear z tabuľky Čas (resp. stĺpec “Fiškálny rok”, ktorý sme práve vytvorili).
Výsledná kontingenčka bude vyzerať takto:
Potiaľto fajn, ale čo keby sme teraz chceli zobraziť v jednej kontingenčke vedľa seba Obrat, za kalendárny aj fiškálny rok súčasne? To už budeme musieť ísť cez DAX. Využijeme pri tom však stĺpec s fiškálnym rokom vyššie.
Ukazovatele za fiškálny rok cez DAX
Vrátime sa teda naspäť k pôvodnej kontingenčke so stĺpcom CalendarYear. A teraz by sme chceli zobraziť vedľa Obratu aj obrat za fiškálny rok s rovnakým číslom. To bude hračka. Použijeme základné vlastnosti kontextu výpočtu, rušenie filtrov a indický zápis filtrov, a vytvoríme si takéto merítko:
Obrat za fiškálny rok := VAR rok = SELECTEDVALUE('Čas'[CalendarYear]) RETURN CALCULATE([Obrat]; ALL('Čas'[CalendarYear]); 'Čas'[FiscalYear] = rok)
Po dosadení do kontingenčky to bude vyzerať takto:
Sú to rovnaké čísla, aké ste videli v kontingenčke vyššie. Nevidíte tam iba obrat za fiškálny rok 2005, pretože na osi kontingenčky je použitý CalendarYear, a ten je v tabuľke Čas vygenerovaný iba po rok 2004. Keď si tabuľku dogenerujete až po rok 2005, tak sa zobrazí štatistika za iba typy rokov 2005 aj v kontingenčke.
Druhý “problém” je to, že to neukazuje celkový súčet obratu za fiškálne roky. Použitím techniky vizuálneho súčtu opravíme veľmi rýchlo aj to. Vytvoríme ďalšie merítko:
Obrat za fiškálny rok s vizuálnym súčtom := SUMX(VALUES('Čas'[CalendarYear]); [Obrat za fiškálny rok])
…a po dosadení do kontingenčky aj tento problém prestane existovať:
Kumulatívne ukazovatele za fiškálny rok cez DAX
Čo však s kumulatívnymi ukazovateľmi za fiškálny rok? Napr. obrat či počet objednávok od začiatku fiškálneho roka? A samozrejme zobrazený v tej istej kontingenčke?
Najjednoduchšie je to spraviť cez funkcie Time Intelligence. V tomto prípade cez funkciu DATESYTD, ktorá má túto syntax:
DATESYTD(dátumovýStĺpec; koniecFiškálnehoRoka)
…kde dátumovýStĺpec je dátumový stĺpec z časovej tabuľky, a koniecFiškálnehoRoka je nepovinný parameter, ktorý určuje koniec fiškálneho roka.
Ten druhý parameter je trochu záludný. Keď sa pozriete do oficiálnej dokumentácie k tejto funkcii, tak tam je napísané, že tam treba zadať dátum konca fiškálneho roka. Navyše ako reťazec, bez roka, a podľa regionálnych nastavení vášho počítača. A ako si viete domyslieť, tak tá posledná podmienka je úplne skvelá. Lepšie povedané, nefunkčná na väčšine počítačov, napríklad so slovenským formátom dátumu. Veď kto by také niečo v 21. storočí čakal, že?
Takže po troche mágie a pátrania som zistil, že tam idú zadať dátumy v dvoch formátoch:
1) americký dátum vo formáte “mesiac/rok”, čiže v našom prípade “6/30”,
2) nedokumentovaný dátumový formát, pochádzajúci priamo od programátorov PowerPivotu, vo formáte “YYYY-MM-DD”. Pričom YYYY je hocijaký rok a ignoruje sa. V našom prípade tam teda zadáme napríklad “2003-06-30”.
Vytvoríme si teda tieto 2 merítka – jedno pre obrat od začiatku kalendárneho roka, a druhé pomocou mágie vyššie pre obrat od začiatku nášho fiškálneho roka:
Obrat od začiatku roka := CALCULATE([Obrat]; DATESYTD('Čas'[DateKey])) Obrat od začiatku fiškálneho roka := CALCULATE([Obrat]; DATESYTD('Čas'[DateKey]; "2003/06/30"))
Do kontingenčky dosadíme tieto 2 merítka, plus do oblasti riadkov kontingenčky ešte pridáme stĺpec MonthName z tabuľky Čas, aby bolo vidno rozdiel medzi nimi. Výsledok bude vyzerať takto:
Tam si všimnite, že obrat od začiatku fiškálneho roku pokračuje v raste aj cez prelom rokov, až po mesiac July, kedy začína ďalší fiškálny rok, a kedy sa začína kumulovať od začiatku.
A čo v prípade DirectQuery?
A čo v prípade, že nepoužívame režim importu, ale používame režim DirectQuery, v ktorom funkcie Time Intelligence nefungujú?
V takomto prípade môžeme použiť napríklad takýto vzorec:
Obrat od začiatku fiškálneho roka DQ := VAR rok = SELECTEDVALUE('Čas'[FiscalYear]) VAR poslednyDatum = MAX('Čas'[DateKey]) RETURN CALCULATE([Obrat]; ALL('Čas'); 'Čas'[FiscalYear] = rok; 'Čas'[DateKey] <= poslednyDatum )
Po dosadení do kontingenčky dostaneme rovnaký výsledok ako v prechádzajúcom prípade, tentokrát fungujúci aj v režime DirectQuery:
S tým bonusom, že aj čísla v medzisúčtoch sú rovnaké, a malo by to fungovať na všetkých úrovniach – čiže napr. rokov, štvrťrokov, mesiacov, týždňov či dní. Princíp je jednoduchý – upravili sme kontext výpočtu tak, že sme najprv funkciou ALL zrušili všetky časové filtre, a potom sme presunuli pôvodný filter z kalendárneho roka na fiškálny rok. Plus nastavili filter na dátumy tak, že sme vyfiltrovali všetko pred maximálnym dátumom v pôvodnom kontexte výpočtu. Po troche uvažovania by vám to malo dôjsť tiež.
Takto teda vypočítate niektoré ukazovatele za fiškálny rok v jazyku DAX. Vzorce sú rovnaké v Power BI aj v excelovskom PowerPivote, len tam budete potrebovať verziu aspoň 2016. Čo by v dnešnej dobe mal mať snáď už naozaj každý. Ostatné ukazovatele viete vypočítať podobným spôsobom. Stačí si vedieť predstaviť kontext výpočtu, čo všetko v ňom máte, čo z toho a ako treba zmeniť, a riešenie by ste mali mať za pár sekúnd. OK, možno minút, pretože nie všetci sú takí skvelí ako ja 😀 Každopádne, vidíte že to ide aj bez funkcií Time Intelligence, a po troche praxe už takéto ukazovatele budete písať ľavou zadnou 🙂
Autor, tréner a expert na PowerPivot, Power BI a jazyk DAX. Založil som tento web, aby som pomohol dostať PowerPivot a Power BI do širšieho povedomia, a aby som ľuďom ukázal, že aj komplexné analytické problémy idú riešiť jednoducho. Po nociach vzývam Majstra Yodu a tajne plánujem ovládnutie vesmíru.