Pomerne často sa stretávam s dotazom, ako vypočítať daný ukazovateľ za rovnaký týždeň pred rokom v jazyku DAX. Jazyk DAX má síce funkcie Time Intelligence, ale tie nepodporujú výpočty na úrovni týždňov. Preto nám ostáva napísať to iba alternatívnymi spôsobmi. A to je oveľa ľahšie, ako sa môže na prvý pohľad zdať.

Zvyčajne chcú užívatelia vypočítať ukazovateľ za rovnaký týždeň pred rokom kvôli tomu, aby mohli porovnávať rovnaké týždne medzi sebou. Problémov je s tým však hneď niekoľko. Ako napríklad to, kedy ktorý týždeň začína, podľa ktorého kalendára, a podobne. A ani sa človek nenazdá, a spadne do excelovského uvažovania, ktorého výsledkom je buď neúspech, alebo ultra pomalý a prekomplikovaný vzorec. Pritom len stačí správne uvažovať v jazyku DAX, a všetky tieto problémy zmiznú okamžite.

Ako na ukazovateľ za rovnaký týždeň pred rokom

Na demonštráciu začneme opäť s našim vzorovým súborom PowerPivotu, resp. Power BI.

Spravíme si kontingenčku, kde:

1. do oblasti hodnôt dáme merítko Obrat z tabuľky Objednávky,
2. do oblasti riadkov dáme stĺpce CalendarYear a WeekNumberOfYear z tabuľky Čas.

Výsledná kontingenčka bude vyzerať takto:

Táto kontingenčka zobrazuje obrat po jednotlivých týždňoch v daných rokoch. A zadanie je tentokrát vypočítať “Obrat za rovnaký týždeň pred rokom”, čiže Obrat v rovnakom týždni, ale o rok skôr. Aby sme potom medzi rokmi mohli porovnávať obraty v rovnakých týždňoch. Aj keď tie týždne nezačínali v rovnakých dátumoch.

Problém s týždňami

Na začiatok si všimnite jednu veľmi podstatnú vec. Stĺpec WeekNumberOfYear v tabuľke Čas. Tabuľka Čas je časová tabuľka, ktorá má dátumový stĺpec DateKey, ktorý obsahuje všetky dátumy v celej histórii analyzovaných dát. Z tohto dátumu sú odvodené všetky ostatné stĺpce. Čiže aj stĺpec WeekNumberOfYear, kde je vypočítané príslušné číslo týždňa v danom roku pre tento dátum:

Ak teda počítate štatistiky po týždňoch, tak takýmto spôsobom si predvypočítate číslo týždňa pre každý dátum v histórii. Aj keď máte rôzne požiadavky na týždne, resp. ich začiatok/koniec. Aby ste potom zbytočne nekomplikovali vzorce. Týmto pokryjete všetky možné scenáre rôznych definícií týždňov, ako napr. ISO týždeň, americký týždeň (začína v nedeľu), európsky týždeň (začína v pondelok), či moslimský týždeň (začína v sobotu). Jednoducho to do tejto tabuľky vypočítate do vypočítaného stĺpca s pomocou vlastného vzorca. Pre jednoduché prípady stačí použiť napr. funkciu WEEKNUM v jazyku DAX.

My sa od tohto okamihu budeme tváriť, že sme si už akože vypočítali číslo týždňa do stĺpca WeekNumberOfYear podľa svojich pravidiel a požiadaviek.

A ako teraz vypočítať ukazovateľ za rovnaký týždeň pred rokom?

Na výber sú vo všeobecnosti 2 možnosti, aj keď určite sami nájdete časom oveľa viac riešení.

Najjednoduchšou možnosťou pre databázistov, excelistov a iných ľudí, ktorí majú riadkové myslenie a ešte nevedia správne rozmýšľať v jazyku DAX, je použiť univerzálny výpočtový vzorec, o ktorom píšem v mojej knihe o Power BI, napríklad v takomto merítku:

Obrat za rovnaký týždeň pred rokom :=
CALCULATE (
    [Obrat];
    FILTER (
        ALL ( 'Objednávky' );
        YEAR ( 'Objednávky'[SaleDateKey] ) = YEAR ( MAX ( 'Objednávky'[SaleDateKey] ) ) - 1
            && WEEKNUM ( 'Objednávky'[SaleDateKey] ) = WEEKNUM ( MAX ( 'Objednávky'[SaleDateKey] ) )
    )
)

A po dosadení do kontingenčky dostaneme správne výsledky:

Sú to čísla, ktoré nájdete pre merítko Obrat v rovnakých týždňoch pred rokom. Podstatou vzorca je to, že si funkciou MAX zistíme číslo aktuálne počítaného roka a týždňa. Potom vyfiltrujeme z tabuľky Objednávky všetky riadky, ktoré majú rovnaké číslo týždňa, plus ktoré majú číslo roka o 1 nižšie. Voči tým riadkom dáme potom pomocou funkcie CALCULATE vypočítať Obrat, ktorý zosumuje ceny v týchto vyfiltrovaných objednávkach.

Nevýhodou tohto vzorca je jeho relatívna neprehľadnosť, a najmä pomalá rýchlosť na veľkých dátach. Pretože hovoríme DAX-u, ako to má presne spraviť, a nie to, čo má vypočítať. Čo je fajn, ak ešte len začínate s jazykom DAX, ale už nie až tak fajn, keď ho chcete využiť naplno.

Správne myslenie v jazyku DAX

Správnejším riešením je využiť to, ako funguje kontext výpočtu v jazyku DAX. Čiže filtre, ktoré máme v aktuálnom kontexte výpočtu. Keď si vyberiete ľubovoľné políčko, pre ktoré sa počíta Obrat, tak uvidíte, že jeho kontext výpočtu je definovaný 2 filtrami:

Na obrázku je vyznačené políčko pre Obrat za 28. týždeň v roku 2002. Čiže kontext výpočtu tohto políčka je definovaný týmito 2 filtrami:

1. CalendarYear = 2002
2. WeekNumberOfYear = 28

A keď chceme, aby to políčko videlo na rovnaký týždeň v predchádzajúcom roku, tak musíme ten prvý filter upraviť tak, že znížime hodnotu v ňom o jednotku, aby sme dostali takéto 2 filtre:

1. CalendarYear = 2001
2. WeekNumberOfYear = 28

Keď potom v takom kontexte výpočtu dáme vypočítať merítko Obrat, tak dostaneme náš ukazovateľ za rovnaký týždeň pred rokom – čiže “Obrat za rovnaký týždeň pred rokom”.

Riešenie

To spravíme veľmi jednoducho. Najprv si funkciou SELECTEDVALUE zistíme aktuálnu hodnotu vo filtri nad stĺpcom CalendarYear. Potom použijeme indický zápis filtrov, aby sme prepísali hodnotu vo filtri nad týmto stĺpcom, na hodnotu o 1 nižšiu. A budeme si musieť pomôcť premennou v jazyku DAX, aby nám CALCULATE dovolil použiť indický zápis filtrov.

Výsledný vzorec bude takýto:

Obrat za rovnaký týždeň pred rokom 2 := 
VAR predchadzajuciRok = SELECTEDVALUE('Čas'[CalendarYear]) - 1
RETURN
CALCULATE([Obrat]; 'Čas'[CalendarYear] = predchadzajuciRok)

Po dosadení do kontingenčky dostaneme úplne rovnaký výsledok ako v predchádzajúcom prípade:

V tomto prípade je však výpočet oveľa rýchlejší, pretože sme využili základné vlastnosti kontextu výpočtu, a povedali sme DAX-u jeho vlastným jazykom, čo chceme vypočítať, a nie to ako to chceme vypočítať. To umožnilo PowerPivotu na pozadí vytvoriť lepší exekučný plán, a vypočítať to snáď najrýchlejším možným spôsobom, aký pozná. Na takýchto malých dátach síce nespoznáte rozdiel, ale na miliónoch riadkov je už rozdiel v rýchlosti niekoľkonásobný.

Takto teda vypočítate ukazovateľ za rovnaký týždeň pred rokom. Ide to spraviť viacerými spôsobmi, ale ten posledný je najsprávnejší. Pretože využívate to, ako funguje jazyk DAX, a ako sa v ňom správne myslí. Vďaka tomu napíšete vzorec, ktorý je nielen rýchlejší, ale aj kratší a prehľadnejší. A takýmto spôsobom ide vypočítať potom nielen ukazovateľ za rovnaký týždeň pred rokom, ale aj iné ukazovatele za iné obdobia v iných časových úsekoch, či už v minulosti alebo v budúcnosti. Týmto spôsobom dokonca ide prepísať mnoho funkcií Time Intelligence, keď napr. musíte fungovať v režime DirectQuery, kde tieto funkcie nie sú. Pretože funkcie Time Intelligence v jazyku DAX sú vo väčšine prípadov len skratkovité funkcie k tomu, čo sa dá zapísať napríklad takýmto spôsobom. A keď už viete, ako veci fungujú, tak viete aj takéto ukazovatele napísať v PowerPivote aj v Power BI ľavou zadnou 🙂

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.