Blížia sa nám veľkonočné sviatky, a preto by nebolo od veci pozrieť sa na to, ako vieme dostať sviatky do PowerPivotu, tabulárneho modelu a Power BI. Problém so sviatkami je ten, že v každej krajine sú inak, a aj v rámci jednej krajiny sa niektoré z nich hýbu v rámci dní, ako napr. aktuálne veľkonočné sviatky. Na prvý pohľad zložitý problém ale vieme pomocou tabuliek vyriešiť veľmi jednoducho.

Povedzme, že sme dostali zadanie spraviť analýzu predajov podľa pracovných vs. nepracovných dní. A práve tie nepracovné dni nám tak trochu komplikujú sviatky. Na to si budeme potrebovať zistiť 2 veci:

1. či je víkend alebo nie
2. či je v daný deň sviatok alebo nie

Keď tieto 2 podmienky dáme dohromady, tak nám z toho vyjde, že nepracovný deň je vtedy, keď je víkend alebo sviatok, a v opačnom prípade sa jedná o pracovný deň. Oba čiastkové problémy vyriešime vypočítanými stĺpcami. Riešenie si ukážeme na našom vzorovom súbore PowerPivotu.

Najprv teda k tomu, ako zistiť, či je v daný deň víkend alebo nie. V časovej tabuľke Čas máme stĺpec DayNumberOfWeek, ktorý určuje poradové číslo dňa v týždni. Ak by ste takýto stĺpec nemali, ide dopočítať z dátumu funkciou WEEKDAY. Keďže je vzorový súbor založený na amerických dátach, kde prvý deň v týždni je nedeľa, tak nedeľa má číslo 1 a sobota číslo dňa 7. Pre slovenské dáta si nahraďte tieto konštanty číslami 6 a 7.

Vzorec pre vypočítaný stĺpec s názom Víkend, ktorý určuje či je víkend alebo nie, bude nasledovný:

=IF([DayNumberOfWeek] = 7 || [DayNumberOfWeek] = 1; „áno“; „nie“)

Alebo v Power BI to skrátene ide napísať aj takto:

Víkend = IF([DayNumberOfWeek] IN {7; 1}; „áno“; „nie“)

Týmto si teda zistíme, či daný deň je víkend alebo nie, a zároveň si tým výrazne uľahčíme finálny výpočet nepracovných dní.

A teraz k tomu, ako sa popasovať so sviatkami. Najjednoduchšia možnosť je naimportovať si do dátového modelu ďalšiu tabuľku, ktorá bude obsahovať sviatky pre všetky roky v našom dátovom modeli. Môžete ju napr. pripojiť ako linkovanú tabuľku, alebo naimportovať z webu cez Power Query, alebo ju zohnať odniekiaľ inak a naimportovať do dátového modelu. Takáto tabuľka musí mať 2 stĺpce – v jednom je celý dátum sviatku, v druhom je názov sviatku. Dajte si pozor na to, aby ten dátumový stĺpec bol naozaj dátumový (aby ho tak pochopil Excel, resp. Power BI), pretože ak to bude iba obyčajný text, tak to cez tento stĺpec neprepojíte na zvyšok dátového modelu. Vzorová skrátená tabuľka so sviatkami z roku 2003 pre náš vzorový súbor vyzerá takto:

Túto tabuľku následne pripojíme do modelu ako linkovanú tabuľku (alebo ju naimportujte, ak nemôžete/nechcete používať linkované tabuľky), a nazveme ju povedzme Sviatky. Potom ju prepojte s tabuľkou Čas nasledovne:

1. dátumový stĺpec z jednej tabuľky prepojte na dátumový stĺpec z druhej tabuľky (v našom prípade prepojíme z tabuľky Čas stĺpec DateKey na tabuľku Sviatky a stĺpec Datum)
2. dajte pozor, aby smer šípky prepojenia išiel z tabuľky Čas do tabuľky Sviatky (ak používate Excel 2010/2013 alebo SSAS 2012/2014), resp. z tabuľky Sviatky do tabuľky Čas (ak používate Excel 2016+, Power BI alebo SSAS 2016+), pretože inak môžete pri písaní nasledujúcich vzorcov dostať chybové hlásenie o neexistencii prepojení medzi tabuľkami aj napriek tomu, že tam je (o dôvodoch, prečo to tak je, niekedy inokedy)

Prepojenie v Exceli 2013 bude vyzerať takto:

Potom prejdite do tabuľky Čas, a dotiahnite názov príslušného sviatku ku každému dátumu, ku ktorému prislúcha, pomocou našej starej známej funkcie RELATED. V tabuľke Čas vytvorte vypočítaný stĺpec s názvom Sviatok, a s týmto vzorcom:

=RELATED(Sviatky[Sviatok])

Takto si teda zistíme ku každému dňu, či je sviatkom alebo nie. Ak je, tak je niečo napísané v stĺpci Sviatok pre daný riadok. Ak nie je, je daná hodnota v stĺpci Sviatok prázdna.

Máme teda všetko potrebné na zistenie pracovných a nepracovných dní, a môžeme pridať do tabuľky Čas finálny vypočítaný stĺpec s názvom „Pracovný deň“, a s týmto vzorcom:

=IF([Víkend] = „áno“ || NOT(ISBLANK([Sviatok])); „nepracovný deň“; „pracovný deň“)

Všetky 3 vypočítané stĺpce budú vedľa seba vyzerať takto:

Nakoniec si to vyskúšame v kontingenčnej tabuľke, či to funguje alebo nie. Vyrobíme kontingenčku takto:

1. do oblasti hodnôt dáme merítko Obrat z tabuľky Objednávky
2. do oblasti riadkov dáme stĺpec „Pracovný deň“ z tabuľky Čas

Výsledné dielo bude vyzerať takto:

Takže takto jednoducho idú pridať sviatky do dátového modelu v PowerPivote, v tabulárnom modeli aj v Power BI. Efektívne tu využívame základnú schopnosť PowerPivotu spájať dáta z viacerých tabuliek na to, aby sme sa vyhli komplikovaným vzorcom a riešeniam. A práve to je na tom najlepšie, že takéto riešenie je jednoduché, škálovateľné na neobmedzený počet rokov, a zároveň aj rýchle. Tak, ako celý PowerPivot, keď viete, ako ho správne používať, a využívate ho na maximum 🙂