Jednou z častých požiadaviek pri analýzach v PowerPivote, po tom, čo už máme funkčné základné ukazovatele ako obrat, náklady, či zisk, je otázka – OK, a ako vyzerali tieto ukazovatele pred rokom/mesiacom/štvrťrokom? A zatiaľ čo bežným postupom je vykopírovanie si týchto údajov do reportu z viacerých kontingenčiek, existuje na to jednoduchšie a elegantnejšie riešenie – funkcie Time Intelligence jazyka DAX.Tieto funkcie nám umožňujú pracovať s časom, a zobraziť naše ukazovatele rôzne prepočítané v čase, ako napr.:

1. Obrat v rovnakom období pred rokom,
2. Kumulatívny obrat od začiatku roka po aktuálny dátum (ukazovatele YTD – year-to-date),
3. Kĺzavý priemer obratu v čase (na zistenie a zobrazenie trendu v dátach),
4. Porovnania nárastu/poklesu ukazovateľov voči predchádzajúcim obdobiam (ukazovatele YoY – year-over-year),
5. Analýzu stavu na účtoch alebo na sklade na začiatku vs. na konci sledovaného obdobia.

Použitie takýchto funkcií je jednoduché, avšak takmer všetky vyžadujú jednu zásadnú vec, bez ktorej nebudú fungovať správne – tzv. časovú tabuľku. Časová tabuľka je špeciálna tabuľka v dátovom modeli, ktorá spĺňa nasledujúce podmienky:

1. Je prepojená na analyzované tabuľky,
2. Obsahuje 1 riadok pre každý deň v histórii dát,
3. Obsahuje všetky dátumy pre všetky analyzované roky,
4. Obsahuje aspoň 1 stĺpec dátového typu “Dátum”, ktorý obsahuje plnohodnotný dátum pre každý riadok z bodu 2),
5. (voliteľne) obsahuje ďalšie stĺpce ako “Rok”, “Mesiac”, “Deň”, či “Sviatok”, ktoré sú odvodené z dátumu v danom riadku, a umožňujú nám analyzovať naše údaje podľa rokov, mesiacov, dní, sviatkov, atď.

Takúto tabuľku zvyčajne nájdete v každom dátovom sklade, prípadne si môžete “opajcnúť” tú našu z nášho vzorového súboru, ktorý si môžete stiahnuť tu. Alebo si ju vytvorte v Exceli a pripojte si ju do PowerPivotu ako linkovanú tabuľku. Alebo, ak máte Excel 2016 alebo pracujete v Power BI, tak si ju môžete vytvoriť automaticky, prípadne DAXovými funkciami CALENDAR a CALENDARAUTO.

Aktualizácia 20.11.2018: Časovú tabuľku si môžete vytvoriť za pár sekúnd aj pomocou Power Query – stiahnite si hotový skript s našou ultimátnou časovou tabuľkou v tomto článku.

Časová tabuľka z nášho vzorového súboru vyzerá takto:

Ďalším nevyhnutným krokom je označiť takúto tabuľku ako časovú tabuľku, pretože PowerPivot si to nevie domyslieť sám, a bez toho berie takúto tabuľku ako každú inú tabuľku. Prejdite teda do PowerPivotu, do tabuľkového zobrazenia, a tam prejdite na záložku s časovou tabuľkou. Následne, v hornej ponuke, prejdite do záložky Návrh, a kliknite na ikonu “Označiť ako tabuľku s dátumom“:

Pod ňou sa rozbalí malé menu, kde opäť kliknite na “Označiť ako tabuľku s dátumom“. Následne sa zobrazí toto okno:

V ňom treba vybrať stĺpec z časovej tabuľky, ktorý obsahuje dátumy. PowerPivot to zvyčajne spraví automaticky za Vás, takže potom už len kliknete na tlačítko OK, a máte to hotové. Ak by ste sa náhodou pomýlili, rovnakým postupom sa dá dostať naspäť, a vybrať iný stĺpec.

Odteraz už PowerPivot vie, ktorú tabuľku má používať ako časovú tabuľku, a môžete sa pustiť do využívania funkcií Time Intelligence 🙂

Povedzme, že máme nasledujúcu kontingenčku:

1. na riadkoch máme hierarchiu Rok-Mesiac-Deň,
2. v oblasti hodnôt máme merítko Obrat.

Keď teraz chceme zobraziť v ďalšom stĺpci “Obrat pred rokom“, ktorý bude fungovať na každej úrovni rozkliku, zadefinujeme nové merítko v PowerPivote takto:

Obrat pred rokom := CALCULATE([Obrat]; DATEADD(‘Čas'[DateKey]; -1; year))

Funkcia DATEADD posunie zadaný dátumový stĺpec z časovej tabuľky o počet období zadaný v 2. a 3. parametri funkcie – v našom prípade o -1 rok. Funkcia vráti ako výsledok posunutý dátumový stĺpec, ktorý zároveň slúži ako kontextový filter. Preto ho musíme použiť s funkciou CALCULATE, aby sme vypočítali obrat voči týmto posunutým dátumom. Ak by ste sa chceli posúvať inak ako rok, tak do 3. parametra funkcie DATEADD je možné zadať “day”, “month” alebo “quarter” – to v prípade, ak chcete vypočítať napr. obrat pred 2 mesiacmi, alebo obrat pred štvrťrokom a pod..

Keď použijeme toto merítko v našej kontingenčke, tak to bude vyzerať takto:

A keď rozklikneme napr. roky 2001 a 2002 na úroveň mesiacov, tak si všimnite, že merítko “Obrat pred rokom” za daný mesiac zobrazuje hodnotu merítka Obrat v rovnakom období presne pred rokom:

A keď si rozkliknete kontingenčku na úroveň dní, týždňov, štvrťrokov, či iných časových ukazovateľov, bude to ukazovať obrat pred rokom za rovnaké obdobie, aké máte rozkliknuté.

A podobným spôsobom funguje väčšina funkcií Time Intelligence v DAXe. Takmer všetky z nich sú kontextové funkcie, a používajú sa rovnakým spôsobom vo funkcii CALCULATE. To Vám poskytuje výhodu v tom, že tieto funkcie môžete neskôr aj vzájomne kombinovať. A “posúvaným” merítkom môže byť hocijaké komplexné merítko, nielen obrat.

Ak teda napríklad potrebujete zobraziť “Obrat pred 2 mesiacmi“, vzorec bude takýto:

Obrat pred 2 mesiacmi := CALCULATE([Obrat]; DATEADD(‘Čas'[DateKey]; -2; month))

Tu si len dajte pozor, že vzorec treba neskôr upraviť tak, aby na úrovni vyššej ako mesiac nepočítal nič, lebo inak to zbytočne bude mýliť užívateľov.

Keď potrebujete posúvať výpočet merítka iba o 1 rok dozadu, existuje v DAXe skrátená funkcia:

Obrat pred rokom v2 := CALCULATE([Obrat]; SAMEPERIODLASTYEAR(‘Čas'[DateKey]))

Táto funkcia je skratkou pre zápis DATEADD(časovýStĺpec; -1; year), takže ak chcete, môžete si takto skrátiť zápis.

Poznámka: funkcie DATEADD a SAMEPERIODLASTYEAR v niektorých prípadoch nefungujú, a vracajú chybové hlásenia, v ktorých spomínajú súvislé dátumy. V tom prípade treba situáciu buď ošetriť – ako je to v tomto článku – alebo spraviť náhradné riešenie, ako je to v tomto článku.

To však nie je všetko, čo dokážu funkcie Time Intelligence. Ďalším problémom, ktorý sa bežne rieši, je to, ako zobraziť kumulatívny obrat od začiatku roka po aktuálny dátum. Robí sa to preto, aby si firma vedela už počas roka vyhodnotiť, či napr. obrat k danému dňu je vyšší ako nižší ako v rovnakom dni pred rokom, a či teda firma zarobila do dnešného dňa viac alebo menej v porovnaní s rovnakým dňom pred rokom. Na toto potrebujeme spočítať kumulatívny obrat od začiatku roka po aktuálny dátum, niekedy označovaný ako YTD (year-to-date) veličina. Spočítame ho nasledovne:

Obrat YTD := CALCULATE([Obrat]; DATESYTD(‘Čas'[DateKey]))

Funkcia DATESYTD si zistí aktuálny dátum (ktorým sa myslí aktuálne rozkliknutý dátum na aktuálnom riadku kontingenčky), a zo zadaného dátumového stĺpca vráti dátumy od začiatku daného roka po tento aktuálny dátum. A podobne ako funkcia DATEADD, aj táto funkcia vráti tieto dátumy ako stĺpec dátumov, takže ho použijeme spolu s funkciou CALCULATE, ktorá nám spočíta merítko Obrat voči týmto dátumom. Výsledok bude v kontingenčke vyzerať takto:

Keď si overíte cez Excel alebo cez kalkulačku súčet tých piatich označených obratov na obrázku a porovnáte ju so sumou označenou v stĺpci “Obrat YTD“, tak zistíte, že tieto dve sumy sú rovnaké.

A ako sme spomínali vyššie, funkcie Time Intelligence môžete vzájomne aj kombinovať. Keby ste chceli vypočítať napr. “kumulatívny obrat pred rokom“, tak vzorec bude nasledovný:

Obrat YTD pred rokom := CALCULATE([Obrat]; SAMEPERIODLASTYEAR(DATESYTD(‘Čas'[DateKey])))

V tomto vzorci si najprv zistíme sadu dátumov od začiatku roka po aktuálny dátum, a potom ju celú posunieme o rok dozadu. Následne voči takýmto dátumom dáme vypočítať merítko Obrat, a máme výsledok 🙂

Takto teda v skratke fungujú funkcie Time Intelligence. Ak si ich potrebujete preštudovať všetky, pozrite si manuál DAX-u na webe, alebo si stiahnite príručku k jazyku DAX v pravej hornej časti tohto blogu. Obsahuje celú jednu kapitolu venujúcu sa všetkým funkciám Time Intelligence, vrátane príkladov, ako ich použiť. Vďaka tomu sa z Vás stanú časom Jedi mastri v časových analýzach, a časové porovnania Vám už nebudú robiť problém 🙂 Prípadne si pozrite moju knihu o Power BI a PowerPivote, kde sa o nich dozviete viac, a nájdete aj ďalšie príklady použitia.