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 v kontingenčke zobrazí štatistika aj za rok 2005.

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 🙂