První část příspěvku obsahuje výpočty jednotlivých počítaných sloupců, které jsou určeny pro doplnění do již existujícího kalendáře v modelu. V druhé části příspěvku je pak dostupný kompletní DAX kód, který vygeneruje celý ISO týdenní kalendář se všemi sloupci v jednom kroku. Soubor s řešením je dostupný ke stažení níže pod tímto příspěvkem.
Stejně jako je tomu u kalendáře standardního, i v tomto případě je lepší variantou načítat již dříve vytvořený kalendář ze zdrojové databáze, a to z důvodu optimálnější komprese dat v modelu. Na druhou stranu, kalendářní tabulka obvykle obsahuje pouze relativně malé množství záznamů, a proto by neměl být z pohledu výkonnosti žádný problém vytvořit si v modelu vlastní kalendář pomocí DAX kódu.
Vlastní ISO týdenní kalendář
ISO týdenní kalendář - počítané sloupce
V této části příspěvku je zobrazen postup, jak si pomocí počítaných sloupců postupně rozšířit stávající datumovou tabulku o nové sloupce, které budou splňovat požadavky ISO týdenního kalendáře a které mohou usnadnit tvorbu časových kalkulací pomocí základních DAX funkcí. Základním předpokladem pro následující výpočty je dostupnost sloupce ve formátu DATE nebo DATETIME v použité datumové tabulce. Tento sloupec musí obsahovat jedinečné hodnoty pro každý den, bez duplicit a bez mezer mezi dny. Některé z následujících výpočtů mohou odkazovat na předcházející počítané sloupce. Z tohoto důvodu doporučuji přidávat jednotlivé sloupce do stávající datumové tabulky v pořadí tak, jak jsou uvedené v tomto příspěvku. Jednotlivé počítané sloupce mohou být obvykle vytvořeny více různými způsoby, níže uvedený postup je proto pouze jeden z více možných přístupů.
Datumová tabulka použitá v tomto příkladu, která slouží jako základ pro nové počítané sloupce, má následující strukturu.
ISO týden
ISO týden je základní stavební jednotkou ISO týdenního kalendáře. Pořadové číslo týdne podle ISO standardu můžeme získat pomocí následujícího výpočtu.
Počítaný sloupec:
Funkce WEEKNUM() vrací pořadové číslo týdne na základě data v aktuálním řádku tabulky. Druhým argumentem funkce WEEKNUM() je systém, podle kterého chceme určit pořadí týdne v roce. Číslo 21 ve druhém argumentu funkce představuje systém založený na ISO 8601 standardu. Jak je možné vidět na obrázku níže, pořadí ISO týdnů může přesahovat kalendářní roky.
ISO týden pořadí
Sloupec ISO týden pořadí je jeden ze sloupců, který je určený pouze pro DAX výpočty, a měl by proto být skrytý pro uživatele reportů. Tento sloupec bude obsahovat pořadové číslo ISO týdnů napříč roky. Pomocí sloupce 'Date'[ISO týden pořadí] se můžeme snadno pohybovat zpět nebo vpřed v čase pomocí jednoduchého odčítaní nebo přičítání týdnů.
Počítaný sloupec:
První pondělí, které je oficiálně podporované v jazyku DAX, je pondělí 2. ledna 1900. Tento den je v číselném vyjádření číslo 2. Výše uvedený výpočet pak vychází z faktu, že funkce INT() zaokrouhluje desetinná čísla vždy dolů. Číslo pět přidáváme ke každému datu proto, aby každé pondělí bylo dělitelné sedmi beze zbytku. Všechny následující dny v aktuálním týdnu pak budou díky funkci INT() obsahovat stejnou hodnotu, jako pondělí tohoto týdne. Výsledná hodnota se zvýší každé pondělí a je konstantní všechny následující dny v týdnu.
ISO čtvrtletí číslo
Výpočet sloupce ISO čtvrtletí číslo je založen na předpokladu, že první tři čtvrtletí v ISO týdenním kalendáři obsahují vždy stejný počet týdnů. Čtvrté čtvrtletí pak může obsahovat 13 nebo 14 týdnů.
Počítaný sloupec:
Na základě sloupců ISO čtvrtletí číslo a ISO týden číslo můžeme vytvořit sloupec s pořadím týdnů ve čtvrtletí.
ISO pořadí týdne ve čtvrtletí
K vytvoření sloupce s pořadím týdne ve čtvrtletí je použita opět jednoduchá matematika, kdy od čísla ISO týdne v roce odečteme všechny týdny v předcházejícím ISO čtvrtletí.
Počítaný sloupec:
Sloupec ISO pořadí týdne ve čtvrtletí je opět sloupec, který může sloužit pro zjednodušení určitých typů DAX výpočtů.
ISO rok číslo
ISO rok neodpovídá kalendářnímu roku v případě, kdy ISO týden na začátku kalendářního roku spadá do předcházejícího ISO roku, nebo pokud ISO týden na konci kalendářního roku spadá do následujícího ISO roku. Pro vytvoření počítaného sloupce ISO rok číslo proto budeme potřebovat sloupec s číslem ISO týdne v roce, který jsme si již vytvořili v jednom z předcházejících kroků, a dále sloupec s kalendářním týdnem v roce, jehož definice může vypadat následovně.
Počítaný sloupec:
Ve druhém argumentu funkce WEEKNUM() je číslo 2, které zastupuje systém kalendářních týdnů začínajících v pondělí. Definice sloupce s ISO rokem pak může vypadat následovně.
Počítaný sloupec:
Dalším důležitým sloupcem pro zjednodušení DAX výpočtů je sloupec s pořadím dnů v ISO roce.
ISO den v roce
Ve výpočtu pořadí dne v ISO roce budeme jednoduše zjišťovat počet řádků v kalendářní tabulce, pro které platí, že ISO rok odpovídá ISO roku v aktuálním řádku a datum v aktuálním řádku je menší nebo roven datu v prohledávané tabulce.
Počítaný sloupec:
Sloupec ISO den v roce je důležitý zejména pro výpočty, ve kterých chceme porovnávat výsledky v aktuálním období s výsledky v předcházejícím období. Jedná se tedy opět o pomocný sloupec určený primárně pro DAX výpočty.
ISO čtvrtletí pořadí
Sloupec ISO čtvrtletní pořadí je sloupec, který bude obsahovat pořadové číslo čtvrtletí napříč roky, a jeho výpočet může vypadat následovně.
Počítaný sloupec:
Sloupec ISO čtvrtletí pořadí obsahuje číselné pořadí čtvrtletí napříč roky. Jedná se o sloupec, který umožňuje jednoduché přesuny napříč čtvrtletími pomocí odečítání a přičítání. Sloupec ISO čtvrtletí pořadí je určený pouze pro DAX výpočty, a měl by být skrytý v uživatelských nástrojích.
ISO den ve čtvrtletí
ISO den ve čtvrtletí je další sloupec určený pouze pro DAX výpočty, a je vypočítán podobným způsobem jako sloupec ISO den v roce. Rozdíl je pouze ve filtru, který nyní obsahuje hodnoty ze sloupce 'Date'[ISO čtvrtletí pořadí], namísto sloupce 'Date'[ISO rok číslo], jak tomu bylo v případě sloupce ISO den v roce.
Počítaný sloupec:
Následující dva sloupce jsou pomocné sloupce, určené pro zjednodušení výpočtů mezi přestupnými ISO roky.
ISO den v roce (do 364)
Standardní ISO rok má 364 dnů (52 týdnů x 7 dnů v týdnu). Jednou za pět let má ISO rok 371 dnů. Sloupec 'Date'[ISO den v roce (do 364)] je pomocný sloupec určený primárně pro výpočty hodnot v předchozím roce. Na rozdíl od sloupce 'Date'[ISO den v roce] bude v tomto sloupci v přestupném ISO roce přiřazena hodnota 364 pro všechny dny, které jsou v přestupném ISO roce "navíc", v porovnání se standardním ISO rokem. Při použití tohoto sloupce k výpočtu hodnot v předchozím roce (PY) budou všechny hodnoty ze dnů s pořadovým číslem 365 až 371 přiřazeny v aktuálním roce k 364 dnu, protože v nepřestupném roce dny s pořadovým číslem 365 až 371 nejsou k dispozici.
Počítaný sloupec:
Sloupec 'Date'[ISO den v roce (do 364)] je pomocný sloupec určený pouze pro zjednodušení DAX výpočtů, a měl by proto být pro uživatele skrytý. Podobný sloupec si vytvoříme také pro účely výpočtů hodnot v předchozím čtvrtletí.
ISO den ve čtvrtletí (do 91)
Standardní čtvrtletí obsahuje 13 týdnů a 91 dnů. V přestupném ISO roce obsahuje čtvrté čtvrtletí 98 dnů. Následující sloupec je určen pro zjednodušení výpočtů hodnot v předchozím čtvrtletí, kdy dny ve čtvrtletí s pořadovým číslem 92 až 98 budou přiřazeny k číslu 91.Počítaný sloupec:
Tento sloupec je opět určený pouze pro DAX výpočty, a měl by být v uživatelských nástrojích skrytý. Následující sloupce jsou již primárně určeny pro použití v uživatelských nástrojích.
ISO týden rok, ISO čtvrtletí rok, ISO rok
Sloupce určené pro zobrazení v uživatelských nástrojích by měli obsahovat také určité označení, že atributy použité ve vizuálech pocházejí z ISO týdenního kalendáře, a ne ze standardního kalendáře. Tím se můžeme vyhnout určitému matení uživatelů reportů, zejména pokud jsou v modelu také atributy patřící do standardního kalendáře. Výběr "značky" u každého atributu je čistě individuální. U roků může jít například o značku ISO Rok-XXXX, u týdnů o značku ISO Týden-XX, a tak dále. Pro vytvoření sloupců určených k použití v reportech již máme všechny hodnoty k dispozici z předcházejících výpočtů. Definice jednotlivých sloupců je proto pouhé zřetězení hodnot v aktuálním řádku tabulky se zvolenou značkou nebo značkami. Tyto značky si může každý autor snadno upravit nebo nahradit za vlastní systém značení.
Počítané sloupce:
Jak už bylo uvedeno výše, v případě výše uvedených tří sloupců se jedná pouze o ilustrativní příklady, a je čistě na autorovi reportů, jaký systém a jaké atributy a jejich kombinace budou dostupné v reportech.
V následující části je k dispozici DAX kód, který vygeneruje celou tabulku s ISO týdenním kalendářem se všemi dříve uvedenými sloupci v jednom kroku.
Vytvoření vlastního ISO týdenního kalendáře
Všechny výše uvedené výpočty je možné vytvořit v jednom kroku v definici počítané tabulky. Protože jsou výpočty některých sloupců závislé na hodnotách z dříve vytvořených sloupců, celý výpočet je rozdělen do několika dílčích kroků pomocí proměnných. Logika výpočtů jednotlivých sloupců je však stejná, jak tomu bylo v předcházející části příspěvku. Každý si navíc může v prvních dvou proměnných definovat rozsah tabulky tak, aby datumová tabulka pokrývala období potřebné pro výpočty v reportech.
Počítaná tabulka:
Způsobů vytvoření ISO týdenního kalendáře je v jazyku DAX více. Výše uvedený způsob je proto pouze jeden z více možných. Stejně tak struktura kalendáře může být v různých modelech jiná, v závislosti na požadavcích uživatelů a na typech výpočtů.
Práce s ISO týdenním kalendářem
V této části příspěvku jsou uvedeny jednoduché ukázky časových kalkulací, které vycházejí z dříve vytvořeného ISO týdenního kalendáře. Jedná se o jednoduché ukázky, jejichž cílem je pouze znázornění způsobu práce s vlastním týdenním kalendářem.
Vlastní časové kalkulace vytvořené pomocí základních DAX funkcí budou vycházet z jednotlivých pomocných sloupců vytvořených právě za účelem zjednodušení časových kalkulací. Uvažujme například výpočet prodejů v celém předchozím roce (PYC). K vytvoření měřítka, které bude vracet sumu za prodané produkty v celém předcházejícím roce, budeme potřebovat načíst číslo představující aktuální ISO rok, a od tohoto čísla jednoduše odečteme číslo jedna, abychom se dostali ve výpočtu do předcházejícího roku. Takovýto výpočet může vypadat následovně.
Měřítko:
Výsledkem nového měřítka je suma za prodané produkty v celém předcházejícím roce.
Dalším typickým příkladem může být výpočet kumulativních prodejů v aktuálním roce. V tomto příkladu budeme potřebovat vytvořit filtr, který bude obsahovat aktuální rok a všechny dny v ISO roce, které jsou menší než je poslední den dostupný v aktuálním kontextu vyhodnocení.
Měřítko:
Výsledkem nového měřítka bude kumulativní součet prodejů v aktuálním roce.
Na základě měřítek [Prodeje YTD (jednoduché)] a [Prodeje PYC (jednoduché)] můžeme snadno vytvořit další měřítko, které bude vracet záporné hodnoty ve dnech, ve kterých ještě kumulativní prodeje v aktuálním roce nedosáhly stejné hodnoty jako v předchozím roce, a kladné hodnoty ve dnech, kdy kumulativní suma prodejů v aktuálním roce přesáhne prodeje v roce předcházejícím.
Měřítko:
Jak můžeme vidět na obrázku níže, tak například v roce 2019 překonaly prodeje v tomto roce prodeje v roce předcházejícím už v týdnu číslo 41.
Komentáře
Okomentovat