Vytvorenie tabuľky dátumov

Dokončené

Pri vytváraní zostáv v službe Power BI podniky väčšinou potrebujú vykonávať výpočty na základe dátumu a času. Organizácie chcú zvyčajne vedieť, ako sa im darilo v podnikaní v uplynulých mesiacoch, štvrťrokoch, fiškálnych rokoch atď. Z tohto dôvodu je dôležité, aby boli časovo orientované hodnoty naformátované správne. Power BI automaticky zistí hodnoty pre stĺpce dátumu a tabuľky; môžu sa však vyskytnúť situácie, v ktorých budete musieť vykonať ďalšie kroky na to, aby ste získali dátumy vo formáte, ktorý vaša organizácia vyžaduje.

Predpokladajme, že vyvíjate zostavy pre tím predaja vo svojej organizácii. Databáza obsahuje tabuľky pre predaj, objednávky, produkty a ďalšie parametre. Všimnite si, že mnohé z týchto tabuliek vrátane predajov a objednávok obsahujú vlastné stĺpce dátumov, ako je znázornené v stĺpcoch ShipDate (Dátum dodania) a OrderDate (Dátum objednávky) v tabuľkách Sales (Predaj) a Orders (Objednávky). Máte za úlohu vytvoriť tabuľku celkového predaja a objednávok podľa rokov a mesiacov. Ako sa dá vytvoriť vizuál s viacerými tabuľkami, pričom každý bude odkazovať na vlastné stĺpce dátumov?

Snímka obrazovky s úryvkom sémantického modelu so zvýraznenými položkami Sales.ShipDate a Order.OrderDate.

Ak chcete vyriešiť tento problém, môžete vytvoriť spoločnú tabuľku dátumov, ktorú možno použiť vo viacerých tabuľkách. V nasledujúcej časti je vysvetlené, ako môžete túto úlohu splniť v službe Power BI.

Vytvorenie spoločných tabuliek dátumov

Spôsoby, ako môžete vytvoriť spoločnú tabuľku dátumov, sú:

  • Zdrojové údaje

  • DAX

  • Power Query

Zdrojové údaje

Príležitostne majú už zdrojové databázy a sklady údajov vlastné tabuľky dátumov. Ak správca, ktorý vytvoril databázu, vykonal dôkladnú prácu, možno tieto tabuľky použiť na vykonanie nasledujúcich úloh:

  • Identifikácia firemných sviatkov

  • Samostatný kalendár a fiškálny rok

  • Identifikácia víkendov a pracovných dní

Tabuľky zdrojových údajov sú hotové a pripravené na okamžité použitie. Ak máte tabuľku ako takú, preneste ju do sémantického modelu a nepoužívajte žiadne iné metódy, ktoré sú uvedené v tejto časti. Odporúčame, aby ste použili tabuľku zdrojových dátumov, pretože je pravdepodobne zdieľaná s inými nástrojmi, ktoré by ste mohli používať okrem služby Power BI.

Ak nemáte tabuľku zdrojových údajov, môžete použiť iné spôsoby vytvorenia spoločnej tabuľky dátumov.

DAX

S cieľom vytvoriť spoločnú tabuľku dátumov môžete použiť funkcie CALENDARAUTO() alebo CALENDAR() jazyka DAX (Data Analysis Expression). Funkcia CALENDAR() vracia súvislý rozsah dátumov na základe počiatočného a koncového dátumu, ktoré sú zadané ako argumenty vo funkcii. Prípadne funkcia CALENDARAUTO() vráti súvislý úplný rozsah dátumov, ktoré sa automaticky určia na základe vášho sémantického modelu. Počiatočný dátum sa vyberie ako najskorší dátum, ktorý existuje vo vašom sémantickom modeli, a koncový dátum je posledným dátumom, ktorý existuje vo vašom sémantickom modeli plus údajoch, ktoré sa vyplnili do fiškálneho mesiaca a ktoré môžete vybrať ako argument vo funkcii CALENDARAUTO(). V tomto príklade sa použije funkcia CALENDAR(), pretože chcete zobraziť len údaje od 31. mája 2011 (prvý deň, keď sa v rámci predaja začali tieto údaje sledovať) a ďalej v priebehu nasledujúcich 10 rokov.

V Power BI Desktop vyberte položku Nová tabuľka a potom zadajte nasledujúci vzorec DAX:

Dates  = CALENDAR(DATE(2011, 5, 31), DATE(2022, 12, 31))

Snímka obrazovky so vzorcom CALENDAR v službe Power BI.

Teraz máte k dispozícii stĺpec dátumov, ktorý môžete použiť. Údaje v tomto stĺpci sú však jemne nedostatočné. Budete tiež chcieť zobraziť stĺpce len za rok, číslo mesiaca, týždeň v roku a deň v týždni. Túto úlohu môžete vykonať výberom položky Nový stĺpec na páse s nástrojmi a zadaním nasledujúcej rovnice jazyka DAX, ktorá načíta rok z vašej tabuľky dátumov.

Year = YEAR(Dates[Date])

Snímka obrazovky znázorňujúca pridanie stĺpcov pomocou rovnice jazyka DAX.

Rovnaký proces môžete vykonať, ak chcete načítať číslo mesiaca, číslo týždňa a deň v týždni:

MonthNum = MONTH(Dates[Date])
WeekNum = WEEKNUM(Dates[Date])
DayoftheWeek = FORMAT(Dates[Date], "DDDD")

Po dokončení bude tabuľka obsahovať stĺpce, ktoré sú zobrazené na nasledujúcom obrázku.

Snímka obrazovky znázorňujúca záverečné stĺpce v tabuľke jazyka DAX.

Teraz ste vytvorili spoločnú tabuľku dátumov pomocou jazyka DAX. Tento proces len pridá vašu novú tabuľku do sémantického modelu. Stále budete musieť vytvoriť vzťahy medzi tabuľkou dátumov a tabuľkami Predaj a Objednávka a potom označiť tabuľku ako oficiálnu tabuľku dátumov sémantického modelu. Pred dokončením týchto úloh sa však uistite, že ste zvážili aj iný spôsob vytvárania spoločnej tabuľky dátumov: pomocou doplnku Power Query.

Power Query

Ak chcete definovať spoločnú tabuľku dátumov, môžete použiť jazyk M – jazyk pre vývoj, ktorý sa používa na vytváranie dotazov v doplnku Power Query.

V aplikácii Power BI Desktop vyberte možnosť Transformovať údaje, ktoré vás nasmerujú na Power Query. Kliknite pravým tlačidlom myši na prázdny priestor ľavej tably Dotazy a otvorí sa nasledujúca rozbaľovacia ponuka, v ktorej vyberiete položku Nový dotaz > Prázdny dotaz.

Snímka obrazovky vytvorenia nového dotazu v službe Power BI.

Ak chcete vytvoriť tabuľku kalendára, vo výslednom zobrazení Nový dotaz zadajte nasledujúci vzorec v jazyku M:

= List.Dates(#date(2011,05,31), 365*10, #duration(1,0,0,0))

Snímka obrazovky znázorňujúca použitie vzorca v jazyku M na vytvorenie tabuľky kalendára.

V prípade vašich údajov o predaji chcete, aby počiatočný dátum zodpovedal najskoršiemu dátumu, ktorý máte vo svojich údajoch: 31. máj 2011. Okrem toho chcete zobraziť dátumy pre nasledujúcich 10 rokov vrátane dátumov v budúcnosti. Tento prístup zaisťuje, že popri napĺňaní tabuľky novými údajmi predaja nebudete musieť túto tabuľku znova vytvárať. Môžete tiež zmeniť trvanie. V tomto prípade potrebujete údajový bod pre každý deň, ale môžete tiež zvýšiť hodnotu hodín, minút a sekúnd. Výsledok sa zobrazuje na nasledujúcom obrázku.

Snímka obrazovky kalendára predaja vo forme zoznamu.

Potom úspešnom dokončení procesu si všimnite, že namiesto tabuľky dátumov máte k dispozícii zoznam dátumov. Ak chcete túto chybu opraviť, prejdite na kartu Transformovať na páse s nástrojmi a vyberte položku Konvertovať > na tabuľku. Ako samotný názov napovedá, táto funkcia skonvertuje váš zoznam na tabuľku. Stĺpec môžete tiež premenovať na DateCol.

Snímka obrazovky znázorňujúca konverziu zoznamu na tabuľku v Editor Power Query.

Následne musíte do svojej novej tabuľky pridať stĺpce, aby sa zobrazili dátumy (rok, mesiac, týždeň, deň) a mohli ste tak vo svojom vizuáli vytvoriť hierarchiu. Prvou úlohou je zmeniť typ stĺpca výberom ikony vedľa názvu stĺpca a v zobrazenej rozbaľovacej ponuke výberom typu dátumu.

Snímka obrazovky znázorňujúca zmenu typu na dátum.

Po dokončení výberu typu Dátum môžete pridať stĺpce pre rok, mesiace, týždne a dni. Prejdite na možnosť Pridať stĺpec, vyberte rozbaľovaciu ponuku v časti Dátum a potom vyberte položku Rok, ako je to znázornené na nasledujúcom obrázku.

Snímka obrazovky znázorňujúca pridávanie stĺpcov prostredníctvom Power Query.

Všimnite si, že služba Power BI pridala stĺpec všetkých rokov, ktoré pochádzajú zo stĺpca DateCol.

Snímka obrazovky znázorňujúca pridanie stĺpcov s Power Query v tabuľke.

Vykonajte rovnaký postup pri mesiacoch, týždňoch a dňoch. Po dokončení tohto procesu bude tabuľka obsahovať stĺpce, ktoré sú zobrazené na nasledujúcom obrázku.

Snímka obrazovky stĺpcov DateCol, Year, Month (Mesiac), Week of Year (Týždeň v roku) a Day Name (Názov dňa).

Teraz ste úspešne použili Power Query na vytvorenie bežnej tabuľky dátumov.

V predchádzajúcich krokoch sa ukazuje, ako sa má tabuľka preniesť do sémantického modelu. Teraz musíte tabuľku označiť ako oficiálnu tabuľku dátumov, aby ju služba Power BI mohla rozpoznať pre všetky budúce hodnoty, a overiť správnosť jej formátovania.

Označenie tabuľky ako oficiálnej tabuľky dátumov

Vašou prvou úlohou pri označovaní tabuľky ako oficiálnej tabuľky dátumov je vyhľadať novú tabuľku na table Polia. Kliknite pravým tlačidlom myši na názov tabuľky a potom vyberte možnosť Označiť ako tabuľku dátumov, ako je to znázornené na nasledujúcom obrázku.

Snímka obrazovky znázorňujúca možnosť Označiť ako tabuľku dátumov.

Keď označíte tabuľku ako tabuľku dátumov, Power BI vykoná platné overenia, aby sa zabezpečilo, že údaje neobsahujú hodnoty null, sú jedinečné a obsahujú priebežné hodnoty dátumu určitého obdobia. Môžete tiež vybrať konkrétne stĺpce v tabuľke, ktoré sa majú označiť ako dátum, čo môže byť užitočné, ak máte v tabuľke veľa stĺpcov. Kliknite pravým tlačidlom myši na tabuľku, vyberte položku Označiť ako tabuľku dátumov a potom vyberte položku Nastavenia tabuľky Dátum. Zobrazí sa nasledujúce okno, v ktorom môžete vybrať stĺpec, ktorý by mal byť označený ako Dátum.

Snímka obrazovky znázorňujúca dialógové okno označiť ako tabuľku dátumov.

Výberom možnosti Označiť ako tabuľku dátumov sa odstránia automaticky generované hierarchie z poľa Dátum v tabuľke, ktorú ste označili ako tabuľku dátumov. V prípade ostatných polí dátumov bude automatická hierarchia naďalej prítomná, kým nevytvoríte vzťah medzi daným poľom a tabuľkou dátumov alebo kým nevypnete funkciu Automatický dátum a čas. Hierarchiu môžete manuálne pridať do spoločnej tabuľky dátumov tak, že kliknete pravým tlačidlom myši na stĺpce rok, mesiac, týždeň alebo deň na table Polia a potom vyberiete položku Nová hierarchia. Tento proces je podrobnejšie rozoberaný ďalej v tomto module.

Vytvorenie vizuálu

Ak chcete vytvoriť vizuál medzi tabuľkami predaja a objednávok, budete musieť vytvoriť vzťah medzi touto novou spoločnou tabuľkou dátumov a tabuľkami predaja a objednávok. V dôsledku toho budete môcť vytvárať vizuály pomocou novej tabuľky dátumov. Ak chcete vykonať túto úlohu, prejdite na kartu Model>Správa vzťahov, kde môžete vytvárať vzťahy medzi bežnou tabuľkou dátumov a tabuľkami predaja a objednávok pomocou stĺpca OrderDate (Dátum objednávky). Na nasledujúcej snímke obrazovky je zobrazený príklad takéhoto vzťahu.

Snímka obrazovky dialógového okna Vytvorenie vzťahu.

Po vytvorení vzťahov si môžete vytvoriť vlastný vizuál celkového predaja a množstva v objednávke podľa času s vašou spoločnou tabuľkou dátumov, ktorú ste vytvorili pomocou metódy jazyka DAX alebo doplnku Power Query.

Ak chcete určiť celkový predaj, je potrebné pridať všetky hodnoty predaja, pretože stĺpec Amount (Množstvo) v tabuľke predaja vyhľadáva iba príjem z každého predaja, a nie celkový príjem z predaja. Túto úlohu môžete dokončiť pomocou nasledujúcej kalkulácie mierky, ktorú vám objasníme v ďalších diskusiách. Výpočet, ktorý budete používať pri vytváraní tejto mierky, je nasledujúci:

#Total Sales = SUM(Sales[‘Amount’])

Po dokončení môžete tabuľku vytvoriť tak, že sa vrátite na kartu Vizualizácie a vyberiete vizuál Tabuľka. Chcete zobraziť celkový počet objednávok a predaj podľa rokov a mesiacov, takže zo svojej tabuľky dátumov potrebujete použiť iba stĺpec rokov a mesiacov, stĺpec OrderQty (Množstvo v objednávke) a mierku #TotalSales(#Celkový predaj). Keď sa oboznamujete s hierarchiami, môžete tiež vytvoriť hierarchiu, ktorá vám umožní prejsť z rokov na mesiace. V tomto príklade ich môžete vidieť umiestnené vedľa seba. Teraz ste úspešne vytvorili vizuál s bežnou tabuľkou údajov.

Snímka obrazovky so stĺpcom Common Date Column using DAX (Spoločný stĺpec dátumov pomocou jazyka DAX).