V niektorých spoločnostiach sa nepoužíva klasický reporting po kalendárnych mesiacoch, ale po reportovacích mesiacoch, ktoré sú mierne odlišné od tých kalendárnych, a počítajú sa dynamicky podľa interných pravidiel. A samozrejme potom aj v Power BI reportoch užívatelia očakávajú, že budú mať možnosť reportovať podľa takýchto mesiacov. Ideme sa teda pozrieť na to, ako zabezpečíme reporting, keď vo firme používame dynamické reportovacie mesiace.

Dynamické reportovacie mesiace vychádzajú z mnohých požiadaviek, ako napríklad aby daný mesiac obsahoval iba celé týždne, alebo stanovený počet týždňov. Dôvodom je ľahšia organizácia práce a rovnomernejšie výkazníctvo, napríklad vo výrobných spoločnostiach. Ako príklad si ukážeme, ako implementovať požiadavku, že reportovacie mesiace majú začínať vždy v 1. štvrtok kalendárneho mesiaca.

Začíname

Začneme s našim vzorovým súborom Power BI. V našom dátovom modeli máme okrem iného tabuľky “Objednávky” a “Čas”, z ktorých si poskladáme takúto kontingenčnú tabuľku:

1. do oblasti hodnôt dáme merítko “Obrat” z tabuľky “Objednávky”,
2. do oblasti riadkov dáme stĺpce “CalendarYear” a “MonthNumberOfYear” z tabuľky “Čas”.

Výsledkom bude takáto kontingenčka, v ktorej sú zobrazené obraty po rokoch a mesiacoch (z priestorových dôvodov sú na obrázku iba prvé mesiace):

Teraz by sme chceli implementovať hore uvedenú požiadavku na dynamické reportovacie mesiace. Čiže chceme reportovať po vlastných mesiacoch tak, aby každý reportovací mesiac začínal v 1. štvrtok kalendárneho mesiaca. A ukážeme si, ako to vieme spraviť nielen v dátovom modeli v Power BI pomocou jazyka DAX, ale aj v Power Query pomocou jazyka M.

Dynamické reportovacie mesiace pomocou jazyka DAX

Riešenie v jazyku DAX je relatívne jednoduché. Na začiatku však musíme odfiltrovať preč riadok s dátumom 30.6.2001, pretože tabuľka “Čas” nie je vytvorená tak, ako by mali byť vytvorené všetky časové tabuľky. Mali by obsahovať všetky dátumy zo všetkých analyzovaných rokov. Nám teraz tento dátum vadí kvôli tomu, že zo 6. mesiaca roku 2001 je to jediný dátum, ktorý máme v tejto tabuľke. A keďže to ani nie je 1. štvrtok v tom mesiaci, tak by nám takýto nekompletný mesiac zbytočne robil problémy pri nasledujúcich výpočtoch. Preto ho hneď na začiatku odfiltrujeme preč (nastavíme v Power Query filter na túto tabuľku na podmienku “DateKey > 30.6.2001”). Alternatívne môžete použiť našu ultimátnu časovú tabuľku, ktorú si dáte cez jej parametre v skripte vygenerovať len pre roky 2001-2004, a použijete ju namiesto tej vzorovej tabuľky “Čas”.

Stačí vytvoriť tieto 2 vypočítané stĺpce v tabuľke “Čas”:

Reportovací mesiac = 
VAR prvyStvrtokVMesiaci = 
   CALCULATE(MIN('Čas'[DateKey]), 
             ALLEXCEPT('Čas', 'Čas'[CalendarYear], 'Čas'[MonthNumberOfYear]), 
             'Čas'[DayNameOfWeek] = "Thursday")
RETURN
IF('Čas'[DateKey] >= prvyStvrtokVMesiaci, 'Čas'[MonthNumberOfYear], 
   IF('Čas'[MonthNumberOfYear] > 1, 'Čas'[MonthNumberOfYear] - 1, 12))

Reportovací rok = 
VAR prvyStvrtokVMesiaci = 
   CALCULATE(MIN('Čas'[DateKey]), 
             ALLEXCEPT('Čas', 'Čas'[CalendarYear], 'Čas'[MonthNumberOfYear]), 
             'Čas'[DayNameOfWeek] = "Thursday")
RETURN
IF('Čas'[DateKey] >= prvyStvrtokVMesiaci, 'Čas'[CalendarYear], 
   IF('Čas'[MonthNumberOfYear] > 1, 'Čas'[CalendarYear], 'Čas'[CalendarYear] - 1))

V oboch vzorcoch si najprv do premennej “prvyStvrtokVMesiaci” vypočítame prvý štvrtok v tom kalendárnom mesiaci, ktorý prislúcha aktuálne počítanému dátumu. Berieme pritom do úvahy, že celý ten vzorec vypočítaného stĺpca sa počíta samostatne pre každý riadok tabuľky. Takže keď sa počíta pre konkrétny riadok, ktorý v tabuľke “Čas” reprezentuje konkrétny deň, tak v tom riadku už máme informáciu o jeho roku v stĺpci “CalendarYear” a aj o jeho mesiaci v stĺpci “MonthNumberOfYear”. Vo výpočte premennej pomocou funkcie CALCULATE zmeníme kontext výpočtu tak, aby výpočet videl na všetky štvrtky v tom kalendárnom roku a mesiaci – pomocou funkcie ALLEXCEPT zrušíme všetky časové filtre okrem roku a mesiaca, a následne pomocou indického zápisu filtrov nastavíme filter nad stĺpcom “DayNameOfWeek” na hodnotu “Thursday”. V takomto kontexte dáme vypočítať minimum zo stĺpca “DateKey”, čiže to nájde prvý/najnižší dátum spomedzi tých štvrtkov v tom mesiaci. Tak získame dátum prvého štvrtku v mesiaci.

Následne už len v hlavnej časti vzorca za klauzulou RETURN vypočítame, do ktorého reportovacieho mesiaca spadá aktuálne počítaný riadok/dátum. Vzorec je jednoduchý a hovorí sám za seba, takže ho tu nebudeme rozoberať.

Keď potom chceme reportovať podľa týchto reportovacích rokov a mesiacov, tak ich stačí dať do kontingenčnej tabuľky namiesto pôvodných kalendárnych rokov a mesiacov. Vytvoríme teraz teda druhú kontingenčnú tabuľku, kde:

1. do oblasti hodnôt dáme merítko “Obrat” z tabuľky “Objednávky”,
2. do oblasti riadkov dáme stĺpce “Reportovací rok” a “Reportovací mesiac” z tabuľky “Čas”.

Výsledná kontingenčka bude vyzerať takto:

Keď teraz spravíme skúšku správnosti, a do oblasti riadkov kontingečky ešte pridáme aj stĺpec “DateKey” z tabuľky “Čas”, obsahujúci jednotlivé dátumy, tak napríklad pre prelom 8. a 9. mesiaca roku 2001 to bude vyzerať takto:

Keď si to skontrolujete s kalendárom, tak uvidíte, že 1. štvrtok v 9. mesiaci roku 2001 bol naozaj 6.9.2001. Podobne je to aj pre ostatné mesiace. Takto si skontrolujeme, že to máme naozaj správne. A takto jednoducho vieme implementovať dynamické reportovacie mesiace pomocou jazyka DAX.

Dynamické reportovacie mesiace pomocou Power Query a jazyka M

Riešenie v Power Query bude podobné ako v jazyku DAX. Prejdeme najprv do Power Query (cez tlačidlo “Transformovať údaje”), kde si najprv odfiltrujeme preč ten problematický dátum 30.6.2001, nastavením filtra na podmienku “DateKey > 30.6.2001”.

V ďalšom kroku si dáme pridáme do tabuľky vypočítaný stĺpec (hlavné menu “Pridať stĺpec” => “Vlastný stĺpec”), do ktorého si vypočítame dátum prvého štvrtku v mesiaci pre každý riadok tejto tabuľky. Ako názov stĺpca teda zadáme “PrvyStvrtokVMesiaci”, a ako vzorec zadáme tento vzorec:

List.Min(
  Table.SelectRows(#"Filtrované riadky", 
                  (r) => r[CalendarYear] = [CalendarYear] 
                         and r[MonthNumberOfYear] = [MonthNumberOfYear] 
                         and r[DayNameOfWeek] = "Thursday"
                  )[DateKey]
         )

V tomto vzorci si najprv funkciou Table.SelectRows vyfiltrujeme všetky riadky so štvrtkami pre rovnaký rok a mesiac, ako je na aktuálne počítanom riadku (názov “Filtrované riadky” odkazuje na predchádzajúci krok v Power Query). Z takejto vyfiltrovanej tabuľky vyberieme dátumový stĺpec “DateKey”, z ktorého funkciou List.Min dáme vrátiť najnižší dátum. A to bude prvý štvrtok v tom mesiaci pre aktuálne počítaný riadok/dátum.

Potom podobne ako pri predchádzajúcom riešení pomocou tohto stĺpca vypočítame stĺpce s reportovacím mesiacom a rokom. V ďalších dvoch krokoch teda vytvoríme už len tieto 2 vypočítané stĺpce, do ktorých si z aktuálne počítaného dátumu a prvého štvrtka v jemu príslušnom kalendárnom mesiaci, vypočítame číslo reportovacieho mesiaca a roku:

Názov stĺpca: Reportovací mesiac
Vzorec stĺpca:

if [DateKey] >= [PrvyStvrtokVMesiaci] then [MonthNumberOfYear] else 
  if [MonthNumberOfYear] > 1 then [MonthNumberOfYear] - 1 else 12

Názov stĺpca: Reportovací rok
Vzorec stĺpca:

if [DateKey] >= [PrvyStvrtokVMesiaci] then [CalendarYear] else 
  if [MonthNumberOfYear] > 1 then [CalendarYear] else [CalendarYear] - 1

Potom už len zmeníme typ týchto 2 stĺpcov na “Celé číslo” (napr. cez ikonku “ABC 123” v záhlaví oboch stĺpcoch), a máme to hotové. Nezabudnite potom ešte kliknúť na tlačidlo “Zavrieť a načítať“, aby sa vám to načítalo do dátového modelu. Tieto stĺpce potom použijete v kontingenčnej tabuľke rovnakým spôsobom, ako sme ich použili v prechádzajúcom riešení cez DAX vyššie.

Tento spôsob výpočtu v Power Query je dosť výpočtovo neefektívny, pretože efektívnejšia verzia by vyžadovala trochu iný prístup. Pre jednoduchosť výkladu sme však zvolili toto riešenie. Načítavanie dát do dátového modelu sa predĺži pri tejto tabuľke o cca. 10-15 sekúnd, čo zvyčajne nie je problém. Preto sme efektivitu výpočtu neriešili. Ak by ste to potrebovali, tak niekoľko techník na to nájdete v mojej knihe o Power Query.

Na záver

Takto teda viete implementovať dynamické reportovacie mesiace pomocou jazyka DAX, aj pomocou Power Query. Vyberte si, ktorý spôsob je pre vás jednoduchší alebo ľahší, oba dávajú rovnaké výsledky. Takto sa potom budete vedieť popasovať ľahko aj s obdobnými požiadavkami na dynamické reportovacie mesiace. A váš reporting bude opäť hračka 🙂