Porovnání výkonosti s minulým obdobím v DAX s ISO týdenním kalendářem

V tomto příspěvku jsou uvedeny dva typy časových kalkulací, jejichž kombinací získáme obrázek o aktuální výkonnosti v porovnání s minulým obdobím. První typ výpočtu bude obsahovat hodnoty za celé předchozí období, například za celý předchozí rok (PYC). Druhý výpočet bude vracet kumulativní hodnoty v aktuálním období, například v aktuálním roce (YTD). Jednoduchým porovnáním pak získáme informaci, zda se například v průběhu aktuálního roku blížíme výsledkům dosaženým v loňském roce, nebo zda jsme již loňský rok překonali. Výsledek si můžeme zobrazit jak v absolutních (YTD vs PYC), tak v relativních (YTD vs PYC %) hodnotách. Jako období budeme v tomto příspěvku uvažovat ISO roky, ISO čtvrtletí a ISO týdny.

Porovnání kumulativních prodejů s prodeji v minulém období v DAX

Všechny 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. 

Power BI model je navíc doplněn o vlastní datumovou tabulku s ISO týdenním kalendářem, jehož struktura je popsána v samostatném příspěvku. Odkazovaný příspěvek obsahuje také návod a DAX kód pro vytvoření kalendářní tabulky, právě podle standardu ISO týdenního kalendáře. 

Soubor s vyřešenými příklady z tohoto článku je k dispozici ke stažení níže pod tímto příspěvkem.

Ve výpočtech budeme 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í. Ve výpočtech uvedených v tomto příspěvku budeme manipulovat s filtry pomocí funkce CALCULATE(), ve které bude v prvním argumentu právě měřítko [Prodeje]. Toto měřítko pak může být nahrazeno jakýmkoliv jiným měřítkem, které je aditivní vůči datumové tabulce (dává smysl sčítat hodnoty v čase).

Pokud měřítko [Prodeje] vložíme do Power BI vizuálu Matrix, spolu s ISO roky, ISO čtvrtletími, ISO týdny a dny v řádcích, výchozí report může vypadat například následovně.

Pro porovnání kumulativních prodejů s prodeji v minulém období budeme potřebovat vytvořit měřítka, která budou vracet sumu za prodané produkty v celém minulém období, a dále měřítka, která budou vracet kumulativní součet prodejů od začátku vybraného období. Jako období budeme uvažovat ISO roky, ISO čtvrtletí a ISO týdny.

Prodeje za celý předcházející rok (PYC)

Výpočet prodejů za celý předcházející rok je relativně jednoduchý. V prvním kroku si do proměnné uložíme číselné vyjádření ISO roku v aktuálním kontextu vyhodnocení. Dále ve funkci CALCULATE() odstraníme všechny filtry z tabulky s ISO týdenním kalendářem, a přidáme filtr obsahující minulý rok, tedy aktuální rok mínus jedna. Před zobrazením výsledku výpočtu si ještě pomocí funkce IF() ověříme, zda se jsou v aktuálním kontextu vyhodnocení dostupná data ve faktové tabulce, a zda je výpočet vyhodnocen v kontextu pouze jednoho ISO roku. Celý DAX výpočet může vypadat následovně.

Měřítko:

Prodeje PYC =

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

VAR Vypocet =

    CALCULATE

    (

        [Prodeje],

        REMOVEFILTERS('Date'),

        'Date'[ISO rok číslo] = MaxRokAktualniKontext - 1

    )

VAR Vysledek =

    IF

    (

        HASONEVALUE('Date'[ISO rok číslo]) && NOT ISEMPTY(Sales),

        Vypocet

    )

RETURN

    Vysledek

Nové měřítko můžeme si můžeme vložit do dříve vytvořeného vizuálu a zobrazit si výsledek na úrovni ISO let.

Měřítko [Prodeje PYC] vrací podle očekávání sumu za prodané produkty v minulém roce. Protože ve funkci CALCULATE() odstraňujeme všechny filtry z datumové tabulky, a používáme pouze filtr obsahující minulý rok, výsledkem měřítka bude hodnota prodejů za celý minulý rok také na úrovni čtvrtletí, týdnů nebo dnů.

Měřítko [Prodeje PYC] proto vrací v každém řádku použitého vizuálu hodnotu, která bude sloužit jako základna pro hodnocení vývoje prodejů v aktuálním roce. 

Druhým krokem bude vytvoření měřítka, které bude vracet kumulativní prodeje od začátku roku po aktuální den.

Prodeje od začátku roku po aktuální den (YTD)

K získání hodnoty kumulativních prodejů v aktuálním roce potřebujeme vytvořit ve funkci CALCULATE() filtr, který bude obsahovat aktuální ISO rok, a současně všechny dny v ISO roce, které jsou menší nebo rovny maximálnímu dnu dostupnému v aktuálním kontextu vyhodnocení. Před zobrazením výsledku výpočtu si opět můžeme pomocí funkce IF() ošetřit, aby se výsledky měřítka nezobrazovaly pro dny, ve kterých ještě nedošlo k žádným prodejům. Definice takto popsalného měřítka může v jazyku DAX vypadat následovně.

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

    (

        HASONEVALUE('Date'[ISO rok číslo]) && NOT ISEMPTY(Sales),

        Vypocet

    )

Return

    Vysledek

Nové měřítko [Prodeje YTD] si můžeme vložit do dříve připraveného vizuálu a zobrazit si výsledky.

Na obrázku výše můžeme například vidět, že v ISO roce 2019 došlo k překonání prodejů z předchozího roku někdy v průměhu čtvrtého čtvrtletí.  Na základě měřítek [Prodeje PYC] a [Prodeje YTD] již můžeme jednoduchým odečítáním a dělením získat měřítka, která budou hodnotit aktuální výkonnost v daném roce, v porovnání s předchozím rokem.

Porovnání kumulativních prodejů v aktuálním roce s prodeji v minulém roce

Jak již bylo uvedeno dříve, k porovnání kumulativních prodejů v aktuálním roce s prodeji v roce minulém již máme vše potřebné připraveno. K porovnání v absolutních hodnotách stačí odečíst výsledek měřítka [Prodeje PYC] od výsledku měřítka [Prodeje YTD].

Měřítko:

Prodeje YTD vs PYC = [Prodeje YTD] - [Prodeje PYC]

Procentuální vyjádření můžeme vydělit výsledek měřítka [Prodeje YTD ve PYC] hodnotou měřítka [Prodeje PYC].

Měřítko:

Prodeje YTD vs PYC % = DIVIDE([Prodeje YTD vs PYC], [Prodeje PYC])

Obě nová měřítka vložíme do původního vizuálu.

Na obrázku výše můžeme například vidět, že v roce 2019 překonaly prodeje předchozí rok už týdnu číslo 41. Podobným způsobem si můžeme vytvořit měřítka, která budou porovnávat kumulativní prodeje v aktuálním čtvrtletí s prodeji za celé minulé čtvrtletí.

Prodeje za celé předcházející čtvrtletí (PQC)

Výpočet prodejů za celé předcházející čtvrtletí je velmi podobný výpočtu prodejů za celý minulý rok. Pouze místo čísla roku použijeme ve filtru funkce CALCULATE() sloupec s pořadovým číslem čtvrtletí.

Měřítko:

Prodeje PQC =

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

VAR Vypocet =

    CALCULATE

    (

        [Prodeje],

        REMOVEFILTERS('Date'),

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

    )

VAR Vysledek =

    IF

    (

        HASONEVALUE('Date'[ISO čtvrtletí pořadí]) && NOT ISEMPTY(Sales),

        Vypocet

    )

RETURN

    Vysledek

Další výpočet, který budeme potřebovat pro porovnání aktuální výkonosti ve vztahu k předcházejícímu čtvrtletí, bude výpočet kumulativních prodejů od začátku čtvrtletí po aktuální den.

Prodeje od začátku čtvrtletí po aktuální den (QTD)

K získání kumulativních prodejů od začátku čtvrtletí po aktuální den použijeme podobný postup, jako v případě kumulativních prodejů od začátku roku po aktuální den. Rozdíl bude opět pouze v použitých sloupcích, kdy budeme pracovat s pořadovými čísly čtvrtletí a s pořadovými čísly dnů ve čtvrtletí. 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

    (

        HASONEVALUE('Date'[ISO čtvrtletí pořadí]) && NOT ISEMPTY('Sales'),

        Vypocet

    )

RETURN

    Vysledek

Na základě měřítek [Prodeje PQC] a [Prodeje QTD] můžeme vytvořit výpočty, které budou přímo porovnávat aktuální prodeje s prodeji v minulém čtvrtletí.

Porovnání kumulativních prodejů v aktuálním čtvrtletí s prodeji v minulém čtvrtletí

Měřítko porovnávající kumulativních prodeje v aktuálním čtvrtletí s prodeji v minulém čtvrtletí v absolutních hodnotách následuje logiku podobného výpočtu na úrovni let a vypadá následovně.

Měřítko:

Prodeje QTD vs PQC = [Prodeje QTD] - [Prodeje PQC]

Následně si můžeme vytvořit měřítko porovnávající kumulativní prodeje v aktuálním čtvrtletí s prodeji v celém minulém čtvrtletí v relativních hodnotách.

Měřítko:

Prodeje QTD vs PQC % = DIVIDE([Prodeje QTD vs PQC], [Prodeje PQC])

Všechna měřítka vytvořená pro porovnání prodejů mezi čtvrtletími si můžeme vložit do původního vizuálu a zobrazit si jejich výsledek.

Na obrázku výše můžeme například vidět, že ve druhém čtvrtletí ISO roku 2019 došlo k překonání prodejů v minulém čtvrtletím již v týdnu číslo 23 (řádek ISO T23-R2019), a celkově byly prodeje ve druhém čtvrtletí stejného roku vyšší o 28, 30 %, než prodeje v celém předchozím čtvrtletí.

Podobným způsobem si můžeme vytvořit měřítka, ve kterých budeme porovnávat kumulativní prodeje v rámci týdnů s prodeji v předcházejícím týdnu.

Prodeje za celý předcházející týden (PWC)

Výpočet prodejů za celý předcházející týden je opět podobný výpočtu prodejů za celý předcházející rok nebo prodejů za celé předcházející čtvrtletí. Rozdíl je opět pouze v použitých sloupcích z datumové tabulky.

Měřítko:

Prodeje PWC =

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

VAR Vypocet =

    CALCULATE

    (

        [Prodeje],

        REMOVEFILTERS('Date'),

        'Date'[ISO týden pořadí] = MaxTydenAktualniKontext - 1

    )

VAR Vysledek =

    IF

    (

        HASONEVALUE('Date'[ISO týden pořadí]) && NOT ISEMPTY(Sales),

        Vypocet

    )

RETURN

    Vysledek

Následuje výpočet kumulativních prodejů od začátku týdne po aktuální den.

Prodeje od začátku týdne po aktuální den (WTD)

Měřítko [Prodeje WTD] je opět podobné jako v případě stejného typu výpočtu na úrovni let a na úrovni čtvrtletí, a má následující definici.

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

    (

        HASONEVALUE('Date'[ISO týden pořadí]) && NOT ISEMPTY('Sales'),

        Vypocet

    )

RETURN

    Vysledek

Na základě měřítka [Prodeje PWC] a měřítka [Prodeje WTD] si opět vytvoříme výpočty porovnávající kumulativní prodeje k aktuálnímu dnu v týdnu s prodeji v minulém týdnu.

Porovnání kumulativních prodejů v aktuálním týdnu s prodeji v minulém týdnu

Měřítko porovnávající kumulativní prodeje v aktuálním týdnu s prodeji v minulém týdnu v absolutních hodnotách má následující definici.

Měřítko:

Prodeje WTD vs PWC = [Prodeje WTD] - [Prodeje PWC]

Měřítko porovnávající kumulativní prodeje v aktuálním týdnu s prodeji v minulém týdnu v relativních hodnotách má následující definici.

Měřítko:

Prodeje WTD vs PWC % = DIVIDE([Prodeje WTD vs PWC], [Prodeje PWC])

Všechna nová měřítka vytvořená pro porovnání prodejů na úrovní týdnů si můžeme vložit do původního vizuálu a zobrazit si výsledky.

Na obrázku výše můžeme vidět, že pomocí měřítek [Prodeje WTD vs PWC] a [Prodeje WTD vs PWC %] lze snadno porovnávat výkonnost v aktuálním týdnu s výsledky, kterých firma dosáhla v týdnu předchozím. Podíváme-li se například na sedmý týden ISO roku 2019, tak můžeme vidět, že v tomto týdnu došlo k překonání prodejů z minulého týdne již 16. února 2019, a celkově byly prodeje v tomto týdnu o 40, 29 % vyšší než v týdnu předchozím.

Všechny výpočty uvedené v tomto příspěvku jsou navázané na ISO týdenní kalendář, jehož struktura je popsána v samostatném příspěvku. Další příklady, včetně časových kalkulací vytvořených pomocí Time intelligence funkcí při práci se standardním kalendářem, můžete najít na stránce DAX příklady.

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