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:

1. do oblasti hodnôt dáme merítko Obrat z tabuľky Objednávky,
2. 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:

1. do oblasti hodnôt dáme merítko Obrat z tabuľky Objednávky,
2. 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/deň”, č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 🙂