Pri vytváraní kontingenčných tabuliek v Exceli, alebo reportov v Power BI, máte niekedy požiadavku použiť dátumový slicer, ktorý je nastavený na dnešný dátum. Keď to skúsite spraviť s bežným dátumovým stĺpcom, tak na ďalší deň po vytvorení reportu narazíte na jeden problém – ak ste deň predtým uložili report, v ktorom bol vybraný aktuálny deň, tak po otvorení reportu na ďalší deň tam bude vybraný stále ten istý deň – tentokrát však už včerajší. A užívatelia – Váš šéf či manažér – Vás za to zrovna nepochvália, že každý deň musia preklikávať dátum v sliceri, a meniť slicer na dnešný dátum. Poďme sa teda pozrieť na jednoduchý trik, ako sa s tým popasovať, aby sa to dialo automaticky. A ktorý bude rovnako fungovať v Exceli aj v Power BI.

Nasledujúci postup je popísaný pre Excel.

Povedzme, že dnes je 11.11.2017 (aká to náhodička). Keď si vo svojom reporte spravíte slicer z dátumového stĺpca, a zakliknete v ňom dnešný deň, tak to bude vyzerať takto:

Ak takýto report otvoríte o deň neskôr, tak Excel (a takisto aj Power BI) zobrazí report aj slicer s rovnakou vybranou položkou, akú tam mal “včera” – t.j. 11.11.2017, a nie 12.11.2017, ako by očakával užívateľ. Je to kvôli tomu, že Excel si pri ukladaní súboru pamätá konkrétnu vybranú hodnotu v sliceri, a pri ďalšom otvorení reportu proste aplikuje tú istú hodnotu na slicer a prepočíta report. On proste nevie, že ste tým deň pretým mysleli, že chcete mať prednastavený slicer na dnešný dátum. A ak ho chceme prinútiť, aby robil to čo chceme, tak musíme toto správanie využiť tak, aby to robilo to čo potrebujeme. Preto potrebujeme vytvoriť nejakú nezávislú statickú hodnotu v takomto stĺpci – napr. hodnotu “dnes” – namiesto aktuálneho dátumu, a zároveň využiť automatiku v Exceli a PowerPivote.

Riešením je spraviť si ďalší vypočítaný stĺpec v rovnakej tabuľke, odkiaľ máme tento dátumový stĺpec. Do tohto vypočítaného stĺpca dáme podmienku, že ak je daný deň rovný dnešku, tak tam bude hodnota “dnes”, inak tam bude naformátovaný dátum z toho pôvodného stĺpca. Vzorec pre takýto stĺpec bude nasledovný:

=IF([Dátum] = TODAY(); “dnes”; FORMAT([Dátum]; “d. M. yyyy”))

Funkcia TODAY vracia aktuálny deň, a funkcia FORMAT funguje podobne ako funkcia TEXT v Exceli – naformátuje zadanú hodnotu na zadaný formát. Túto funkciu sme museli použiť preto, lebo funkcia IF vyžaduje, aby obe návratové hodnoty mali rovnaký dátový typ. A keďže dátový typ hodnoty “dnes” je Text, tak aj dátum musíme prekonvertovať z typu Dátum na typ Text pomocou funkcie FORMAT.

Výsledný vypočítaný stĺpec bude vyzerať takto:

Nezabudnite potom ešte nastaviť zoradenie tohto nového stĺpca podľa hodnôt toho pôvodného stĺpca, aby sa aj tie nové hodnoty zobrazovali v sliceri v správnom poradí. Pretože už nie sú dátumové, ale textové, a tam funguje triedenie podľa abecedy, resp. podľa poradia znakov, a nie podľa dátumov. Ako nastaviť triedenie podľa iného stĺpca nájdete v tomto článku.

Následne môžeme vymeniť pôvodný slicer za slicer z tohto nového stĺpca, a označiť v ňom položku “dnes”, reprezentujúcu dnešný dátum:

Keď takýto report uložíte a otvoríte na ďalší deň, tak Excel si bude pamätať, že ste naposledy mali v sliceri vybranú hodnotu “dnes”. Keď ale máte nastavené automatické prepočítavanie reportu po otvorení súboru, tak hneď po štarte Excel spustí PowerPivot, a ten automaticky prepočíta všetky vzorce v dátovom modeli, vrátane nášho vypočítaného stĺpca. A keďže sme v ňom použili funkciu TODAY, tak sa nám hodnota “dnes” posunie o jeden dátum ďalej, a tým pádom sa posunie aj v sliceri. Následne Excel prepočíta report aj slicery podľa aktuálneho dátového modelu, a zobrazí report zafiltrovaný slicerom na hodnotu “dnes”. Keďže ale táto hodnota momentálne reprezentuje 12.11.2017, tak sa report aj slicer zobrazia správne:

A potom sa môžete vytešovať, ako sa Vám podarila ďalšia vychytávka,  a môžete prejsť na ďalšiu úlohu 🙂 Takto sa teda vytvára prednastavený slicer na dnešný dátum. A nielen to. Využitím fungovania a automatiky v Exceli a Power BI sa viete častokrát vyhnúť zdĺhavým riešeniam a programovaniu makier. A hádajte, kde sa to dá naučiť? 😉