Ak ste niekedy robili analýzu podľa počtu pracovných dní, či iné podobné analýzy, tak ste pravdepodobne narazili na problém, ako v jazyku DAX posunúť daný dátum na prvý nasledujúci pracovný deň, aby sa vám ľahšie počítala daná štatistika. Napr. ak dátum objednania spadol napr. na víkend, a pre účely výpočtu ho potrebujete posunúť na prvý nasledujúci pracovný deň po ňom. Pre veteránov v DAXe však ani toto nie je problém, a preto sa teraz na to pozrieme 🙂

V jednom z predchádzajúcich článkov sme si ukázali, ako vypočítať rozdiel v pracovných dňoch medzi dvoma zadanými dátumami. Tento výpočet vám ale nie vždy môže vyhovovať, napr. keď máte mierne odlišnú definíciu, ako sa u vás počítajú rozdiely v pracovných dňoch. A jeden z klasických prípadov je práve to, že si predtým potrebujete posunúť daný dátum na prvý nasledujúci pracovný deň, ak to nie je pracovný deň, alebo na prvý predchádzajúci pracovný deň. My si ukážeme tú prvú aj druhú možnosť.

Riešenie si znova ukážeme na našom vzorovom súbore PowerPivotu, resp. Power BI. Budeme opäť vychádzať z výsledkov na konci tohto článku, kde sme si v našom vzorovom súbore, v časovej tabuľke, dopočítali ku každému dňu v histórii, či je to pracovný alebo nepracovný deň, podľa toho či je/nie je víkend alebo sviatok. Výsledkom bol stĺpec v tabuľke Čas s názvom Pracovný deň, ktorý pre daný deň obsahoval buď hodnotu “pracovný deň” alebo “nepracovný deň“, podľa toho či išlo o pracovný alebo nepracovný deň. A tento stĺpec využijeme v ďalšom výpočte.

Povedzme, že teraz chceme posunúť dátumy zo stĺpca SaleDateKey (dátum objednania) v tabuľke Objednávky na prvý nasledujúci pracovný deň, ak to nie je pracovný deň. Spravíme to novým vypočítaných stĺpcom v tejto tabuľke, s týmto vzorcom:

Datum posunuty = MAXX(TOPN(1; FILTER(‘Čas’; ‘Čas'[DateKey] >= ‘Objednávky'[SaleDateKey] && ‘Čas'[DateKey] < ‘Objednávky'[SaleDateKey] + 10); ‘Čas'[Pracovný deň]; ASC; ‘Čas'[DateKey]; ASC); ‘Čas'[DateKey])

…alebo vypeknené cez DAX Formatter:

Výsledok bude vyzerať takto, pre zopár vzorových dní:

A ako to celé funguje? Opäť, veľmi jednoducho, ak ste už zvyknutí na DAX 🙂

1. najprv si funkciou FILTER vyfiltrujeme z tabuľky Čas všetky riadky, ktoré majú dátum v stĺpci DateKey väčší alebo rovný ako dátum v stĺpci SaleDateKey v aktuálne počítanom riadku tabuľky Objednávky, a zároveň kde je DateKey menší ako 10 dní po SaleDateKey. Čiže si vyfiltrujeme všetky riadky z tabuľky Čas, ktoré tam sú od daného dátumu, ktorý nás zaujíma, po ten istý dátum + 10 dní. Takže budeme mať vo výsledku tabuľku, v ktorej sa nachádza 10 dní nasledujúcich po dátume v stĺpci SaleDateKey, vrátane toho dátumu,
2. potom si funkciou TOPN nájdeme prvý pracovný deň v týchto 10 dňoch. Využjeme to, že funkcia TOPN vie zotriediť riadky, aj podľa viacerých stĺpcov. Riadky z predchádzajúceho kroku najprv zotriedime podľa stĺpca ‘Čas'[Pracovný deň]. Keďže sú v ňom hodnoty “pracovný deň” a “nepracovný deň“, a písmeno “p” je v abecede skôr ako písmeno “n”, tak toto triedenie spôsobí to, že po ňom budú v tejto mini tabuľke najprv riadky s pracovnými dňami, a potom riadky s nepracovnými dňami. Takéto riadky dáme dodatočne zotriediť pomocou stĺpca ‘Čas'[DateKey], čiže podľa dátumu, a to spôsobí to, že vo výslednej tabuľke budú najprv pracovné dni zoradené podľa dátumu, a pod nimi budú nepracovné dni, tiež zoradené podľa dátumu. Z takéhoto výsledku zoberieme prvý riadok (viď prvý parameter funkcie TOPN). Tento prvý riadok obsahuje prvý pracovný deň z tých 10 dní vyššie – čiže ak bol aktuálny dátum v stĺpci SaleDateKey pracovným dňom, tak bude obsahovať tento dátum, inak bude obsahovať dátum prvého pracovného dňa po tomto dátume – čiže presne to čo hľadáme,
3. v poslednom kroku z toho 1 riadku potrebujeme vybrať hodnotu tohto dátumu. To spravíme v DAXe pomocou funkcie MAXX, kde povieme, že chceme vybrať zo zadanej 1-riadkovej tabuľky maximum zo stĺpca ‘Čas'[DateKey]. A keďže je tam len jeden riadok, tak to vráti hodnotu v tom riadku a zároveň stĺpci. Keď chcete, môžete použiť aj funkciu MINX, AVERAGEX, SUMX či MEDIANX, pretože je to v tomto prípade zajedno – je tam len 1 riadok.

Toto riešenie ale nebude fungovať v starších PowerPivotoch, ak by ste potrebovali vybrať textovú hodnotu. To ale viete obísť funkciou CALCULATE, podobne ako v staršom článku o dynamickej kategorizácii dát.

Takto teda posuniete zadané dátumy, jeden po druhom, na prvý nasledujúci pracovný deň, ak to nebol pracovný deň.

Obdobne viete posúvať dátumy aj na predchádzajúce dni – stačí upraviť iba podmienku v hore uvedenej funkcii FILTER tak, aby vám vyfiltrovala všetky riadky z tabuľky čas, ktoré sú v rozsahu od -10 dní pred zadaným dátumom, po zadaný dátum vrátane. Plus úpravou triedenia podľa dátumov vo funkcii TOPN na opačný smer:

Datum posunuty dozadu = MAXX(TOPN(1; FILTER(‘Čas’; ‘Čas'[DateKey] > ‘Objednávky'[SaleDateKey] – 10 && ‘Čas'[DateKey] <= ‘Objednávky'[SaleDateKey]); ‘Čas'[Pracovný deň]; ASC; ‘Čas'[DateKey]; DESC); ‘Čas'[DateKey])

…alebo vypeknené cez DAX Formatter:

Výsledok bude vyzerať takto, pre zopár vzorových dní:

Tých 10 dní je tam v oboch prípadoch kvôli jednoduchosti, pretože sme počas oboch výpočtov potrebovali vybrať rozsah X dní od zadaného dátumu, v ktorom bude aspoň 1 pracovný deň. A využili sme to, že v žiadnom roku u nás nenasleduje za sebou 10 nepracovných dní. Tú konštantu si môžete upraviť aj na nižšie číslo, ak sa vám chce hľadať naprieč všetkými rokmi, koľko je to minimálne dní. Myslím, že to bolo niečo okolo 6 alebo 7. Každopádne, dokým to pravidelne nepotrebujete prepočítavať na miliardách riadkov, tak je to zajedno. PowerPivot to svojou rýchlosťou zvládne, a kľudne tam môžete nechať aj tú 10-ku. A to je na ňom to pekné, že niekedy nemusíte uvažovať úplne presne do detailov, a môžete sa sústrediť viac na čo najrýchlejšie urobené riešenie. Pretože aj takáto mierna neefektivita sa stratí v tej obrovskej rýchlosti výpočtu 🙂

Takže takto viete posúvať dátumy na nasledujúce pracovné dni, aj na prechádzajúce pracovné dni. A následne to využiť do ďalších výpočtov, napr. ako v článku pri počítaní rozdielu v pracovných dňoch medzi dvoma zadanými dátumami. A ako vždy, kreativite sa medze nekladú 🙂