Časové kalkulace v DAX

Úvodní obrázek

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é s 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 (Data lineage*).

*Pokud chceme filtrovat model, musíme jako filtry použít hodnoty z konkrétního sloupce. Hodnoty, které nepatří do žádného konkrétního sloupce (nemají Data lineage), 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 (hodnoty s Data lineage). Tento popis pojmu Data lineage je velmi zjednodušený. Detailní popis Data lineage je nad rámec tématu tohoto příspěvku a vydal by na samostatný článek.

Funkce časového měřítka (Time intelligence)

Samostatnou kategorií funkcí v jazyku DAX jsou funkce Časového měřítka (Time intelligence). Tyto funkce vrací, až na několik málo výjimek*, tabulky s jedním sloupcem ve formátu DATETIME. Tento sloupec může obsahovat jednu nebo více hodnot. 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ě předpokladům pro práci funkcemi Časového měřítka (Time intelligence).

*Výjimku tvoří například funkce TOTALYTD() a její ekvivalenty pro měsíc a kvartál. Tyto funkce sice na pozadí používají standartní Time intelligence funkce, ale vrací skalární hodnotu. V jazyku DAX jsou tyto funkce k dispozici zřejmě pro zjednodušení zápisu vzorců. Například funkce TOTALYTD() používá na pozadí kombinaci funkcí CALCULATE() a DATESYTD(). 

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

Pro 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 daty  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 nebo DATETIME, který bude obsahovat jedinečné hodnoty bez duplicit.

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 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í vhodně formátovaná 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".

Označní kalendářní tabulky v Power BI

V druhém kroku vybereme sloupec, který obsahuje hodnoty ve formátu DATE.

Označní kalendářní tabulky v Power BI 2

Podobným způsobem lze označit datumovou tabulku také v Excelu, kde je k tomuto účelu k dispozici samostatné tlačítko přímo v Power Pivotu. 

Označení kalendářní tabulky v Excel Power Pivot

Pokud máme tabulku označenou jako tabulku kalendářních dat, nebo pokud máme vazby mezi kalendářní tabulkou a ostatními tabulkami nastavené přes sloupce ve formátu DATE, pak DAX přidá automaticky na pozadí funkci REMOVEFILTERS('Název datumové tabulky') do všech výpočtů, ve kterých použijeme některou z vestavěných časových funkcí pro manipulaci s filtry. Tato vlastnost zjednodušuje práci s časovými funkcemi a je dobré o ní vědět, abychom přesněji porozuměli 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. V takové situaci si musíme dávat pozor na sloupce, které 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 ze sloupcem ve formátu DATE nebo DATETIME z tabulky, která obsahuje úplné roky, má pouze jeden řádek pro každý datum, a je označena jako tabulka kalendářních dat. Tato tabulka je pak propojena relacemi s ostatními tabulkami.   

Správný sloupec pro Time intelligence funkce

Pokud je v cílové tabulce více sloupců ve formátu DATE, a potřebujeme v různých výpočtech používat různé datumy, například datum objednávky, datum odeslání zboží atd., pak můžeme vytvořit mezi kalendářní tabulkou a cílovou 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 v cílové tabulce. 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 všech dnů v předchozím roce s hodnotami ve formátu 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 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

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.

Time intelligence funkce na úrovni dnů

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ě.

Time intelligence funkce na úrovni měsíců

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 tabulky 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.

č. 34

Komentáře