Časové měřítko v PowerPivotu v Excelu

Platí pro
Excel pro Microsoft 365 Excel 2024 Excel 2021 Excel 2019 Excel 2016

Jazyk DAX (Data Analysis Expressions) má 35 funkcí speciálně pro agregaci a porovnávání dat v průběhu času. Na rozdíl od funkcí data a času jazyka DAX funkce časového měřítka v Excelu nic podobného nemají. Je to proto, že funkce časového měřítka pracují s daty, která se neustále mění v závislosti na kontextu vybraném v kontingenčních tabulkách a vizualizacích nástroje Power View.

Abyste mohli pracovat s funkcemi časových řad, musíte mít datový model tabulku kalendářních dat. Tabulka kalendářních dat musí obsahovat sloupec s jedním řádkem pro každý den každého roku zahrnutého do dat. Tento sloupec je považován za sloupec Datum (i když jeho název lze libovolně pojmenovat). Mnoho funkcí časových řad vyžaduje sloupec data k výpočtu podle dat vybraných jako pole v sestavě. Pokud máte třeba míru, která vypočítává závěrečný zůstatek na konci čtvrtletí pomocí funkce CLOSINGBALANCEQTR, musí Power Pivot k tomu, aby věděl, kdy skutečně nastane konec čtvrtletí, odkazovat na sloupec kalendářních dat v tabulce kalendářních dat, aby věděl, kdy čtvrtletí začíná a končí. Další informace o tabulkách kalendářních dat najdete v tématu Princip tabulek kalendářních dat a jejich vytváření v PowerPivotu v Excelu.

Funkce

Funkce, které vracejí jedno datum

Funkce v této kategorii vracejí jedno datum. Výsledek pak můžete použít jako argumenty dalších funkcí.

První dvě funkce v této kategorii vracejí první nebo poslední datum v Date_Column v aktuálním kontextu. To může být užitečné, když chcete najít první nebo poslední datum, kdy jste měli transakci určitého typu. Tyto funkce používají jenom jeden argument, název sloupce kalendářního data v tabulce kalendářních dat.

Následující dvě funkce v této kategorii najdou první nebo poslední datum (nebo také libovolnou jinou hodnotu sloupce), kdy výraz obsahuje neprázdnou hodnotu. Nejčastěji se to používá v situacích, jako je například inventura, kdy chcete získat poslední množství zásob a nevíte, kdy byla poslední inventura provedena.

Šest dalších funkcí, které vracejí jedno datum, jsou funkce, které vracejí první nebo poslední datum v měsíci, čtvrtletí nebo roce v aktuálním kontextu výpočtu.

Funkce, které vracejí tabulku kalendářních dat

Existuje šestnáct funkcí časových řad, které vracejí tabulku kalendářních dat. Nejčastěji se tyto funkce používají jako argument funkce SetFilter u funkce VYPOČÍTAT . Podobně jako všechny funkce s časovou řadou i v jazyce DAX přebírá každá funkce jako jeden ze svých argumentů sloupec kalendářních dat.

Prvních osm funkcí v této kategorii začíná sloupcem s kalendářním datem v aktuálním kontextu. Pokud například používáte míru v kontingenční tabulce, může být na popiscích sloupců nebo řádků měsíc nebo rok. Čistým výsledkem je, že sloupec s daty je filtrován tak, aby obsahoval pouze data pro aktuální kontext. Počínaje tímto aktuálním kontextem potom těchto osm funkcí vypočítá předchozí (nebo následující) den, měsíc, čtvrtletí nebo rok a vrátí tato data ve formě tabulky s jedním sloupcem. "předchozí" funkce fungují zpětně od prvního data v aktuálním kontextu a "další" funkce se posunou dál od posledního data v aktuálním kontextu.

Následující čtyři funkce v této kategorii jsou podobné, ale místo výpočtu předchozího (nebo následujícího) období počítají sadu kalendářních dat v období, které je "od začátku měsíce" (nebo od začátku čtvrtletí, od začátku roku nebo od začátku roku nebo ve stejném období předchozího roku). Všechny tyto funkce provádějí výpočty s použitím posledního data v aktuálním kontextu. Všimněte si, že funkce SAMEPERIODLASTYEAR vyžaduje, aby aktuální kontext obsahoval souvislou sadu kalendářních dat. Pokud aktuální kontext netvoří souvislou sadu kalendářních dat, vrátí funkce SAMEPERIODLASTYEAR chybu.

Poslední čtyři funkce v této kategorii jsou trochu složitější a taky trochu výkonnější. Tyto funkce slouží k přechodu ze sady kalendářních dat, které jsou v aktuálním kontextu, na novou sadu dat.

  • DATEADD (Date_Column, Number_of_Intervals, interval)
  • DATESBETWEEN (Date_Column, Start_Date, End_Date)
  • DATESINPERIOD (Date_Column, Start_Date, Number_of_Intervals, interval)

Funkce DATESBETWEEN vypočítá sadu kalendářních dat mezi zadaným počátečním a koncovým datem. Zbývající tři funkce se od aktuálního kontextu posunou o určitý počet časových intervalů. Interval může být den, měsíc, čtvrtletí nebo rok. Tyto funkce usnadňují posun časového intervalu pro výpočet pomocí některé z následujících možností:

  • Zpět o dva roky zpět
  • Zpět o měsíc
  • Přechod vpřed o tři čtvrtiny
  • Zpět o 14 dní
  • Přechod vpřed o 28 dnů

V každém případě stačí jenom zadat, který interval a kolik z těchto intervalů se má posunout. Kladný interval se posune v čase dopředu, zatímco záporný interval se posune zpět v čase. Samotný interval je určen klíčovým slovem DEN, MĚSÍC, ČTVRTLETÍ nebo ROK. Tato klíčová slova nejsou řetězce, proto by neměla být v uvozovkách.

Funkce, které vyhodnocují výrazy během určitého časového období

Tato kategorie funkcí vyhodnocuje výraz během zadaného časového období. Toho lze dosáhnout pomocí funkce CALCULATE a dalších funkcí časových řad. Příklad:

= TOTALMTD (výraz, Date_Column [, nastavitfiltr])

je přesně to stejné jako:

= CALCULATE (Expression, DATESMTD (Date_Column)[, SetFilter])

Snadněji se ale používají tyto funkce časového měřítka, pokud se dobře hodí pro problém, který je třeba vyřešit:

  • TOTALMTD (výraz, Date_Column [, NastavitFiltr])
  • TOTALQTD (výraz, Date_Column [, NastavitFiltr])
  • TOTALYTD (výraz, Date_Column [, NastavitFiltr] [,YE_Date]) *

Do této kategorie patří také skupina funkcí, které počítají počáteční a konečný zůstatek. U těchto konkrétních funkcí byste měli porozumět určitým konceptům. Za prvé, jak se vám může zdát zřejmé, počáteční zůstatek za jakékoli období je stejný jako závěrečný zůstatek za předchozí období. Závěrečný zůstatek zahrnuje všechna data ke konci období, zatímco počáteční zůstatek nezahrnuje žádná data z aktuálního období.

Tyto funkce vždy vracejí hodnotu výrazu, který se vyhodnocoval pro určitý časový bod. Časový okamžik, který nás zajímá, je vždy poslední možná hodnota data v kalendářním období. Počáteční zůstatek je založen na posledním datu předchozího období, zatímco konečný zůstatek je založen na posledním datu aktuálního období. Aktuální období je vždy určeno posledním datem v kontextu aktuálního data.

Další zdroje

Články: Princip tabulek kalendářních dat a jejich vytváření v PowerPivotu v Excelu

Odkaz: Informace o funkcích jazyka DAX na Office.com

Ukázky: Modelování a analýza dat zisků a ztrát pomocí nástroje Microsoft PowerPivot v Excelu