Výpočet YTD, QTD a MTD v DAX

Tento příspěvek obsahuje postup pro vytvoření kumulativních součtů od začátku roku po aktuální den (YTD), kumulativních součtů od začátku čtvrtletní po aktuální den (QTD) a kumulativních součtů od začátku měsíce po aktuální den (MTD) za použití vestavěných časových DAX funkcí (Time intelligence funkce). Předpokladem pro použití funkcí časového měřítka v jazyku DAX je dostupnost úplné datumové tabulky v modelu a označení této tabulky jako "tabulky kalendářních dat". Základní předpoklady pro práci s vestavěnými časovými funkcemi jsou podrobněji popsány v samostatném příspěvku.

Příklady 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 tímto článkem.

Výpočet kumulativních součtů od začátku období k aktuálnímu datu

V příkladu budeme pracovat s měřítkem [Prodeje], které počítá sumu za prodané produkty ze sloupce Sales[Sales Amount] v aktuálním kontextu vyhodnocení.

Měřítko:

Prodeje = SUM(Sales[Sales Amount])

Měřítko vložíme do vizuálu Matrix, spolu s roky, kvartály, měsíci a dny v řádcích. Výchozí pohled může vypadat následovně. 

Nyní si můžeme postupně vytvořit jednotlivé výpočty, ve kterých budeme ovlivňovat výsledek měřítka [Prodeje] časovými funkcemi, které použijeme jako filtry ve funkci CALCULATE().

Výpočet kumulativních prodejů od začátku roku k aktuálnímu datu (YTD)

Prvním argumentem funkce CALCULATE() je obvykle měřítko nebo samotný výpočet, pro který chceme spočítat kumulativní prodeje od začátku roku k aktuálnímu dni. Druhým argumentem funkce CALCULATE() bude v případě YTD výpočtu funkce DATESYTD(). Argumentem funkce DATESYTD() je název sloupce, který obsahuje hodnoty ve formátu DATE nebo DATETIME a patří do správně naformátované kalendářní tabulky. Funkce DATESYTD() načte maximální datum dostupné v aktuálním kontextu vyhodnocení a vrátí tabulku s jedním sloupcem. Vrácená tabulka obsahuje všechna data od začátku roku po aktuální den a bude použita jako filtr při vyhodnocení výrazu v prvním argumentu funkce CALCULATE(). Před aplikováním filtru s tabulkou obsahující jednotlivé dny dojde na pozadí DAX funkce DATESYTD() k odstranění všech dříve aplikovaných filtrů z datumové tabulky 'Date', což je vlastnost typická pro téměř všechny vestavěné časové funkce v jazyku DAX.

Měřítko:

Prodeje YTD =

CALCULATE

(

[Prodeje],

DATESYTD('Date'[Date])

)

Nové měřítko můžeme vložit do připraveného vizuálu Matrix.

Měřítko [Prodeje YTD] vrací pro každý den sumu prodejů za všechny předchozí dny, které patří do aktuálního roku. Pokud se na následujícím obrázku podíváme na prodeje v rámci jednotlivých kvartálů, můžeme vidět, že měřítko vrací kumulativní prodeje v rámci aktuálního roku. Jakmile dojde k překročení nového roku, měřítko začne sčítat hodnoty znovu od začátku roku po aktuální dostupný den.

Při detailnějším pohledu na následující obrázek můžeme vidět typickou situaci, se kterou se můžeme setkat při tvorbě časových kalkulací. V tabulce 'Sales' je poslední záznam o prodejích z 15. června 2020. Měřítko [Prodeje] ve dnech následujících po 15. červnu správně nevrací žádnou hodnotu, protože již nejsou dostupné žádné hodnoty pro tyto dny. Nové měřítko [Prodeje YTD] ale vrací kumulativní sumu prodejů za rok 2020 i v dalších dnech po 15. červnu, ve kterých nedošlo k žádným prodejům. Hodnota měřítka [Prodeje YTD] je proto ve všech dnech následujících po 15. červnu stejná a opakuje se pro všechny následující dny, které jsou dostupné v kalendářní tabulce.

Pokud bychom chtěli zastavit počítání kumulativních prodejů u dnů, ve kterých nedošlo k žádným prodejům, máme několik možností jak toho dosáhnout. Ten asi nejjednodušší způsob je ověřit si pomocí funkce IF(), zda měřítko [Prodeje] vrací v aktuálním kontextu nějakou hodnot. Pokud ano, znamená to, že v aktuálním kontextu došlo k prodejům, a v tom případě necháme vypsat hodnotu měřítka [Prodeje YTD]. Pokud v aktuálním kontextu nedošlo k žádným prodejům, výsledek bude prázdná hodnota BLANK. Takto popsaný výpočet může vypadat následovně.

Měřítko:

Prodeje YTD (skryté budoucí jednoduché) =

IF

(

NOT ISBLANK( [Prodeje]),

CALCULATE

(

[Prodeje],

DATESYTD('Date'[Date])

),

BLANK()

)

Při pohledu na následující obrázek můžeme vidět rozdíl mezi původním měřítkem [Prodeje YTD] a měřítkem [Prodeje YTD (skryté budoucí jednoduché)]. 

Měřítko [Prodeje YTD] zobrazuje prodeje i v budoucích dnech, pro které ještě nemáme k dispozici žádné prodeje. Měřítko [Prodeje YTD (skryté budoucí jednoduché)] vrací hodnoty pouze ve dnech, ve kterých došlo k nějakým prodejům. 

Výše uvedený způsob ošetření zobrazení hodnot pro budoucí dny je dostačující pro výpočet kumulativních prodejů. Ve většině dalších časových kalkulací již ale tento způsob ošetření nebude fungovat. Proto si ukážeme ještě jiný přístup, který je více flexibilní a který můžeme aplikovat obecně na všechny další typy časových kalkulací.

Výpočet kumulativních prodejů (YTD) v otevřeném časovém období

Při práci s vestavěnými  časovými funkcemi v jazyku DAX je jedním ze základních předpokladů používat pro časové kalkulace úplnou tabulku kalendářních dat. Vzhledem k tomuto faktu pracujeme ve většině dnů v roce s modelem, ve kterém máme k dispozici neúplná data ve vztahu ke kalendářnímu roku, kvartálu nebo měsíci. Cílem je proto počítat pouze s těmi dny, pro které máme k dispozici hodnoty prodejů v datovém modelu. Časové kalkulace proto budeme omezovat pouze na dny, které jsou menší  než poslední dostupný den. Jaký je poslední dostupný den se však může lišit podle různých požadavků a podle struktury modelu. Může se jednat o poslední den, pro který máme dostupná data v tabulce prodejů. Může se jedna také o aktuální den, nebo o den, ve kterém došlo k aktualizaci dat ze zdrojové databáze. V použitém cvičném modelu jsou dostupná data pouze do 15. června 2020. V příkladu tedy použijeme jako filtr pro funkci DATESYTD() tento den jako konstantu. Nicméně v reálném modelu bychom definovali poslední dostupný den dynamicky, jak je naznačeno v definici měřítka níže, v zakomentovaných řádcích.

Měřítko:

Prodeje YTD (skryté budoucí) =

VAR MaxDate = DATE(2020,6,15)

--VAR MaxDate = TODAY()

--VAR MaxDate = TODAY() - 1

--VAR MaxDate = CALCULATE(MAX(Sales[Order Date]),REMOVEFILTERS())

VAR Vypocet =

CALCULATE

(

[Prodeje],

CALCULATETABLE

(

DATESYTD('Date'[Date]),

FILTER

(

VALUES('Date'[Date]),

'Date'[Date] <= MaxDate

)

)

)

RETURN

Vypocet

V měřítku [Prodeje YTD (skryté budoucí)] jsme v prvním kroku definovali maximální dostupný den, pro který chceme měřítko vyhodnotit, a který je uložen v proměnné MaxDate. Následuje samotný výpočet, kde je prvním argumentem funkce CALCULATE() měřítko [Prodeje]. Druhým argumentem funkce CALCULATE() je funkce CALCULATETABLE(), která má jako první argument funkci DATESYTD(). Funkce DATESYTD() je vyhodnocena v kontextu vnějších filtrů, ke kterým dále přidáváme filtr obsahující hodnoty ze sloupce 'Date'[Date] v aktuálním kontextu vyhodnocení, ale omezené pouze na hodnoty menší nebo rovno než námi definovaný poslední den, pro který chceme měřítko vyhodnotit. Jinak řečeno, funkce DATESYTD() načítá maximální dostupný den v aktuálním kontextu vyhodnocení. V měsíci červen 2020 by to byl 30. červen 2020. My k tomuto kontextu přidáme další filtr, který je striktnější, a obsahuje data pouze do 15. června 2020. Maximální dostupný den, který je k dispozici při vyhodnocení funkce DATESYTD() pro měsíc červen 2020, je proto 15. červen 2020. 16. června a všechny následující dny se vnější filtry a filtr definovaný uvnitř funkce CALCULATETABLE() navzájem vylučují, a výsledek celého měřítka je proto ve všech následujících dnech prázdná hodnota BLANK.

Jak je vidět na obrázku výše, měřítko [Prodeje YTD (skryté budoucí)] vrací hodnotu BLANK ve dnech následujících po 15. červnu 2020. V reálném modelu bychom poslední dostupný den museli vyhodnocovat dynamicky, jak je naznačeno v zakomentované části v definici měřítka.

Jak již bylo naznačeno dříve, možností, jak vyhodnocovat časové kalkulace pouze pro relevantní dny, je více. Výše uvedený postup může vypadat na první pohled složitě. Na druhou stranu tento postup přesně popisuje logiku, jak potřebujeme postupovat při omezení časových kalkulací. Stejného výsledku bychom dosáhli například také rozšířením datumové tabulky o nový počítaný sloupec, který by obsahoval v každém řádku pro každý den informaci, zda je aktuální datum menší nebo roven maximálním datu, pro který chceme časové kalkulace počítat. Takovýto sloupec bychom následně mohli použít pro filtrování časové funkce uvnitř funkce CALCULATETABLE(), namísto funkce FILTER(), použité v tomto příkladu.

Výpočet kumulativních prodejů od začátku čtvrtletí k aktuálnímu datu (QTD)

Výpočet kumulativního součtu v rámci čtvrtletí (QTD) je velmi podobný výpočtu kumulativních součtů v rámci let (YTD). Pouze namísto funkce DATESYTD() použijeme funkci DATESQTD(). Logika výpočtu je naprosto stejná, proto níže uvádím pouze definici měřítek, bez dalšího komentáře.

Měřítko:

Prodeje QTD =

CALCULATE

(

[Prodeje],

DATESQTD('Date'[Date])

)

Měřítko:

Prodeje QTD (skryté budoucí) =

VAR MaxDate = DATE(2020,6,15)

--VAR MaxDate = TODAY()

--VAR MaxDate = TODAY() - 1

--VAR MaxDate = CALCULATE(MAX(Sales[Order Date]),REMOVEFILTERS())

VAR Vypocet =

CALCULATE

(

[Prodeje],

CALCULATETABLE

(

DATESQTD('Date'[Date]),

FILTER

(

VALUES('Date'[Date]),

'Date'[Date] <= MaxDate

)

)

)

RETURN

Vypocet

Výsledek měřítka [Prodeje QTD (skryté budoucí)] v porovnání s měřítkem [Prodeje YTD (skryté budoucí)] je možné vidět na následujícím obrázku.

Měřítko [Prodeje QTD (skryté budoucí)] vrací kumulativní součty od začátku čtvrtletí k aktuálnímu datu. Měřítko [Prodeje YTD (skryté budoucí)] vrací kumulativní součty od začátku roku k aktuálnímu datu.

Výpočet kumulativních prodejů od začátku měsíce k aktuálnímu datu (MTD)

Při výpočtu kumulativních součtů v rámci měsíců můžeme použít funkci DATESMTD(), vše ostatní zůstává stejné, a proto uvádím pouze definici měřítek, bez dalšího komentáře.

Měřítko:

Prodeje MTD =

CALCULATE

(

[Prodeje],

DATESMTD('Date'[Date])

)

Měřítko:

Prodeje MTD (skryté budoucí) =

VAR MaxDate = DATE(2020,6,15)

--VAR MaxDate = TODAY()

--VAR MaxDate = TODAY() - 1

--VAR MaxDate = CALCULATE(MAX(Sales[Order Date]),REMOVEFILTERS())

VAR Vypocet =

CALCULATE

(

[Prodeje],

CALCULATETABLE

(

DATESMTD('Date'[Date]),

FILTER

(

VALUES('Date'[Date]),

'Date'[Date] <= MaxDate

)

)

)

RETURN

Vypocet

Na následujícím obrázku můžeme vidět srovnání měřítek  [Prodeje YTD (skryté budoucí)] , [Prodeje QTD (skryté budoucí)]  a [Prodeje MTD (skryté budoucí)] .

Měřítko  [Prodeje YTD (skryté budoucí)]  sčítá hodnoty od začátku roku k aktuálnímu datu. Měřítko  [Prodeje QTD (skryté budoucí)] sčítá hodnoty od začátku každého čtvrtletí po aktuální den a  [Prodeje MTD (skryté budoucí)] od začátku každého měsíce po aktuální den.

Shrnutí

Výše uvedené ukázky jsou pouze jedním z více způsobů, jak můžeme dosáhnout stejného výsledku. Také způsob ošetření zobrazování hodnot pro budoucí dny může být proveden i jinými technikami. V tomto článku se proto jedná pouze ukázku jednoho z více možných přístupů k vytvoření kumulativních součtů od začátku vybraného období k aktuálnímu dnu pomocí jazyka DAX a časových funkcí.

Stáhnout soubor s řešením.
č. 35