Tento článek obsahuje popis základních předpokladů, které jsou důležité pro správné fungování Time Intelligence funkcí v jazyku DAX. Níže v příspěvku můžete najít také stručný popis, jak tyto funkce fungují a jaká mají omezení. Praktické příklady Time intelligence výpočtů pak můžete najít na stránce DAX - Příklady.
Rozdíl mezi Date and time funkcemi a Time intelligence funkcemi
Než se posuneme k hlavnímu tématu tohoto příspěvku, bude dobré vyjasnit si dva důležité pojmy, které mohou způsobovat nejasnosti při tvorbě výpočtů v jazyku DAX. V oficiální dokumentaci k jazyku DAX jsou dvě kategorie funkcí, které mohou být na první pohled zaměnitelné. Jedná se o funkce v kategorii Date and Time (Funkce data a času) a funkce Time Intelligence (Funkce časového měřítka).
Funkce data a času (Date and time)
Funkce data a času (Date and time) jsou funkce velmi podobné se stejnými funkcemi v Excelu. Výsledkem těchto funkcí mohou být hodnoty s různými datovými typy. Například funkce YEAR() vrací číslo představují rok, funkce NOW() vrací aktuální datum ve formátu DATETIME, atd. Všechny Date and time funkce mohou být velmi užitečné pro určité typy výpočtů. Tyto funkce ale nemůžeme použít přímo jako filtry ve funkci CALCULATE() nebo CALCULATETABLE(), protože tyto funkce obvykle vrací skalární hodnoty bez vazby na data v modelu (Lineage Tag*).
*Pokud chceme filtrovat model, což je také případ Time intelligence výpočtů kde manipulujeme s časem, musíme jako filtry použít hodnoty z konkrétního sloupce. Hodnoty, které nepatří do žádného konkrétního sloupce (nemají Lineage Tag shodný s některým ze sloupců v modelu), nemůžeme použít přímo jako filtry pro CALCULATE() a CALCULATETABLE(). Hodnoty, které nepatří do žádného sloupce, můžeme použít nepřímo jako filtry, to znamená jako argumenty jiných funkcí, které již budou vracet hodnoty s vazbou na konkrétní sloupce v modelu. Tento popis vlastnosti Lineage Tag je velmi zjednodušený. Více informací o vlastnosti Lineage Tag a o způsobu jak s touto vlastností můžeme v DAX výpočtech manipulovat najdete v samostatném příspěvku pod tímto odkazem.
Funkce časového měřítka (Time intelligence)
Samostatnou kategorii funkcí v jazyku DAX tvoří funkce Časového měřítka (Time intelligence funkce). Tyto funkce vrací, až na několik málo výjimek*, tabulky s jedním sloupcem a hodnotami ve formátu DATE. Tato tabulka s jedním sloupcem může obsahovat jeden nebo více řádků. Výsledné hodnoty mají vazbu na konkrétní sloupec, a můžeme je proto použít přímo jako filtry ve funkci CALCULATE() a CALCULATETABLE().
Celý tento příspěvek je věnován právě Time intelligence funkcím a především základním předpokladům, které je třeba splnit pro správné fungování těchto funkcí.
*Výjimku tvoří například funkce TOTALYTD() a její ekvivalenty pro měsíc (TOTALMTD()) a kvartál (TOTALQTD()). Tyto funkce sice na pozadí používají standartní Time intelligence funkce, ale vrací přímo skalární hodnotu. V jazyku DAX jsou tyto funkce k dispozici pro zjednodušení zápisu některých často používaných vzorů. Například funkce TOTALYTD() používá na pozadí kombinaci funkcí CALCULATE() a DATESYTD(). Tyto pro uživatele jednodušší funkce, které jsou na pozadí vyhodnoceny pomocí dalších DAX funkcí, obecně označujeme jako syntaktické zkratky.
Předpoklady pro práci s funkcemi časového měřítka v jazyku DAX
Při práci s Time intelligence funkcemi je důležité mít v modelu alespoň jednu úplnou datumovou tabulku a tuto tabulku mít označenou jako tabulku kalendářních dat.
1. Úplná kalendářní tabulka
Při práci s funkcemi časového měřítka je důležité mít v modelu úplnou datumovou tabulku. Úplná datumová tabulka je taková tabulka, která obsahuje všechny dny v letech, ve kterých chceme pracovat s časovými kalkulacemi. Pokud například pracujeme s obdobím v rozmezí od 25. 7. 2018 do 7. 9. 2020, datumová tabulka by měla obsahovat sloupec ve formátu DATE se všemi dny od 1. 1. 2018 do 31. 12. 2020. Pokud pracujeme s fiskálními roky, pak by měla datumová tabulka obsahovat všechny dny v uvažovaných fiskálních letech, v rozmezí od začátku prvního fiskálního roku po konec posledního fiskálního roku. V obou případech, jak v tabulce pro fiskální roky, tak v tabulce pro kalendářní roky, musí být k dispozici sloupec ve formátu DATE, který bude obsahovat jedinečné hodnoty bez duplicit a bez mezer mezi jednotlivými dny.
Jeden ze způsobů, jak si můžeme jednoduše vytvořit úplnou kalendářní tabulku pomocí DAX funkcí, je popsán v níže odkazovaném článku.
https://www.tkadlcikpetr.cz/2021/09/dax-vytvoreni-datumove-tabulky-funkce.html
Pozn.: Sloupec obsahující všechny dny v zamýšlených letech může být také ve formátu DATETIME, nicméně TIME hodnoty musí být v tomto případě pro každý datum stejné.
2. Tabulka označená jako tabulka kalendářních dat
Datumová tabulka by dále měla být označena jako "tabulka kalendářních dat". Pokud se pokusíme označit tabulku jako "tabulku kalendářních dat", před dokončením je v druhém kroku nutné vybrat sloupec ve formátu DATE nebo DATETIME, který obsahuje jedinečné hodnoty pro každý řádek tabulky. Pokud se v dialogovém okně tento sloupec nenabízí, znamená to, že tabulka není ve správném formátu pro použití jako "tabulka kalendářních dat". Tabulku nemusíme označovat jako "tabulku kalendářních dat" v případě, kdy jsou pro relace mezi datumovou tabulkou a ostatními tabulkami použity jako klíče sloupce ve formátu DATE. Nicméně i v tomto případě je dobré označit datumovou tabulku jako "tabulku kalendářních dat".
Jednou z možností, jak v Power BI označit tabulku jako tabulku kalendářních dat, je kliknutím pravým tlačítkem na kalendářní tabulku, a v dialogovém okně zvolit možnost "Mark as date table".
V druhém kroku vybereme sloupec, který obsahuje hodnoty ve formátu DATE.
Podobným způsobem lze označit datumovou tabulku také v Power Pivot v Excelu, kde je k tomuto účelu k dispozici samostatné tlačítko nazvané "Označit jako tabulku kalendářních dat".
Pokud máme tabulku označenou jako tabulku kalendářních dat, nebo pokud máme relace mezi kalendářní tabulkou a ostatními tabulkami nastavené přes sloupce ve formátu DATE nebo DATETIME, pak DAX přidá automaticky na pozadí každé Time intelligence funkce funkci REMOVEFILTERS(), ve které bude uveden název datumové tabulky použité pro aktuální Time intelligence výpočet. Tato vlastnost zjednodušuje práci s časovými funkcemi a je dobré o ní vědět, abychom přesněji porozuměli tomu jak funkce časového měřítka fungují.
3. Použití správného sloupce v Time intelligence funkcích
V Tabulárním modelu pracujeme obvykle s více tabulkami. Může tedy nastat situace, kdy je v modelu více sloupců v různých tabulkách s hodnotami ve formátu DATE nebo DATETIME. Proto si musíme dávat pozor na to, které sloupce používáme v Time intelligence funkcích. V Time intelligence funkcích bychom měli vždy používat pouze sloupce z datumové tabulky, která splňuje předchozí dva předpoklady. Pokud máme například v tabulce prodejů sloupec ve formát DATE u každé objednávky, tento sloupec není v současné době vhodný pro Time intelligence funkce, protože tabulka, ve které se tento sloupec nachází, obvykle nesplňuje předchozí dva požadavky. Time intelligence funkce používáme pouze se sloupcem ve formátu DATE nebo DATETIME z tabulky, která obsahuje úplné roky, má pouze jeden řádek pro každý datum, nemá mezery mezi jednotlivými dny a je označena jako tabulka kalendářních dat. Tato tabulka je pak propojena relacemi s ostatními tabulkami nahranými v modelu.
Pokud je ve faktové tabulce více sloupců s datumy, a potřebujeme v různých výpočtech používat různé sloupce, například datum objednávky, datum odeslání zboží atd., pak můžeme vytvořit mezi kalendářní tabulkou a faktovou tabulkou více relací, a požadovanou relaci pak aktivovat v době výpočtu pomocí funkce USERELATIONSHIP(). Druhým přístupem pak může být vytvoření více kalendářních tabulek, pro každý jeden datum ve faktové tabulce jednu datumovou tabulku.
Oba přístupy mají své výhody i nevýhody, nicméně pořád platí, že pro Time intelligence funkce bychom měli používat vždy sloupec ve formátu DATE nebo DATETIME z kalendářní tabulky, která je správně naformátovaná.
Co jsou to Time intelligence funkce v jazyku DAX
Time intelligence funkce jsou až na výjimky funkce vracející tabulky s jedním sloupcem, který obsahuje jednu nebo více hodnot ve formátu DATETIME. Pokud například použijeme funkci PREVIOUSYEAR(), tak výsledkem funkce nebude jedna hodnota obsahující předchozí rok, ale tabulka s jedním sloupcem a všemi dny v předchozím roce ve formátu DATE nebo DATETIME. Dalším příkladem může být funkce SAMEPERIODLASTYEAR(), která načte datumy z aktuálního kontextu, a vrátí stejný rozsah datumů posunutý o jeden rok zpět. Pokud je například funkce SAMEPERIODLASTYEAR() vyhodnocena v kontextu filtru celého měsíce únor 2019, vrátí všechny dny z měsíce únor 2018.
Time intelligence funkce obvykle používáme jako filtry ve funkci CALCULATE() pro ovlivnění výsledku výpočtu v prvním argumentu této funkce. Existují ale i speciální časové funkce, které můžeme používat přímo bez použití funkce CALCULATE(), například funkce TOTALYTD() nebo funkce CLOSINGBALANCEYEAR() a jejich ekvivalenty pro kvartály a měsíce.
Časové funkce je bezpečné používat na úrovni let, kvartálů a měsíců. V jazyku DAX neexistují vestavěné časové funkce pro manipulaci s daty na úrovni týdnů. Pokud bychom chtěli porovnávat hodnoty na úrovni týdnů, musíme si vytvořit speciální datumovou tabulku s pomocnými sloupci a výpočty tvořit pomocí základních DAX funkcí, kterými jsou například funkce FILTER(), VALUES(), ALL() atd. Příklady časových kalkulací vytvořených pro práci s ISO týdenním kalendářem najdete v samostatných příspěvcích na této stránce.
Dále, pokud chceme porovnávat hodnoty na úrovni dnů, je lepší používat základní DAX funkce a speciální pomocné sloupce v datumové tabulce. Tato omezení jsou dána strukturou Gregoriánského kalendáře, kdy měsíce mají různé počty dnů, pracovní týdny mohou začínat a končit v různých kalendářních letech atd. Všechny tyto vlastnosti standardního kalendáře jsou spolehlivě ošetřeny, pokud pracujeme v kalkulacích na úrovni roků, kvartálů a měsíců.
Omezení při výpočtech na úrovni dnů
Na krátké ukázce si můžeme znázornit, jaké výsledky mohou v krajním případě nastat při porovnávání hodnot na úrovni dnů. Pokud bychom chtěli porovnávat prodeje v jednotlivých dnech s hodnotami prodejů za předchozí měsíc, tak pro 31. březen 2019 bychom pomocí funkce DATEADD() mohli dostat hodnotu prodejů z 28. února 2019. V obou případech se jedná o poslední den v měsíci, což může být ještě v pořádku. Nicméně pokud se podíváme na výsledek stejného výpočtu pro 30. březen 2019, opět dostaneme prodeje z 28. února 2019, atd., až do 28. března 2019.
Tento fakt je dán tím, že v únoru 2019 není k dispozici 29, 30 a 31 den. Funkce DATEADD() tak vrací poslední dostupný den v únoru pro všechny čtyři dny, od 28. března do 31. března. Na úrovni měsíců, čtvrtletní a roků jsou tyto anomálie ošetřeny a časové funkce fungují s úplným a správně formátovaným kalendářem spolehlivě.
Nicméně pro práci na úrovni dnů, týdnů, nebo jiných časových úseků je lepší pracovat s kalendářní tabulkou obsahující speciální pomocné sloupce. Tyto sloupce pak můžeme používat pro výpočty vytvořené pomocí základních DAX funkcí.
Shrnutí
Základním předpokladem pro práci s Time intelligence funkcemi je mít v modelu úplnou tabulku kalendářních dat a tuto tabulku mít označenou jako "tabulku kalendářních dat". Označit tabulku jako "tabulku kalendářních dat" můžeme pomocí přímého nastavení v metadatech modelu, nebo vytvořením relace mezi tabulkami na základě sloupce ve formátu DATE. Při práci s Time intelligence funkcemi pak DAX automaticky na pozadí přidává funkci REMOVEFILTERS() s argumentem ve formě názvu datumové tabulky, což umožňuje zjednodušený zápis jednotlivých funkcí časového měřítka.
Time intelligence funkce obvykle používáme jako filtry ve funkci CALCULATE(). Tyto funkce přijímají jeden nebo více argumentů, mezi kterými je vždy sloupec ve formátu DATE z datumové tabulky. Time intelligence funkce vrací, až na výjimky, tabulku obsahující jeden sloupec s hodnotami ve formátu DATE. Tuto tabulku s jedním sloupcem ve formátu DATE obvykle používáme jako filtr pro ovlivnění prvního argumentu ve funkci CALCULATE().
Praktické příklady časových kalkulací můžete najít na stránce DAX - Příklady.
Komentáře
Okomentovat