Časová analýza dát je v takmer každom bežnom reporte. A veľakrát sa tam nachádzajú aj ukazovatele YTD (year-to-date), ktoré zobrazujú kumlatívnu verziu ukazovateľa od začiatku roka po aktuálny dátum. Jazyk DAX s tým nemá problém, ale vo väčšine prípadov, ak máte správne vytvorený dátový model, tak už nie vždy užívateľom vyhovuje, ako sa ten výpočet správa po aktuálnom dátume. Preto si teraz ukážeme, ako to jednoducho ošetriť.

Na demonštráciu problému si vytvoríme jednoduchý dátový model. Budeme analyzovať týchto zopár objednávok:

V prvom kroku si naimportujeme túto tabuľku, a nazveme ju “Objednávky”. V druhom kroku si naimportujeme časovú tabuľku pre roky 2015-2019. Využite napr. ultimátnu časovú tabuľku v Power Query, kde si upravíte ten skript na jeho začiatku na tento rozsah rokov. Túto tabuľku pomenujeme “Čas”.

Tabuľku Objednávky a Čas prepojíme podľa dátumových stĺpcov. Čiže v PowerPivote, resp. Power BI spravte prepojenie z tabuľky Objednávky a stĺpca Dátum, na tabuľku Čas a stĺpec Dátum.

Potom označte tabuľku Čas ako časovú tabuľku.

Potom si vytvoríme merítko Obrat, ktoré bude počítať sumu cien objednávok:

Obrat := SUM(‘Objednávky'[Cena])

Potom si vyskladáme novú kontingenčnú tabuľku, kde:

1. v oblasti hodnôt bude merítko Obrat,
2. v oblasti riadkov budú stĺpce Rok a Mesiac z tabuľky Čas.

Výsledná kontingenčka vyzerá takto:

Nič svetoborné, ale uvidíte zachvíľu, čo sa stane, keď pridáme ukazovateľ YTD.

Teraz vytvoríme ďalšie merítko, ktoré bude počítať kumulatívny Obrat od začiatku roku. To spravíme s využitím funkcií Time Intelligence veľmi ľahko:

Obrat YTD := CALCULATE([Obrat]; DATESYTD(‘Čas'[Dátum]))

Po jeho dosadení do kontingenčky to ale bude vyzerať takto:

Kumulatívny súčet sa pre január aj február vypočítal správne, ale namiesto pôvodných 2 zobrazených mesiacov, máme zrazu v kontingenčke 12 mesiacov. A čo bude iritovať väčšinu užívateľov – že aj v mesiacoch marec až december máme zobrazený obrat YTD, keď ešte nie sú žiadne obraty za tieto mesiace. A preto by sme aj takéto riadky chceli skryť.

Prečo sa to ale počíta aj pre tie mesiace od marca ďalej? Jednoducho preto, lebo v časovej tabuľke máme všetkých 12 mesiacov pre každý rok. Tak je to správne nastavené, aby správne fungovali funkcie Time Intelligence. A výpočet merítok funguje vždy tak, že sa vzorec merítka spustí najprv pre všetky kombinácie hodnôt v stĺpcoch, ktoré máte v kontingenčke. Čiže v tomto prípade sa spustí pre všetky mesiace vo všetkých rokoch. V rokoch pred 2019 to nevypočíta nič, pretože tam neboli žiadne objednávky. V roku 2019 už objednávky boli, a keď sa počíta bunka napr. za apríl 2019, tak sa počíta kumulatívny obrat od začiatku roka po apríl 2019 vrátane. Čo je de facto obrat od začiatku roku, zobrazený pre február 2019, pretože ďalej už nie sú objednávky. A preto sa to februárové číslo opakuje pre všetky mesiace, až do konca roku 2019.

V ďalšom kroku kontingenčka štandardne skryje všetky prázdne riadky, ak ste to náhodou nevypli. Keď sme tam mali predtým iba merítko Obrat, tak to skrylo riadky pre tie mesiace a roky, pre ktoré sa toto merítko nevypočítalo (čiže pre ktoré neboli žiadne objednávky / žiaden obrat). A preto v tej prvej kontingenčke iba s Obratom zostali viditeľné riadky iba pre január a február 2019.

Keď sme však pridali do kontingenčky merítko “Obrat YTD”, tak to sa vypočítalo pre všetky mesiace v roku 2019, tak ako sme si vysvetlili vyššie. A keďže pri tých mesiacoch už neboli prázdne riadky, tak preto aj kontingenčka takéto riadky neskryla. A ak chceme, aby ich skryla, tak tie prázdne riadky musíme nejako zabezpečiť.

Riešením je jednoduchá finta, ktorú pozná snáď každý, kto už dlhšie pracuje s PowerPivotom či Power BI. Spočíva v tom, že keď chcete, aby sa skryli konkrétne riadky v kontingenčke, tak zabezpečte, aby sa v nich pre všetky merítka vypočítali prázdne, resp. žiadne hodnoty. Čiže ak chceme skryť v našom prípade všetky riadky od marca 2019 ďalej, tak musíme pre merítka “Obrat” aj “Obrat YTD” zabezpečiť, aby vrátili prázdnu hodnotu. V prípade merítka Obrat to nemusíte riešiť  – pre to obdobie neexistujú žiadne objednávky. A v prípade merítka “Obrat YTD” využijeme fakt, že ho chceme mať vypočítané iba na tých riadkoch, kde je vypočítaný nejaký obrat. To spravíme veľmi jednoducho, otestovaním merítka Obrat na hodnotu BLANK. Výsledný vzorec pre merítko “Obrat YTD” po úprave bude takýto:

Obrat YTD := IF([Obrat] <> BLANK(); CALCULATE([Obrat]; DATESYTD(‘Čas'[Dátum])); BLANK())

Výsledok bude vyzerať takto:

A to je presne to, čo sme chceli. Išlo by to samozrejme spraviť aj komplikovanejšie – testovaním a filtrovaním na aktuálny dátum – ale načo si komplikovať život, keď viete, že objednávky zvyčajne nemáte zadávané s dátumom do budúcnosti? Po využití základných vlastností dátového modelu, výpočtov a dát v ňom, ide väčšina výpočtov totižto robiť takto jednoducho 🙂

A ak by ste mali náhodou objednávky aj do budúcnosti, tak riešenie je jednoduché – pridať do funkcie CALCULATE rozsah dátumov od začiatku roku po dnes. To spravíme pomocou funkcií Time Intelligence (vypeknené cez DAX Formatter):

Obrat YTD :=
IF (
    [Obrat] <> BLANK ();
    CALCULATE (
        [Obrat];
        DATESYTD ( 'Čas'[Dátum] );
        DATESBETWEEN ( 'Čas'[Dátum]; STARTOFYEAR ( 'Čas'[Dátum] ); TODAY () )
    );
    BLANK ()
)

Tento článok je písaný 30.1.2019, a objednávky máme v tomto prípade až do 2.2.2019. Preto aj “Obrat YTD” by mal ukazovať Obrat spočítaný iba po koniec januára. Výsledok bude vyzerať takto:

Takto je to už zobrazené správne aj pre dopredné objednávky. A čo s tým, že sa zobrazuje aj pre február? To si už nechajte za domácu úlohu. Stačí na to využiť jednu zo základných vlastností kontextu výpočtu a 2 funkcie pre prácu s ním, ktoré nájdete aj v mojej knihe o Power BI, a budete to mať ošetrené úplne ideálne 🙂

Michal Chmelár

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.