Vytvoření tabulky kalendářních dat
Při vytváření sestav v Power BI patří mezi běžné obchodní požadavky provádění výpočtů na základě kalendářních dat a času. Organizace chtějí vědět, jak se daří jejich obchodní činnosti v měsících, čtvrtletích, fiskálních rocích atd. Z tohoto důvodu je důležité, aby tyto hodnoty orientované na čas byly správně formátovány. Power BI automaticky rozpoznává sloupce a tabulky s kalendářními daty, můžou ale nastat situace, kdy budete muset podniknout další kroky, abyste získali data ve formátu, který vaše organizace vyžaduje.
Předpokládejme například, že ve vaší organizaci vyvíjíte sestavy pro prodejní tým. Databáze obsahuje tabulky s prodeji, objednávkami, produkty a dalšími informacemi. Všimnete si, že mnoho z těchto tabulek včetně tabulek Sales (Prodeje) a Orders (Objednávky) obsahuje vlastní sloupce kalendářních dat, jak je vidět u sloupců ShipDate a OrderDate v tabulkách Sales a Orders. Máte za úkol vyvinout tabulku celkových prodejů a objednávek uspořádanou podle let a měsíců. Jak můžete vytvořit vizuál s více tabulkami, z nichž každá odkazuje na svůj vlastní sloupec kalendářních dat?
Abyste tento problém vyřešili, můžete vytvořit společnou tabulku kalendářních dat, kterou bude možné používat ve více tabulkách. Následující část popisuje, jak můžete tento úkol provést v Power BI.
Vytvoření společné tabulky kalendářních dat
Mezi způsoby, jak můžete vytvořit společnou tabulku kalendářních dat, patří:
Zdrojová data
DAX
Power Query
Zdrojová data
V některých případech už zdrojové databáze a datové sklady mají vlastní tabulky kalendářních dat. Pokud správce, který danou databázi navrhl, odvedl pečlivou práci, můžete tyto tabulky používat k provádění následujících úkolů:
Identifikace firemních svátků
Oddělení kalendářního a fiskálního roku
Identifikace víkendových a pracovních dnů
Tabulky ze zdrojových dat jsou vyspělé a připravené k okamžitému použití. Pokud máte tabulku jako takovou, přeneste ji do sémantického modelu a nepoužívejte žádné další metody popsané v této části. Doporučujeme, abyste použili zdrojovou tabulku kalendářních dat, protože je pravděpodobně sdílená s jinými nástroji, které můžete používat vedle Power BI.
Pokud nemáte tabulku ze zdrojových dat, můžete k vytvoření společné tabulky kalendářních dat použít jiné způsoby.
DAX
K vytvoření společné tabulky kalendářních dat můžete použít funkce jazyka DAX (Data Analysis Expression) CALENDARAUTO() nebo CALENDAR(). Funkce CALENDAR() vrací souvislý rozsah kalendářních dat na základě počátečního a koncového data – tato data se zadávají jako argumenty funkce. Případně funkce CALENDARAUTO() vrátí souvislý úplný rozsah kalendářních dat, která jsou automaticky určena z vašeho sémantického modelu. Počáteční datum se vybere jako nejstarší datum, které existuje v sémantickém modelu, a koncové datum je poslední datum, které existuje v sémantickém modelu, plus data vyplněná do fiskálního měsíce, která můžete zahrnout jako argument funkce CALENDARAUTO(). Pro účely tohoto příkladu se používá funkce CALENDAR(), protože chcete zobrazit jenom data od 31. května 2011 (první den, kdy v tabulce Sales začala být sledována data) a pokračovat dalších 10 let.
V Power BI Desktop vyberte Nová tabulka a zadejte následující vzorec DAX:
Dates = CALENDAR(DATE(2011, 5, 31), DATE(2022, 12, 31))
Teď máte sloupec kalendářních dat, který můžete používat. Tento sloupec je však poněkud nedostačující. Můžete chtít také zobrazit sloupce jen s roky, čísly měsíců, týdny v roce a dny v týdnu. Tento úkol můžete provést tak, že na pásu karet vyberete Nový sloupec a zadáte následující rovnici DAX, která načte rok z vaší tabulky kalendářních dat.
Year = YEAR(Dates[Date])
Stejným postupem můžete načíst číslo měsíce, číslo týdne a den v týdnu:
MonthNum = MONTH(Dates[Date])
WeekNum = WEEKNUM(Dates[Date])
DayoftheWeek = FORMAT(Dates[Date], "DDDD")
Když to dokončíte, bude vaše tabulka obsahovat sloupce, které jsou zobrazeny na následujícím obrázku.
Nyní jste vytvořili společnou tabulku kalendářních dat pomocí jazyka DAX. Tento proces pouze přidá novou tabulku do sémantického modelu. Stále budete muset vytvořit relace mezi tabulkou kalendářních dat a tabulkami Sales a Order a potom tabulku označit jako oficiální tabulku kalendářních dat sémantického modelu. Před dokončením těchto úkolů však nezapomeňte zvážit další způsob vytvoření společné tabulky kalendářních dat – pomocí Power Query.
Power Query
K definování společné tabulky kalendářních dat můžete použít jazyk M, vývojový jazyk, který se používá k vytváření dotazů v Power Query.
V Power BI Desktopu vyberte Transformovat data. To vás přenese do Power Query. Na prázdném místě levého podokna Dotazy kliknutím pravým tlačítkem otevřete následující rozevírací nabídku, kde vyberete Nový dotaz > prázdný dotaz.
Ve výsledném zobrazení Nový dotaz zadejte následující vzorec v jazyce M pro vytvoření tabulky kalendáře:
= List.Dates(#date(2011,05,31), 365*10, #duration(1,0,0,0))
V případě prodejních dat budete chtít, aby počáteční datum odpovídalo nejstaršímu datu, které máte ve svých datech: 31. května 2011. Navíc chcete vidět kalendářní data pro dalších 10 let včetně kalendářních dat v budoucnosti. Tento přístup zajistí, že když budou přitékat nová prodejní data, nebudete muset tuto tabulku vytvářet znovu. Můžete také změnit trvání. V tomto případě chcete mít datový bod pro každý den, ale můžete také používat přírůstky v hodinách, minutách a sekundách. Výsledek si můžete prohlédnout na následujícím obrázku.
Po úspěšném dokončení tohoto postupu si všimnete, že máte místo tabulky kalendářních dat seznam kalendářních dat. Pokud chcete tuto chybu opravit, přejděte na pásu karet na kartu Transformace a vyberte Převést > na tabulku. Jak název napovídá, tato funkce převede váš seznam na tabulku. Sloupec můžete také přejmenovat na DateCol.
Dále budete chtít do své nové tabulky přidat sloupce, abyste viděli kalendářní data jako roky, měsíce, týdny a dny a mohli jste ve svém vizuálu vytvořit hierarchii. Prvním úkolem je změnit typ sloupce tak, že vyberete ikonu vedle názvu sloupce a ve výsledné rozevírací nabídce vyberete typ Datum.
Po výběru typu Datum můžete přidat sloupce pro roky, měsíce, týdny a dny. Přejděte na Přidat sloupec, v části Datum vyberte rozevírací nabídku a pak vyberte Rok, jak je znázorněno na následujícím obrázku.
Všimněte si, že nástroj Power BI přidal sloupec se všemi roky, které lze získat ze sloupce DateCol.
Stejný postup proveďte pro měsíce, týdny a dny. Když tento postup dokončíte, bude vaše tabulka obsahovat sloupce, které jsou zobrazeny na následujícím obrázku.
Nyní jste úspěšně použili Power Query k vytvoření společné tabulky kalendářních dat.
Předchozí kroky ukazují, jak tabulku dostat do sémantického modelu. Nyní musíte tuto tabulku označit jako oficiální tabulku kalendářních dat, aby ji nástroj Power BI mohl rozpoznat pro všechny budoucí hodnoty, a zajistit, aby měla správné formátování.
Označení oficiální tabulky kalendářních dat
Prvním úkolem při označování tabulky jako oficiální tabulky kalendářních dat je vyhledání této nové tabulky v podokně Pole. Klikněte pravým tlačítkem na název tabulky a pak vyberte Označit jako tabulku kalendářních dat, jak je znázorněno na následujícím obrázku.
Při označování tabulky jako tabulky kalendářních dat provádí Power BI ověření, aby se zajistilo, že data neobsahují žádné hodnoty null, jsou jedinečná a v určitém období obsahují souvislé hodnoty kalendářních dat. V tabulce můžete také zvolit konkrétní sloupce, které chcete označit jako datum, což může být užitečné v případě, že máte v tabulce mnoho sloupců. Klikněte pravým tlačítkem na tabulku, vyberte Označit jako tabulku kalendářních dat a pak vyberte Nastavení tabulky kalendářních dat. Zobrazí se následující okno, ve kterém můžete zvolit, který sloupec má být označený jako Datum.
Když vyberete Označit jako tabulku kalendářních dat, budou z pole Datum v tabulce, kterou jste označili jako tabulku kalendářních dat, odebrány automaticky vygenerované hierarchie. Ostatní pole kalendářních dat budou dál obsahovat automatickou hierarchii, dokud nevytvoříte relaci mezi daným polem a tabulkou kalendářních dat nebo dokud nevypnete funkci Automatické datum a čas. Hierarchii do společné tabulky kalendářních dat můžete přidat ručně kliknutím pravým tlačítkem myši na sloupce rok, měsíc, týden nebo den v podokně Pole a výběrem možnosti Nová hierarchie. Tento proces je podrobněji popsán dále v tomto modulu.
Vytvoření vizuálu
Když budete chtít vytvořit vizuál mezi tabulkami Sales a Orders, budete muset vytvořit relaci mezi touto novou společnou tabulkou kalendářních dat a tabulkami Sales a Orders. Pak budete moct vytvářet vizuály pomocí této nové tabulky kalendářních dat. Když budete chtít tento úkol provést, přejděte na kartu Model>Spravovat relace, kde můžete vytvořit relace mezi společnou tabulkou kalendářních dat a tabulkami Orders a Sales pomocí sloupce OrderDate. Na následujícím screenshotu je příklad takové relace.
Po vytvoření relací můžete vytvořit vizuál pro celkové prodeje a množství objednávek podle času pomocí společné tabulky kalendářních dat, kterou jste vyvinuli pomocí metody využívající jazyk DAX nebo Power Query.
Chcete-li určit celkové prodeje, musíte sečíst všechny prodeje, protože sloupec Amount (Částka) v tabulce Sales obsahuje pouze tržby za jednotlivé prodeje, nikoli celkové prodejní tržby. Tento úkol můžete dokončit pomocí následujícího výpočtu míry, který bude vysvětlen později. Při vytváření této míry použijete následující výpočet:
#Total Sales = SUM(Sales[‘Amount’])
Po dokončení můžete vytvořit tabulku tak, že se vrátíte na kartu Vizualizace a vyberete vizuál Tabulka. Chcete zobrazit celkové objednávky a prodeje podle roků a měsíců, takže chcete zahrnout pouze sloupce Year a Month ze své tabulky kalendářních dat, sloupec OrderQty a míru #TotalSales. Když se seznámíte s hierarchiemi, můžete také vytvořit hierarchii, která vám umožní přecházet k podrobnější úrovni z roků na měsíce. V tomto příkladu je můžete zobrazit vedle sebe. Nyní jste úspěšně vytvořili vizuál se společnou tabulkou kalendářních dat.
