Ako vypočítať obrat za stálych zákazníkov v jazyku DAX, v PowerPivote aj v Power BI

Michal Chmelár Návody , , , , , , , , , ,

Obrat sa sleduje snáď v každej jednej firme, pretože ide o jeden z najdôležitejších ukazovateľov výkonu firmy. Niekedy však nie je až tak podstatný samotný obrat, ako obrat za stálych zákazníkov. Pretože stáli zákazníci už majú s firmou vybudované vzťahy, a pre firmu sú veľakrát dôležitejší ako ostatní zákazníci. Preto sa teraz pozrieme, ako to vypočítať hneď niekoľkými spôsobmi.

V tomto článku znova použijeme náš vzorový súbor PowerPivotu, resp. Power BI.

Potom si vyskladáme takýto report, kde budú zobrazené obraty (merítko Obrat z tabuľky Objednávky) podľa rokov (stĺpec CalendarYear z tabuľky Čas):

A teraz by sme chceli vypočítať obrat za stálych zákazníkov. Ten sa dá vypočítať na X spôsobov, a najdôležitejšie je zadefinovať si presne, čo sa myslí pod tým pojmom “stály zákazník”.

Prvý prípad – zákazníci z celého predchádzajúceho roka

V prvom príklade si ukážeme prípad, keď je stály zákazník definovaný ako zákazník, ktorý spravil nejakú objednávku počas predchádzajúceho roka. V tomto prípade vytvoríme takéto merítko:

Obrat za stálych zákazníkov :=
IF (
    HASONEFILTER ( 'Čas'[CalendarYear] );
    CALCULATE (
        [Obrat];
        FILTER (
            DISTINCT ( 'Objednávky'[CustomerKey] );
            CONTAINS (
                CALCULATETABLE (
                    DISTINCT ( 'Objednávky'[CustomerKey] );
                    SAMEPERIODLASTYEAR ( 'Čas'[DateKey] )
                );
                [CustomerKey]; 'Objednávky'[CustomerKey]
            )
        )
    )
)

Tento vzorec si najprv otestuje funkciou HASONEFILTER, či je v kontexte výpočtu vybraný jeden rok, aby sa nám merítko nepočítalo na úrovni celkového súčtu, kde to nemá zmysel. Ak je vybraný iba jeden rok, tak sa spustí hlavná časť vzorca.

V ňom si vypočítame Obrat voči prieniku zákazníkov z aktuálneho roku, a zákazníkov z predchádzajúceho roku. Najprv si cez funkciu DISTINCT zistíme zoznam unikátnych zákazníkov z aktuálne počítaného roku. Potom funkciou FILTER z tohto zoznamu odfiltrujeme zákazníkov, ktorí nemali objednávky v minulom roku. Spravíme to tak, že zavoláme funkciu CONTAINS, ktorá má túto syntax:

CONTAINS(tabuľka; stĺpec; hľadanáHodnota)

Táto funkcia vracia pravdu (hodnotu true), ak sa hľadanáHodnota nachádza v zadanej tabuľke v zadanom stĺpci. Inak vracia nepravdu (hodnotu false).

Funkciou FILTER teda zavoláme funkciu CONTAINS pre každý riadok zo zoznamu zákazníkov. Funkciou CONTAINS potom testujeme, či sa aktuálne prechádzaný zákazník nachádza v zozname zákazníkov z predchádzajúceho roka. Ten si vytvoríme tak, že si pomocou funkcie CALCULATETABLE spočítame zoznam zákazníkov v kontexte výpočtu posunutom o rok dozadu, pomocou funkcie Time Intelligence s názvom SAMEPERIODLASTYEAR. Výsledkom funkcie CONTAINS bude, či sa aktuálne prechádzaný zákazník vo funkcii FILTER nachádza v tom zozname zákazníkov spred roka. A následne funkcia FILTER odfiltruje zákazníkov z aktuálne počítaného roku len na tých, ktorí mali objednávku aj v predchádzajúcom roku. Nakoniec funkciou CALCULATE vypočítame Obrat voči tomuto zoznamu zákazníkov.

Namiesto funkcie DISTINCT by tam mohla byť zadaná aj celá tabuľka Objednávky, ale takto je výpočet rýchlejší. Optimalizácia, ktorej rozumejú iba Jedi Mastri 😀

Každopádne, výsledok bude vyzerať takto:

V tomto prípade sa obrat za stálych zákazníkov vypočíta iba pre posledné dva roky, pretože toto je syntetická databáza, a táto “firma” nemala prvé dva roky naozaj žiadnych stálych zákazníkov. Overiť si to viete buď cez jazyk SQL, alebo vytvorením viacerých kontingenčiek, kde všetky rozbijete ešte podľa stĺpca CustomerKey.

Čo sa však stane, keď túto kontingenčku rozklikneme ešte podľa mesiacov? Pridajme teda do nej stĺpec MonthName z tabuľky Čas:

Všimnite si, že obrat za stálych zákazníkov je vypočítaný až od júla 2003. Čo je síce správne, ale už nie sú správne tie čísla, pretože sú až podozrivo nízke. A ich súčet za všetky mesiace sa ani náhodou neblíži súčtu za celý ten rok 2003. Takže niekde bude zrada.

Problém je v tom, že sme v predchádzajúcom príklade posunuli kontext výpočtu funkciou SAMEPERIODLASTYEAR o rok dozadu. Ak keď máme kontingenčku rozkliknutú na úroveň mesiacov, tak to vypočítalo obrat za stálych zákazníkov, ktorí mali aspoň jednu objednávku v rovnakom mesiaci pred rokom. Pritom zadanie znelo tak, že stály zákazník je zákazník, ktorý mal aspoň jednu objednávku v celom predchádzajúcom roku. Opravíme to teda tak, že nebudeme posúvať kontext výpočtu o rok dozadu funkciou SAMEPERIODLASTYEAR, ale dáme si vrátiť kontext výpočtu za celý predchádzajúci rok funkciou PREVIOUSYEAR. Potom vo vzorci vyššie nahradíme funkciu SAMEPERIODLASTYEAR za funkciu PREVIOUSYEAR, až dostaneme takéto merítko:

Obrat za stálych zákazníkov :=
IF (
    HASONEFILTER ( 'Čas'[CalendarYear] );
    CALCULATE (
        [Obrat];
        FILTER (
            DISTINCT ( 'Objednávky'[CustomerKey] );
            CONTAINS (
                CALCULATETABLE (
                    DISTINCT ( 'Objednávky'[CustomerKey] );
                    PREVIOUSYEAR ( 'Čas'[DateKey] )
                );
                [CustomerKey]; 'Objednávky'[CustomerKey]
            )
        )
    )
)

A po prepočítaní bude kontingenčka už zobrazovať správne čísla:

Druhý prípad – zákazníci zo všetkých predchádzajúcich rokov

V druhom príklade si ukážeme, ako vypočítať obrat za stálych zákazníkov, keď je stály zákazník definovaný ako zákazník, ktorý spravil nejakú objednávku počas všetkých predchádzajúcich rokov. Alebo počas N prechádzajúcich rokov. To vypočítame týmto merítkom:

Obrat za stálych zákazníkov v2 :=
IF (
    HASONEFILTER ( 'Čas'[CalendarYear] );
    CALCULATE (
        [Obrat];
        FILTER (
            DISTINCT ( 'Objednávky'[CustomerKey] );
            CONTAINS (
                CALCULATETABLE (
                    DISTINCT ( 'Objednávky'[CustomerKey] );
                    DATESBETWEEN (
                        'Čas'[DateKey];
                        DATE ( 1900; 1; 1 );
                        FIRSTDATE ( 'Čas'[DateKey] ) - 1
                    )
                );
                [CustomerKey]; 'Objednávky'[CustomerKey]
            )
        )
    )
)

V tomto prípade je skoro celý ten vzorec takmer taký istý ako v predchádzajúcom príklade. Líši sa iba v tom, že namiesto funkcií SAMEPERIODLASTYEAR, resp. PREVIOUSYEAR, sme použili funkciu DATESBETWEEN, opäť spomedzi funkcií Time Intelligence. Ktorá má takúto syntax:

DATESBETWEEN (dátumovýStĺpec; dátumOd; dátumDo)

Táto funkcia vráti dátumovýStĺpec odfiltrovaný na dátumy od-do, s rešpektovaním ostatných nečasových filtrov. Po slovensky povedané, natiahne kontext výpočtu od dátumuOd po dátumDo.

V tejto funkcii sme natiahli kontext výpočtu od 1.1.1900 po “prvý dátum v aktuálnom kontexte výpočtu, mínus 1 deň”. Čiže ak to počítame napr. za august 2003, tak to vráti rozsah dátumov od 1.1.1900 po 31.7.2003. Respektívne, iba jeho výsek existujúci v časovej tabuľke (ktorá je vygenerovaná zhruba od polovice roku 2001). Namiesto dátumu 1.1.1900 sme mohli dať aj napr. CALCULATE(MIN(‘Čas'[DateKey]); ALL(‘Čas'[DateKey])), ale toto je kratšie riešenie.

Voči takémuto kontextu výpočtu potom funkciou CALCULATETABLE vypočítame zoznam zákazníkov v tom časovom období. A podobne ako v predchádzajúcom príklade, následne odfiltrujeme zákazníkov v aktuálne počítanom časovom období len na tých, ktorí sa nachádzali aj v tom celom predchádzajúcom období. Voči takémuto zoznamu zákazníkov necháme potom vypočítať obrat v aktuálne počítanom období (roku, mesiaci, a pod.).

Po dosadení do kontingenčky to bude vyzerať takto:

A keby ste teraz mali definovaného stáleho zákazníka ako zákazníka, ktorý spravil aspoň jednu objednávku v predchádzajúcich celých N rokoch, tak napr. pre N = 2 bude vzorec takýto:

Obrat za stálych zákazníkov v3 :=
IF (
    HASONEFILTER ( 'Čas'[CalendarYear] );
    CALCULATE (
        [Obrat];
        FILTER (
            DISTINCT ( 'Objednávky'[CustomerKey] );
            CONTAINS (
                CALCULATETABLE (
                    DISTINCT ( 'Objednávky'[CustomerKey] );
                    DATESBETWEEN (
                        'Čas'[DateKey];
                        DATEADD ( STARTOFYEAR ( FIRSTDATE ( 'Čas'[DateKey] ) ); -2; YEAR );
                        FIRSTDATE ( 'Čas'[DateKey] ) - 1
                    )
                );
                [CustomerKey]; 'Objednávky'[CustomerKey]
            )
        )
    )
)

Opäť takmer identický vzorec ako v prechádzajúcom prípade, len sme ako začiatok intervalu do funkcie DATESBETWEEN dali iný parameter. Najprv sme si funkciou FIRSTDATE zistili prvý dátum v aktuálne počítanom období (rok, mesiac, …). Ten sme funkciou STARTOFYEAR posunuli na začiatok príslušného roku, a ten sme funkciou DATEADD posunuli o 2 roky dozadu. Ak chcete iné obdobie ako 2 roky, tak namiesto “-2” zadajte iný počet záporných rokov. Výsledok bude vyzerať takto:

Ako vidíte, pre rok 2003 dávajú predchádzajúce merítko a toto merítko logicky rovnaké výsledky, a rozdiely vidno až v roku 2004.

Tretí prípad – zákazníci za predchádzajúci rok

No a ak by vám to nestačilo, a chceli by ste vypočítať obrat za stálych zákazníkov, keď je stály zákazník definovaný ako zákazník, ktorý spravil nejakú objednávku počas posledného roka – čiže počas posledných 12 mesiacov – tak vzorec pre merítko bude znova mierne upravený takto:

Obrat za stálych zákazníkov v4 :=
IF (
    HASONEFILTER ( 'Čas'[CalendarYear] );
    CALCULATE (
        [Obrat];
        FILTER (
            DISTINCT ( 'Objednávky'[CustomerKey] );
            CONTAINS (
                CALCULATETABLE (
                    DISTINCT ( 'Objednávky'[CustomerKey] );
                    DATESBETWEEN (
                        'Čas'[DateKey];
                        DATEADD ( FIRSTDATE ( 'Čas'[DateKey] ); -12; MONTH );
                        FIRSTDATE ( 'Čas'[DateKey] ) - 1
                    )
                );
                [CustomerKey]; 'Objednávky'[CustomerKey]
            )
        )
    )
)

V tomto prípade sme znova upravili iba začiatok intervalu vo funkcii DATESBETWEEN. Najprv sme funkciou FIRSTDATE zistili prvý dátum v aktuálne počítanom období (rok, mesiac, …). Ten sme funkciou DATEADD posunuli o 12 mesiacov dozadu. Zvyšok výpočtu je potom rovnaký ako v prechádzajúcom príklade.

Výsledok bude vyzerať takto:

A opäť – ak chcete vypočítať obrat za stálych zákazníkov za iný počet mesiacov, tak zmeňte “-12” vo vzorci na iné záporné číslo.

Takto teda viete vypočítať obrat za stálych zákazníkov na N spôsobov. Ako vidíte, stačí rozumieť funkciám Time Intelligence, a pohrať sa s nimi tak, aby vám vyšlo presne to obdobie, ktoré potrebujete. Nie že by to nešlo spraviť aj inak, ale pomocou týchto funkcií je to asi najjednoduchšie. A práve v tom je ich krása 🙂