Časová inteligencia v doplnku Power Pivot for Excel

Jazyk DAX (Data Analysis Expressions) obsahuje 35 funkcií určených špeciálne na zhromažďovanie a porovnávanie údajov v priebehu času. Na rozdiel od funkcií dátumu a času jazyka DAX nemajú funkcie časovej inteligencie v Exceli nič podobné. Dôvodom je, že funkcie časovej inteligencie pracujú s údajmi, ktoré sa neustále menia v závislosti od kontextu, ktorý vyberiete v kontingenčných tabuľkách a vizualizáciách funkcie Power View.

Ak chcete pracovať s funkciami časovej inteligencie, musíte mať v dátovom modeli zahrnutú tabuľku dátumov. Tabuľka dátumov musí obsahovať stĺpec s jedným riadkom pre každý deň každého roka, ktorý je súčasťou údajov. Tento stĺpec sa považuje za stĺpec Dátum (hoci ho môžete pomenovať ľubovoľne). Mnohé funkcie časovej inteligencie vyžadujú stĺpec dátumu, aby mohli vykonávať výpočty podľa dátumov, ktoré vyberiete ako polia v zostave. Ak máte napríklad ukazovateľ, ktorý vypočítava záverečný zostatok na konci štvrťroka pomocou funkcie CLOSINGBALANCEQTR, na to, aby Power Pivot zistil, kedy skutočne nastáva koniec štvrťroka, musí odkazovať na stĺpec dátumu v tabuľke dátumov, aby zistil, kedy sa štvrťrok začína a končí. Ďalšie informácie o tabuľkách dátumov nájdete v téme Informácie o tabuľkách dátumov v doplnku Power Pivot v Exceli a ich vytváranie.

Funkcie

Funkcie vracajúce jeden dátum

Funkcie v tejto kategórii vrátia jeden dátum. Výsledok sa potom môže použiť ako argumenty pre iné funkcie.

Prvé dve funkcie v tejto kategórii vrátia prvý alebo posledný dátum v Date_Column v aktuálnom kontexte. Môže to byť užitočné, keď chcete nájsť prvý alebo posledný dátum transakcie určitého typu. Tieto funkcie používajú iba jeden argument – názov stĺpca dátumu v tabuľke dátumov.

Nasledujúce dve funkcie v tejto kategórii nájdu prvý alebo posledný dátum (alebo aj akúkoľvek inú hodnotu stĺpca), v ktorých výraz neobsahuje prázdnu hodnotu. Najčastejšie sa používa v situáciách, ako je inventár, keď chcete získať poslednú inventárnu sumu, ale neviete, kedy bola vykonaná posledná inventúra.

Šesť ďalších funkcií, ktoré vrátia jeden dátum, sú funkcie, ktoré vrátia prvý alebo posledný dátum mesiaca, štvrťroka alebo roka v rámci aktuálneho kontextu výpočtu.

Funkcie vrátené tabuľky dátumov

Existuje šestnásť funkcií časovej inteligencie, ktoré vracajú tabuľku dátumov. Najčastejšie sa tieto funkcie použijú ako argument NastaviťFilter pre funkciu CALCULATE . Podobne ako všetky funkcie časovej inteligencie v jazyku DAX, každá funkcia používa stĺpec dátumu ako jeden zo svojich argumentov.

Prvých osem funkcií v tejto kategórii začína stĺpcom dátumu v aktuálnom kontexte. Ak napríklad v kontingenčnej tabuľke používate ukazovateľ, v označeniach stĺpcov alebo riadkov sa môže nachádzať mesiac alebo rok. Úplným efektom je, že stĺpec s dátumom je filtrovaný tak, aby zahŕňal iba dátumy v aktuálnom kontexte. Vychádzajúc z aktuálneho kontextu týchto osem funkcií potom vypočíta predchádzajúci (alebo nasledujúci) deň, mesiac, štvrťrok alebo rok a vráti tieto dátumy vo forme tabuľky v jednom stĺpci. Funkcie "previous" pracujú v aktuálnom kontexte smerom dozadu od prvého dátumu a funkcie "next" sa v aktuálnom kontexte posunú dopredu od posledného dátumu.

Ďalšie štyri funkcie v tejto kategórii sú podobné, ale namiesto výpočtu predchádzajúceho (alebo nasledujúceho) obdobia vypočítavajú množinu dátumov v období od začiatku mesiaca (alebo od začiatku štvrťroka, od začiatku roka alebo v rovnakom období predchádzajúceho roka). Všetky tieto funkcie vykonávajú výpočty pomocou posledného dátumu v aktuálnom kontexte. Všimnite si, že funkcia SAMEPERIODLASTYEAR vyžaduje, aby aktuálny kontext obsahoval súvislé množiny dátumov. Ak aktuálny kontext nie je súvislá množina dátumov, funkcia SAMEPERIODLASTYEAR vráti chybu.

Posledné štyri funkcie v tejto kategórii sú o niečo zložitejšie a tiež o niečo výkonnejšie. Tieto funkcie slúžia na prechod z množiny dátumov, ktoré sú v aktuálnom kontexte, na novú množinu dátumov.

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

Funkcia DATESBETWEEN vypočíta množinu dátumov medzi zadaným počiatočným dátumom a koncovým dátumom. Zostávajúce tri funkcie posúvajú v aktuálnom kontexte určitý počet časových intervalov. Interval môže byť deň, mesiac, štvrťrok alebo rok. Tieto funkcie uľahčujú posunutie časového intervalu pre výpočet pomocou ktorejkoľvek z týchto možností:

  • Návrat o dva roky dozadu
  • Návrat o jeden mesiac späť
  • Prechod o tri štvrtiny dopredu
  • Návrat o 14 dní späť
  • Prechod o 28 dní dopredu

V každom prípade stačí zadať, ktorý interval a koľko z týchto intervalov sa má posunúť. Kladný interval sa posunie v čase dopredu, zatiaľ čo záporný interval sa posunie v čase späť. Samotný interval určuje kľúčové slovo DAY, MONTH, QUARTER alebo YEAR. Tieto kľúčové slová nie sú reťazce, preto by nemali byť v úvodzovkách.

Funkcie, ktoré vyhodnocujú výrazy v priebehu časového obdobia

Táto kategória funkcií vyhodnotí výraz za určité časové obdobie. To isté môžete dosiahnuť pomocou funkcie CALCULATE a ďalších funkcií časovej inteligencie. Príklad:

= TOTALMTD (Výraz, Date_Column [, NastaviťFilter])

je presne to isté ako:

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

Je však jednoduchšie používať tieto funkcie časovej inteligencie, keď sú vhodné pre problém, ktorý je potrebné vyriešiť:

  • TOTALMTD (Výraz, Date_Column [, NastaviťFilter])
  • TOTALQTD (Výraz, Date_Column [, NastaviťFilter])
  • TOTALYTD (Expression, Date_Column [, SetFilter] [,YE_Date]) *

Do tejto kategórie patrí aj skupina funkcií, ktoré vypočítavajú počiatočné a konečné zostatky. Pri používaní týchto funkcií by ste mali rozumieť určitým pojmom. Po prvé, ako sa vám pravdepodobne zdá zrejmé, počiatočný zostatok v ľubovoľnom období je rovnaký ako konečný zostatok za predchádzajúce obdobie. Konečný zostatok zahŕňa všetky údaje za koniec obdobia, zatiaľ čo počiatočný zostatok nezahŕňa žiadne údaje za aktuálne obdobie.

Tieto funkcie vždy vrátia hodnotu výrazu vyhodnoteného pre určitý časový bod. Časový bod, na ktorom nám záleží, je vždy posledná možná hodnota dátumu v kalendárnom období. Počiatočný zostatok vychádza z posledného dátumu predchádzajúceho obdobia, zatiaľ čo konečný zostatok vychádza z posledného dátumu aktuálneho obdobia. Aktuálne obdobie je vždy určené posledným dátumom v kontexte aktuálneho dátumu.

Ďalšie zdroje informácií

Články: Informácie o tabuľkách dátumov v doplnku Power Pivot v Exceli a ich vytváranie

Odkaz: Odkaz na funkciu jazyka DAX v Office.com

Ukážky: Modelovanie a analýza údajov o ziskoch a stratách pomocou doplnku Microsoft PowerPivot v Exceli