Výpočet RT, YTD, QTD a WTD v DAX s ISO týdenním kalendářem

V jazyku DAX jsou k dispozici pokročilé funkce časového měřítka (Time intelligence funkce), díky kterým můžeme relativně jednoduše vytvořit výpočty s kumulativním součtem od začátku roku po aktuální den (YTD), od začátku čtvrtletí po aktuální den (QTD) nebo od začátku měsíce po aktuální den (MTD). Jednou z podmínek pro použití Time intelligence funkcí v jazyku DAX je práce s kalendářní tabulkou, která je správně naformátovaná a obsahově odpovídá standardnímu Gregoriánskému kalendáři. Pokud pracujeme s vlastním kalendářem, který se v jakémkoliv směru odchyluje od standardního kalendáře, musíme se v současné době spolehnout na základní DAX funkce. Tento článek obsahuje výpočty kumulativních součtů v různých časových obdobích pomocí základních DAX funkcí při práci s vlastním ISO týdenním kalendářem.

Kumulativní součty s ISO týdenním kalendářem

Příklady v tomto příspěvku jsou vytvořeny ve cvičném Power BI souboru Adventure Works DW 2020.pbix, který je navíc doplněný o vlastní ISO týdenní kalendář. Soubor s řešením, včetně ISO týdenního kalendáře, je dostupný ke stažení níže pod tímto příspěvkem. Základní informace a postup, jak vytvořit vlastní ISO týdenní kalendář pomocí DAX funkcí, můžete najít v samsotatném příspěvku pod tímto odkazem.

V příkladech uvedených v tomto příspěvku si postupně vytvoříme výpočet kumulativních součtů přes celé období (RT), výpočet kumulativních součtů od začátku ISO roku po aktuální den (YTD), kumulativních součtů od začátku ISO čtvrtletí po aktuální den (QTD) a od začátku ISO týdne po aktuální den (WTD). V příkladech budeme také pracovat s měřítkem [Prodeje], které má následující definici.

Měřítko:

Prodeje = SUM(Sales[Sales Amount])

Měřítko [Prodeje] vrací sumu za prodané produkty v aktuálním kontextu vyhodnocení. Pokud nové měřítko vložíme do vizuálu Matrix, s ISO roky, ISO kvartály a ISO týdny v řádcích vizuálu, výchozí report může vypadat následovně.

Hierarchie v řádcích vizuálu obsahuje ISO roky, ISO čtvrtletí, ISO týdny a dny. Všechny tyto atributy jsou součástí kalendářní tabulky v použitém modelu, který je dostupný ke stažení níže pod tímto příspěvkem. Nyní, když máme připravené prostředí, můžeme přistoupit k samotným výpočtům časových kalkulací.

Výpočet RT s ISO týdenním kalendářem

Výpočet kumulativního součtu přes celé období po aktuální den (Running Total) s ISO týdenním kalendářem se příliš neliší od stejného typu výpočtu při použití standardního kalendáře, protože pro tento typ výpočtu není v jazyku DAX k dispozici žádná speciální Time intelligence funkce. Stejně jako při práci se standardním kalendářem budeme chtít vytvořit ve funkci CALCULATE() filtr, který bude obsahovat všechny dny, které jsou menší nebo rovno jako je poslední dostupný den v aktuálním kontextu vyhodnocení. Na rozdíl od práce se standardním kalendářem pak navíc do filtru ve funkci CALCULATE() přidáme funkci REMOVEFILTERS() s názvem ISO kalendářní tabulky, a to z důvodu odstranění všech vnějších filtrů nastavených na sloupce z této tabulky. Při práci s vlastním kalendářem je tento dodatečný krok nezbytný pro většinu výpočtů, protože se nemůžeme spoléhat na automatické odstranění filtrů z kalendářní tabulky, jako je tomu při práci se standardní datumovou tabulkou, zejména v případě kdy je standardní kalendářní tabulka označena jako "tabulka kalendářních dat". Samotný výpočet kumulativního součtu přes celé období (RT) s ISO týdenním kalendářem tak může vypadat následovně.

Měřítko:

Prodeje RT (jednoduché) =

VAR MaxDenAktualniKontext = MAX('Date'[Date])

VAR Vypocet =

    CALCULATE

    (

        [Prodeje],

        REMOVEFILTERS('Date'),

        'Date'[Date] <= MaxDenAktualniKontext

    )

RETURN

    Vypocet

Nové měřítko [Prodeje RT (jednoduché)] si můžeme vložit do dříve připraveného vizuálu, výsledek pak může vypadat následovně.

Jak je možné vidět na obrázku výše, měřítko [Prodeje RT (jednoduché)] vrací v každém řádku zobrazeného vizuálu sumu za prodané produkty od prvního dne ve kterém došlo k prodeji produktů, až po poslední den dostupný v aktuálním kontextu vyhodnocení. Měřítko [Prodeje RT (jednoduché)] můžeme ještě vylepšit tak, aby nezobrazovalo hodnoty ve dnech, ve kterých ještě nedošlo k žádným prodejům. Tento typický problém většiny časových kalkulací můžeme vidět na následujícím obrázku.

V použitém cvičném modelu jsou dostupné data o prodejích pouze do 15. června 2020. To by měl být poslední den, ve kterém bude měřítko s kumulativními součty vracet výsledky. Všechny následující dny jsou považovány za budoucnost, protože pro tyto dny ještě nemáme k dispozici informace o prodejích. V následující verzi výpočtu RT si proto ještě před zobrazením výsledku výpočtu pomocí funkce IF() ověříme, jestli jsou v aktuálním kontextu vyhodnocení dostupná data o prodejích ve faktové tabulce 'Sales'.

Měřítko:

Prodeje RT =

VAR MaxDenAktualniKontext = MAX('Date'[Date])

VAR Vypocet =

    CALCULATE

    (

        [Prodeje],

        REMOVEFILTERS('Date'),

        'Date'[Date] <= MaxDenAktualniKontext

    )

VAR Vysledek =

    IF

    (

        NOT ISEMPTY('Sales'),

        Vypocet

    )

RETURN

    Vysledek

Jak můžeme vidět na obrázku níže, nové měřítko [Prodeje RT] již vrací výsledky pouze ve dnech, ve kterých jsou již k dispozici záznamy o prodejích.

V následující části je zobrazen výpočet kumulativních prodejů od začátku roku po aktuální den (YTD).

Výpočet YTD s ISO týdenním kalendářem

Měřítko s kumulativním součtem prodejů od začátku roku po aktuální den (Year To Date) bude obsahovat, na rozdíl od předchozího výpočtu, navíc filtr, který bude omezovat rozsah období pouze na aktuální rok, ve kterém je měřítko vyhodnoceno. Kombinací aktuálního roku a všech dnů, které jsou menší než je poslední den dostupný v aktuálním kontextu vyhodnocení, dostaneme požadovaný výsledek.

Měřítko:

Prodeje YTD =

VAR MaxRokAktualniKontext = MAX('Date'[ISO rok číslo])

VAR MaxDenAktualniKontext = MAX('Date'[ISO den v roce])

VAR Vypocet =

    CALCULATE

    (

        [Prodeje],

        REMOVEFILTERS('Date'),

        'Date'[ISO rok číslo] = MaxRokAktualniKontext,

        'Date'[ISO den v roce] <= MaxDenAktualniKontext

    )

VAR Vysledek =

    IF

    (

        NOT ISEMPTY(Sales),

        Vypocet

    )

Return

    Vysledek

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

Jak můžeme vidět na obrázku výše, měřítko [Prodeje YTD] vrací kumulativní součet prodejů od začátku roku po poslední dostupný den v aktuálním kontextu vyhodnocení. V ISO roce 2017 je ještě výsledek měřítka [Prodeje RT] a měřítka [Prodeje YTD] stejný. V dalším roce měřítko [Prodeje YTD] začíná počítat sumu prodejů znovu od začátku roku, zatímco měřítko [Prodeje RT] pokračuje ve sčítání hodnot z předchozího roku.

Výpočet QTD s ISO týdenním kalendářem

Při výpočtu kumulativního součtu od začátku čtvrtletí (Quarter To Date) po aktuální den můžeme opět vycházet z předcházejícího výpočtu kumulativního součtu od začátku roku po aktuální den, pouze s tím rozdílem, že ve filtru ve funkci CALCUALTE() použijeme namísto čísla roku filtr s pořadovým číslem čtvrtletí, které je pro každé čtvrtletí jedinečné napříč všemi dostupnými roky. Celý výpočet může vypadat následovně.

Měřítko:

Prodeje QTD =

VAR MaxDenAktualniKontext = MAX('Date'[ISO den v roce])

VAR MaxCtvrtletiAktualniKontext = MAX('Date'[ISO čtvrtletí pořadí])

VAR Vypocet =

    CALCULATE

    (

        [Prodeje],

        REMOVEFILTERS('Date'),

        'Date'[ISO den v roce] <= MaxDenAktualniKontext,

        'Date'[ISO čtvrtletí pořadí] = MaxCtvrtletiAktualniKontext

    )

VAR Vysledek =

    IF

    (

        NOT ISEMPTY('Sales'),

        Vypocet

    )

RETURN

    Vysledek

Nové měřítko si vložíme do vizuálu spolu s měřítkem [Prodeje YTD]. Výsledek pak bude vypadat následovně.

Jak je možné vidět na obrázku výše, měřítko [Prodeje QTD] vrací kumulativní součet vždy od začátku čtvrtletní po aktuální den. Na začátku nového čtvrtletí pak začíná výpočet znovu od začátku, opět až po konec aktuálního čtvrtletí.

Výpočet WTD s ISO týdenním kalendářem

Výpočet WTD (Week To Date) bude vracet kumulativní součet prodejů od začátku týdne po aktuální den. Na začátku každého nového týdne pak budou hodnoty sčítány znovu od prvního dne. Princip výpočtu WTD je opět velmi podobný jako předcházející výpočty, pouze ve filtru funkce CALCULATE() použijeme sloupec s pořadovým číslem týdne napříč roky.

Měřítko:

Prodeje WTD =

VAR MaxDenAktualniKontext = MAX('Date'[Date])

VAR MaxTydenAktulniKontext = MAX('Date'[ISO týden pořadí])

VAR Vypocet =

    CALCULATE

    (

        [Prodeje],

        REMOVEFILTERS('Date'),

        'Date'[Date] <= MaxDenAktualniKontext,

        'Date'[ISO týden pořadí] = MaxTydenAktulniKontext

    )

VAR Vysledek =

    IF

    (

        NOT ISEMPTY('Sales'),

        Vypocet

    )

RETURN

    Vysledek

Nové měřítko si můžeme opět vložit do vizuálu Matrix, a zobrazit si výsledek měřítka na úrovni jednotlivých dnů.

Jak bylo možné vidět na příkladech v tomto příspěvku, výpočty RT, YTD, QTD a WTD s ISO týdenním kalendářem nejsou nijak složité, pokud pracujeme s kalendářní tabulkou, která obsahuje sloupce s pořadovými čísly čtvrtletí a týdnů napříč roky. Jak již bylo zmíněno dříve, způsob vytvoření těchto pomocných sloupců v ISO týdenním kalendáři je popsán v samostatném příspěvku. Soubor s řešením si pak můžete stáhnout pod níže uvedeným odkazem. Další praktické DAX příklady, včetně časových kalkulací vytvořených pomocí Time intelligence funkcí, které jsou určené pro práci se standardním kalendářem, můžete najít na stránce DAX – příklady.

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