Pri príprave dátového modelu pre obchodnú analýzu sa užívatelia často stretávajú s problémom, ako v PowerPivote, resp. Power BI vypočítať počet pracovných dní medzi dvoma zadanými dátumami. Napr. pri objednávkach, na výpočet počtu pracovných dní medzi dátumom objednania a dátumom vybavenia, aby vedeli sledovať následne napr. priemerný počet pracovných dní, potrebných na vybavenie objednávky. Poďme sa na to teda pozrieť.

Poznámka: Toto je článok z knihy o Power BI, PowerPivote a jazyku DAX. Článok bol pre potreby blogu mierne upravený.

DAX nemá veľmi funkcie na to, aby vedel vypočítať rozdiel medzi dvoma dátumami v pracovných dňoch. Najnovší DAX má síce funkciu DATEDIFF, ktorou viete zistiť rozdiel v dňoch medzi danými dátumami, ale to viete aj ich jednoduchým odpočítaním. Keď však do toho máte zakomponovať víkendy a sviatky, tak sa problém stane o to zaujímavejším.

Riešenie je relatívne jednoduché, ak máte najnovší DAX alebo PowerPivot od jeho 3. generácie – čiže Power BI alebo aspoň Excel 2016 či SSAS Tabular 2016. Budeme 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.

Najprv budeme potrebovať dva dátumové stĺpce v tabuľke Objednávky. Jeden stĺpec – dátum objednania – tam už máme ako stĺpec s názvom SaleDateKey. Druhý stĺpec tam nemáme, ale pre účely tohto príkladu si vyrobíme vypočítaný stĺpec s názvom “Dátum vybavenia“, ktorý bude 14 kalendárnych dní po dátume objednania, a bude mať takýto jednoduchý vzorec:

Dátum vybavenia = ‘Objednávky'[SaleDateKey] + 14

Do tabuľky Objednávky vyrobíme potom vypočítaný stĺpec, ktorý ku každej objednávke vypočíta počet pracovných dní medzi dátumom objednania – stĺpec SaleDateKey – a dátumom vybavenia – v stĺpci Dátum vybavenia:

Počet pracovných dní = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(‘Objednávky'[SaleDateKey]; ‘Objednávky'[Dátum vybavenia]); “Prac. deň”; LOOKUPVALUE(‘Čas'[Pracovný deň]; ‘Čas'[DateKey]; [Date])); [Prac. deň] = “pracovný deň”))

…alebo vypeknené pomocou DAX Formattera:

Tento vzorec funguje takto:

1. najprv si pomocou funkcie CALENDAR vytvoríme dátumový stĺpec s názvom Date a s hodnotami od dátumu objednania po dátum vybavenia,
2. potom ku každému riadku takéhoto stĺpca vytvoríme ďalší stĺpec funkciou ADDCOLUMNS, s názvom “Prac. deň“, a cez funkciu LOOKUPVALUE doňho dotiahneme príslušnú hodnotu z časovej tabuľky, zo stĺpca ‘Čas'[Pracovný deň],
3. v ďalšom kroku odfiltrujeme takúto dvojstĺpcovú tabuľku len na také riadky, kde je v stĺpci “Prac. deň” hodnota “pracovný deň” – čiže len na riadky s pracovnými dňami,
4. potom funkciou COUNTROWS spočítame počet riadkov v takejto tabuľke, čo nám dá vo výsledku počet pracovných dní medzi pôvodnými dvoma dátumami, plus jeden deň navyše – pretože ak boli oba dátumy rovnaké a v pracovný deň, tak nám to vráti 1 riadok.

Výsledok v tabuľke Objednávky bude vyzerať napr. takto:

Následne v reporte iba pridáte takýto stĺpec napr. do tabuľky či kontingenčky, a nastavíte sumarizáciu stĺpca funkciou Priemer. Výsledok pri zobrazení výsledkov podľa rokov vyzerá napr. takto:

Toto riešenie funguje ale len od 3. generácie PowerPivotu, pretože používa funkciu CALENDAR, ktorá bola pridaná do PowerPivotu v roku 2015. Preto funguje len v Power BI, Exceli 2016 a SSAS Tabular 2016, a ich novších verziách. Práve tu však vidíte, prečo používať vždy čo najnovšiu verziu PowerPivotu – pretože relatívne jednoducho viete vyriešiť aj takéto bežné problémy.

Keby ste to mali riešiť starším DAX-om, tak tam bude vyzerať vzorec nejako takto:

Počet pracovných dní v2 = COUNTROWS(FILTER(‘Čas’; ‘Čas'[DateKey] >= ‘Objednávky'[SaleDateKey] && ‘Čas'[DateKey] <= ‘Objednávky'[Dátum vybavenia] && ‘Čas'[Pracovný deň] = “pracovný deň”))

…alebo vypeknené pomocou DAX Formattera:

Paradoxne, vzorec je jednoduchší ako v prvom prípade, ale výkonovo pomalší. Keď ale nemáte veľké dáta, tak ho nemusíte nutne optimalizovať.

Takže, takto sa raz a navždy rieši dilema, ako v Power BI a PowerPivote vypočítať rozdiel v pracovných dňoch medzi dvoma dátumami. Výhodou toho je, že teraz môžete mať presnejšie štatistiky rôznych plnení, a lepšie sledovať veličiny závislé od pracovných dní, aj v slovenských pomeroch. A keď chcete vedieť viac, tak sa prihláste na náš kurz Power BI, kde dostanete riešenia takýchto príkladov na počkanie 🙂