Keď začínate používať funkcie Time Intelligence na časové analýzy v PowerPivote v Exceli, tak veci spočiatku vyzerajú, že fungujú. Niekedy však narazíte na celkom exotické chyby, ktoré nevyzerajú byť chybami, a idete rozbiť klávesnicu, keď to nie a nie vyriešiť. Jednou z takých záludností je používanie funkcií DATEADD a SAMEPERIODLASTYEAR spolu so stĺpcami kontingenčky. A následne notoricky známa chyba „Funkciu DATEADD možno použiť iba so súvislými výbermi dátumov„. Ako ju opraviť?

Riešenie si opäť ukážeme na našom vzorovom súbore PowerPivotu.

Povedzme, že chceme vypočítať Obrat, a vedľa neho zobraziť Obrat pred rokom. Podľa návodu v tomto článku teda spravíme v dátovom modeli takéto merítko:

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

Následne spravíme takúto kontingenčku:

1. do oblasti hodnôt dáme merítka Obrat a Obrat pred rokom,
2. do oblasti riadkov dáme stĺpce CalendarYear a MonthName z tabuľky Čas.

Výsledná kontingečka bude zobrazovať Obrat a Obrat pred rokom, pre každý rok a mesiac:

Potiaľto fajn, všetko funguje presne ako má.

Problém však nastane, keď presunieme stĺpec MonthName do oblasti stĺpcov kontingenčky. Namiesto preusporiadania kontingenčky dostaneme takéto chrumkavé hlásenie:

Chybová hláška hovorí čosi o tom, že musíme mať súvislý výber dátumov. Ale veď sakra my máme súvislý výber dátumov!!! A prečo to sakra išlo, keď to bolo na riadkoch, a nejde, keď to je v stĺpoch?! Zasa niečo z kategórie „it’s not a bug, it’s a feature“ ???

V tomto prípade je problém zašitý niekde inde. Keď si odstránime z kontingenčky merítko Obrat pred rokom, tak presun mesiaca do stĺpcov ide bez problémov:

A keď ho tam znova pridáme, tak znova tá istá chyba. Kde sakra ti inženýři z NDR udělali chybu?

Problém je v tom, ako kontingenčka pracuje s PowerPivotom. A ako sa to správa na úrovni Grand Totalu, čiže celkového súčtu. Keď sa pozriete na posledný riadok kontingečky, tak to na prvý pohľad nevyzerá nejak zaujímavo:

Problém je však v tom, že táto vyznačená bunka na obrázku reprezentuje všetky januáre za všetky roky. A funkcia DATEADD sa s tým, narozdiel od iných funkcií, nevie vysporiadať, pretože detekovala, že má robiť niečo naprieč viacerými rokmi súčasne. A to spraviť nevie a vyhodí onu chybu. Preto jej musíme trochu pomôcť.

Trikom je otestovať si, či je v aktuálnom kontexte výpočtu vybratý iba jeden rok. To spravíme pomocou funkcie HASONEVALUE takto:

Obrat pred rokom:=IF(HASONEVALUE(‚Čas'[CalendarYear]); CALCULATE([Obrat]; DATEADD(‚Čas'[DateKey]; -1; YEAR)))

…alebo vypeknené cez DAX Formatter:

Bližšie fungovanie funkcie HASONEVALUE popisujem v mojej knihe. V skratke – používa sa na otestovanie, či je v kontexte výpočtu vybraná iba jedna hodnota zo zadaného stĺpca. A v spojení s funkciou IF si otestujeme, či je vybratý iba jeden rok, a ak áno, tak vypočítame náš pôvodný vzorec.

Toto riešenie už bude fungovať, a po pridaní merítka do kontingečky to už pekne zbehne:

Čiže takto elegantne vyriešite jednu z najčastejších frustrácií s funkciou DATEADD v kontingenčke. Jednoduché, rýchle, elegantné. A pre istotu takto nezabúdajte ošetriť každý jeden vzorec s funkciou DATEADD.

Druhou alternatívou je otestovať si, či ste na úrovni Grand Totalu alebo nie, a podľa toho počítať alebo nepočítať daný pôvodný vzorec. Je to popísané v tomto článku. Na tento účel je to ale zbytočne komplikované riešenie.

Ak by vám to aj tak nefungovalo, tak existuje ešte jedno extrémnejšie riešenie – zabaliť to do funkcie SUMX, a spočítať to postupne po jednodlivých dňoch:

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

Za to vás PowerPivot ale výkonnostne moc neodmení. Je to asi najpomalšie riešenie, aké existuje, ale – funguje.

Poznámka: funkcia DATEADD a SAMEPERIODLASTYEAR môže vyhodiť ešte aj inú, podobnú chybovú hlášku o súvislých dátumoch. Jej riešenie nájdete v tomto článku.

A s ktorou funkciou nebankujete Vy?