Jazyk DAX obsahuje celou řadu funkcí, které jsou určeny pro manipulaci s časem při tvorbě výpočtů. Pro plnohodnotnou práci s funkcemi časového měřítka (Time intelligence funkce) se mimo jiné doporučuje mít v modelu alespoň jednu datumovou tabulku. Tento příspěvek obsahuje vzory pro vytvoření datumové tabulky pomocí DAX výrazů, které lze použít pro vytvoření nové počítané tabulky například v Power BI.
(Aktualizováno 5. 8. 2023)
Všechny způsoby vytvoření kalendářní tabulky zobrazené v tomto příspěvku jsou vytvořeny ve cvičném Power BI souboru Adventure Works DW 2020.pbix, který je volně dostupný ke stažení na internetu. Soubor s řešením je dostupný ke stažení níže pod příspěvkem.
Vytvoření kalendářní tabulky v Power BI
Standardní datumová tabulka se standardním gregoriánským kalendářem by měla v kontextu dat v modelu obsahovat úplné roky. Datumová tabulka s úplnými kalendářními roky je taková tabulka, která obsahuje všechny dny v daném roce od 1.1 do 31.12., bez mezer a bez duplicit. Pokud pracujeme v modelu s fiskálními roky, nemusí jeden úplný rok odpovídat kalendářnímu roku. Úplný fiskální rok proto může být například rok obsahující všechny dny od 1.7.2017 do 30.6.2018.
V případě, že nemáme kalendářní tabulku k dispozici ve zdrojové databázi, můžeme si ji vytvořit pomocí DAX funkcí CALENDAR() nebo CALENDARAUTO(). Obě tyto funkce vygenerují jeden sloupec s hodnotami ve formátu DATE. Všechny ostatní potřebné sloupce, jako jsou například roky, měsíce nebo dny v týdnu, můžeme následně snadno dopočítat na základě tohoto vygenerovaného sloupce.
Kalendářní tabulka a funkce CALENDAR
Funkce CALENDAR() má dva povinné argumenty - počáteční datum a koncové datum. Výsledkem funkce je tabulka s jedním sloupcem s hodnotami ve formátu DATE, v rozmezí podle zadaných argumentů.
Počítaná tabulka:
Pokud na základě výše uvedeného výpočtu vytvoříme v Power BI novou počítanou tabulku, výsledek bude vypadat následovně.
Takto vytvořená tabulka má pouze jeden sloupec, který obsahuje datumy v rozmezí od 1.1. 2018 do 31.12.2021. Na základě tohoto sloupce můžeme podle potřeby dopočítat další sloupce - například roky, názvy měsíců, názvy dnů a tak dále. Možností, jak vytvořit další počítané sloupce je více. Jednou z nejjednodušších cest je přidání dalších sloupců pomocí funkce ADDCOLUMNS(). Díky této funkci můžeme přidat libovolné množství sloupců v jednom kroku. Každý počítaný sloupec vychází v každém řádku tabulky z hodnoty ve sloupci [Date], který je výsledkem funkce CALENDAR().
Počítaná tabulka:
Výslednou tabulku můžeme následně propojit s ostatními tabulkami v modelu pomocí relací. Relaci můžeme vytvořit buď na základě sloupce 'Date'[Date], případně na základě sloupce 'Date'[DateKey], který obsahuje celočíselné hodnoty ve formátu "YYYYMMDD". Sloupce s číselným vyjádřením vybraného atributu mohou být použity pro řazení sloupců s textovými popisy. Například sloupec 'Date'[Měsíc] je ve formátu text. Pokud bychom chtěli v reportu zobrazit měsíce ve správném pořadí, v metadatech sloupce nastavíme řazení hodnot podle sloupce 'Date'[Měsíc číslo].
Poznámka: Ve funkci FORMAT() je v některých příkladech v tomto příspěvku vyplněn třetí argument pro určení národního prostředí. Tento argument je možné používat pouze v Power BI. V Excelu nebo ve Visual Studiu při tvorbě SSAS Tabulárního modelu tento třetí argument ve funkci FORMAT() nelze použít, protože tyto nástroje dědí defaultní národní prostředí.
Kalendářní tabulka s funkcí CALENDAR dynamicky
Datové modely v Power BI obsahují obvykle více tabulek, jejichž součástí mohou být také sloupce s hodnotami ve formátu DATE nebo DATETIME. V případě, že již model obsahuje sloupec nebo více sloupců s hodnotami ve formátu DATE nebo DATETIME, ze kterých můžeme při generování datumové tabulky vycházet, lze funkci CALENDAR() použít dynamicky. V použitém cvičném modelu můžeme pro tento účel použít sloupec 'Sales'[Order Date], který obsahuje datum uskutečnění objednávky. Z tohoto sloupce si můžeme vybrat dynamicky první a poslední rok, ve kterých došlo k objednávkám produktů. Tyto hraniční roky použijeme v prvním argumentu funkce DATE(). Druhý a třetí argument funkce DATE() pak doplníme konstantami. Pro počáteční datum použijeme první den v roce, a pro koncové datum použijeme poslední rok v roce.
Počítaná tabulka:
K takto vygenerovanému sloupci s hodnotami ve formátu DATE můžeme opět přidat další sloupce pomocí funkce ADDCOLUMNS(), stejně jako tomu bylo v předchozím případě.
Počítaná tabulka:
Takto vytvořená kalendářní tabulka bude při každém načtení dat přepočítána a pokud budou načtena data pro nový rok, nový rok se automaticky přidá do kalendářní tabulky. Další možností, jak vygenerovat datumovou tabulku, je použití funkce CALENDARAUTO(). Předpokladem pro použití funkce CALENDARAUTO() je také existence alespoň jednoho sloupce s hodnotami ve formátu DATE nebo DATETIME.
Funkce CALENDARAUTO
Funkce CALENDARAUTO() najde automaticky první a poslední datum v modelu. Prohledávány jsou všechny sloupce, které obsahují hodnoty ve formátu DATE nebo DATETIME, kromě počítaných sloupců. Výsledkem CALENDARAUTO() je opět jeden sloupec s hodnotami ve formátu DATE, stejně jako tomu bylo u funkce CALENDAR(). Tyto hodnoty pokrývají celý rozsah všech stávajících datumů v celém modelu. Pokud jsou například v modelu také datumy narození zákazníků, a nejstarší zákazník se narodil 4.8.1950 a současně poslední prodaný produkt byl prodán 1.5.2022, datumová tabulka vygenerovaná funkcí CALENDARAUTO() bude obsahovat všechny datumy od 1.1.1950 do 31.12.2022.
V souboru Adventure Works DW 2020.pbix jsou k dispozici hodnoty ve formátu DATE pouze v tabulce 'Sales', a to v rozmezí od 1.7.2017 do 30.6.2021. Výsledkem funkce CALENDARAUTO() je proto sloupec se dny od 1.1.2017 do 31.12.2021.
Počítaná tabulka:
Rozsah vrácených hodnot funkce CALENDARAUTO() tedy závisí na rozsahu všech datumů v celém modelu tak, aby kalendářní tabulka pokryla všechny potřebné dny, které mohou být hypoteticky zahrnuty do výpočtů.
Stejným způsobem jako u funkce CALENDAR() můžeme i nyní doplnit další počítané sloupce pomocí funkce ADDCOLUMNS().
Počítaná tabulka:
Funkci CALENDARAUTO() můžeme použít také pro vytvoření datumové tabulky určené pro výpočty v rámci fiskálních let, jak je popsáno v následující části.
Funkce CALENDARAUTO a fiskální roky
Ve funkci CALENDARAUTO() můžeme použít celočíselný argument v rozmezí 1 - 12. Vložené číslo představuje poslední kalendářní měsíc ve fiskálním roku. Pokud například vložíme jako argument číslo 6, výsledkem bude datumová tabulka obsahující fiskální roky se všemi dny od 1.7. do 30. 6. Počet let, které bude takto vytvořená tabulka zahrnovat, je opět dán prvním a posledním dnem, pro který máme v modelu hodnoty ve formátu DATE nebo DATETIME. Jednotlivé sloupce již ale budou muset být vytvořeny jinými výpočty, než tomu bylo u standartního kalendáře. Například sloupec s fiskální roky bude nabývat jiných hodnot než sloupec obsahující kalendářní roky atd.
Přístupů, jak vytvořit kalendářní tabulku je více. Z výkonových důvodů a z důvodu efektivnější komprese dat je vždy lepším řešením načítat datumovou tabulku, pokud je to možné, přímo ze zdrojové databáze. Současně, argument o lepší kompresy dat je relevantní pouze v případě rozsáhlých tabulek. Pokud v modelu pracujeme pouze s desítkami let, vytvoření kalendářní tabulky pomocí DAX výrazů bude mít zanedbatelný dopad na efektivitu výpočtů. Výhodou vytvoření kalendářní tabulky pomocí DAX výrazů uvedených v tomto příspěvku je jednoduchost a snadná škálovatelnost tabulky v případě požadavků na dodatečné úpravy nebo na doplnění kalendářní tabulky o další sloupce.
Všechny sloupce, které jsou ve výše uvedených ukázkách přidány do kalendářní tabulky pomocí funkce ADDCOLUMNS() jsou pouze ukázkové. Každý autor si může tyto sloupce upravit nebo doplnit o další sloupce, které chce mít v modelu k dispozici.
Praktické příklady Time intelligence výpočtů můžete najít na stránce DAX - Příklady. K dispozici je také článek obsahující postup pro vytvoření ISO týdenního kalendáře, který můžete najít pod tímto odkazem.
Děkuju za srozumitelné vysvětlení, s tímhle jsem se natrápil, než jsem našel tyhle super stránky! Díky moc! Tomáš D.
OdpovědětVymazatJsem rád že Vám článek pomohl a děkuji za komentář.
Vymazat