Minule som sa stretol u jedného klienta so zaujímavou požiadavkou. Chcel zobraziť údaje o chybovosti výroby v Power BI pomocou časovej kaskády, čiže postupného rozpadu štatistiky z predchádzajúcich rokov až na aktuálne mesiace. Na prvý pohľad komplikované zadanie vyvolalo dojem, že by sme to mali riešiť pokročilejšími vzorcami. Nakoniec však časová kaskáda išla spraviť v Power BI veľmi jednoducho.

Zadanie príkladu

Problém si ukážeme na takejto vzorovej tabuľke, nazvanej írečitým slovenským názvom „Data“:

V tejto tabuľke sú 2 stĺpce. V prvom je dátum merania, a v druhom je percento chybovosti výroby za ten deň. Kvôli krátkosti ukážky sú uvedené iba niektoré dátumy počas posledných 2 rokov (v čase písania tohto článku je aktuálny dátum 5.11.2024).

Zadanie je také, že tieto dáta potrebujeme vizualizovať v grafe, kde zobrazíme priemernú chybovosť v percentách za dané časové obdobie. Vizualizovať to však nebudeme po jednotlivých dátumoch, pretože v tej tabuľke môžu byť pokojne aj tisíce záznamov. Budeme to vizualizovať pomocou časovej kaskády tak, že najprv zobrazíme kumulatívne stĺpce pre všetky predchádzajúce roky. Po nich nasledujú stĺpce pre všetky predchádzajúce kvartály v aktuálnom roku, a po nich nasledujú stĺpce s mesiacmi z aktuálneho kvartálu. Čiže tak ako sa postupne blíži aktuálne obdobie, tak sa postupne graf kaskádovito rozpadá v čase do menších a menších období.

Najprv skúsime vykresliť graf v Power BI tak, že:

1. vytvoríme klasický stĺpcový graf,
2. do X-ovej osi dáme stĺpec „Dátum“,
3. do Y-ovej osi dáme priemer zo stĺpca „Chybovosť“,
4. vo formátovaní grafu, resp. vo vizualizačnom paneli, nastavíme, aby nám to zobrazovalo po jednotlivých dátumoch (maliarsky štetec => Os X => Typ => Kategorické),
5. v grafe zapneme zobrazenie hodnôt nad stĺpcami (zapneme prepínač v záhlaví skupiny nastavení s názvom „Označenia údajov“),
6. stĺpcu „Chybovosť“ nastavíme formátovanie v percentách,
7. graf zoradíme podľa poľa „Dátum“, ak je to potrebné.

Výsledný graf bude vyzerať takto:

Tu vidíte, že tento graf síce zobrazuje priemernú chybovosť pre každý dátum v zdrojovej tabuľke, ale v praxi to nie je veľmi použiteľné. Jednak vás nezaujíma chybovosť pre konkrétne dni 2 roky dozadu, a problémom bude aj počet stĺpcov v takomto grafe. Ak máte v tej tabuľke dáta za posledné 2 roky, tak dostanete cca. 2×365 stĺpcov. Takýto graf, napriek tomu že Power BI ho bez problémov zvládne vykresliť, bude pre užívateľa sám osebe absolútne nepoužiteľný.

Časová kaskáda

Riešením je vytvoriť časovú kaskádu pre vhodné časové obdobia, tak ako sme si už uviedli vyššie. A riešenie je to veľmi jednoduché.

V prvom kroku si pridáme do dátového modelu časovú tabuľku, s rozsahom dátumov pre tie roky, ktoré potrebujeme analyzovať. My v našom prípade použijeme našu ultimátnu časovú tabuľku, ktorú bude stačiť nastaviť na rozsah rokov 2023-2024. Nazveme ju ako „Čas“, a po jej naimportovaní do dátového modelu, prepojíme stĺpec „Dátum“ v tejto časovej tabuľke so stĺpcom „Dátum“ v tabuľke „Data“:

Teraz už stačí iba vytvoriť nový vypočítaný stĺpec v tabuľke „Čas“ s názvom „Časová kaskáda“, s takýmto vzorcom:

Časová kaskáda = 
IF('Čas'[Rok] < YEAR(TODAY()), LEFT('Čas'[Rok], 4), 
   IF('Čas'[Štvrťrok] < QUARTER(TODAY()), 'Čas'[Rok-Štvrťrok], 'Čas'[Rok-Mesiac]))

V tomto stĺpci pre každý z dátumov v časovej tabuľke vypočítame, do ktorého časového zoskupenia patrí. Ak je dátum v predchádzajúcich rokoch, tak vrátime číslo roka (zabalené do funkcie LEFT, čím z neho spravíme text). Ak je dátum v tomto roku v niektorom z predchádzajúcich kvartálov, tak vrátime daný kvartál, resp. jeho reportingovú verziu zo stĺpca „Rok-Štvrťrok“. A ak je dátum v aktuálnom kvartáli, tak vrátime preň aktuálny mesiac, resp. jeho reportingovú verziu v stĺpci „Rok-Mesiac“. Takto vytvoríme zoskupovací stĺpec, ktorý po použití v grafe alebo kontingenčnej tabuľke zoskupí dáta do takýchto období v našej časovej kaskáde. Dáta v tomto stĺpci budú vyzerať, napríklad pre dátumy na prelome rokov 2023 a 2024, takto:

Následne odstránime z grafu stĺpec „Dátum“, a namiesto neho tam dáme tento nový stĺpec „Časová kaskáda“. Výsledný graf bude vyzerať takto:

Na grafe síce vidíte, že sú tam jednotlivé obdobia časovej kaskády, ale zatiaľ to nevyzerá vábne. Hodnoty v tom novom stĺpci sú textové hodnoty, a preto ich dátový model zoraďuje ako texty. Nie je to však nič, čo by nešlo poľahky vyriešiť.

Časová kaskáda – správne zoradenie

Ak chceme, aby tie časové obdobia boli zoradené vo všetkých grafoch v správnej postupnosti, tak stačí vyrobiť ďalší vypočítaný stĺpec, kde si vypočítame poradové hodnoty pre hodnoty v predchádzajúcom stĺpci, podľa ktorých jeho hodnoty budú zoradené v grafoch. Stĺpec nazveme „Časová kaskáda poradie“, a bude mať napr. takýto vzorec:

Časová kaskáda poradie = 
IF('Čas'[Rok] < YEAR(TODAY()), 'Čas'[Rok],
   IF('Čas'[Štvrťrok] < QUARTER(TODAY()), 'Čas'[Rok] + 200 + 'Čas'[Štvrťrok], 
      'Čas'[Rok] + 500 + 'Čas'[Mesiac]))

V tomto vzorci kreatívne využívame hodnoty a závislosti v dátach tak, aby najnižšie zoraďovacie čísla vrátilo pre roky, väčšie čísla pre kvartály, a ešte väčšie čísla pre mesiace. Namiesto konštánt 200 a 500 sa dajú použiť aj iné, podobné konštanty.

Keď už máme vytvorený tento zoraďovací stĺpec, tak potom zapneme funkcionalitu „Zoradiť podľa stĺpca“ na tom prvom stĺpci „Časová kaskáda“ tak, že určíme, že sa jeho položky v grafoch majú zoraďovať podľa hodnôt v tom druhom stĺpci „Časová kaskáda poradie“. Návod pre Power BI nájdete v tomto článku, a návod pre excelovský PowerPivot nájdete v tomto článku.

Ak ste predtým dali zoradiť graf podľa stĺpca „Časová kaskáda“, tak po aplikovaní tohto nastavenia bude výsledný graf vyzerať takto:

Tam vidíte, že sa časová kaskáda už zobrazí v správnom poradí. Najprv idú všetky predchádzajúce roky (máme v dátach iba jeden), potom všetky predchádzajúce kvartály v aktuálnom roku (tam sú aktuálne 3), a potom mesiace v aktuálnom kvartáli (tam sú aktuálne 2). Pripomínam, že článok je písaný dňa 5.11.2024, takže graf na obrázku je vykreslený voči tomuto dátumu.

Následne už stačí v reporte, resp. v dátovom modeli, iba nastaviť automatickú aktualizáciu dát. Stačí 1x za deň. Takto sa Vám bude časová kaskáda prepočítavať automaticky podľa hore uvedených pravidiel každý jeden deň. Takto sa o to potom už nemusíte vôbec starať.

Na záver

Takto jednoducho sa dá vytvoriť časová kaskáda v dátovom modeli, či už v Power BI, PowerPivote alebo SSAS Tabulare. Pričom napríklad v Power BI či Exceli ju zobrazíte rovnakým spôsobom nielen v grafoch, ale aj v kontingenčných tabuľkách. Navonok komplikované zadanie sa nakoniec dalo vyriešiť 2 jednoduchými vzorcami. Tak, ako by to spravil každý jeden profík, ktorý pozná princípy správneho dátového modelovania. Ale o tom už v inom článku, či na niektorom z kurzov dátového modelovania 🙂