A Power Pivot dátumtáblái nélkülözhetetlenek az adatok böngészéséhez és kiszámításához az idő múlásával. Ez a cikk részletesen ismerteti a dátumtáblákat, és hogy hogyan hozhatja létre őket a Power Pivotban. Ez a cikk különösen a következőket ismerteti:
-
Miért fontos egy dátumtábla az adatok dátum és idő szerinti böngészéséhez és kiszámításához?
-
Dátumtábla hozzáadása az adatmodellhez a Power Pivot használatával.
-
Új dátumoszlopok , például Év, Hónap és Időszak létrehozása dátumtáblázatban.
-
Dátumtáblák és ténytáblák közötti kapcsolatok létrehozása.
-
Hogyan dolgozhat az idővel.
Ez a cikk a Power Pivot új felhasználóinak szól. Fontos azonban, hogy már tisztában legyen az adatok importálásával, a kapcsolatok létrehozásával, valamint a számított oszlopok és mértékek létrehozásával.
Ez a cikk nem ismerteti, hogyan használható a DAX Time-Intelligence függvények mértékképletekben. További információ a mértékek DAX időintelligencia-függvényekkel való létrehozásáról: Időintelligencia az ExcelBen a Power Pivotban.
Megjegyzés: A Power Pivotban a "mérték" és a "számított mező" nevek szinonimák. Ebben a cikkben a névmértékeket használjuk. További információ: Mértékek a Power Pivotban.
Tartalom
A dátumtáblák ismertetése
Szinte minden adatelemzés magában foglalja az adatok dátumok és időpontok szerinti böngészését és összehasonlítását. Előfordulhat például, hogy össze szeretné adni az előző pénzügyi negyedév értékesítési összegeit, majd össze szeretné hasonlítani ezeket az összegeket más negyedévekkel, vagy ki szeretné számítani egy számla hónap végi záróegyenlegét. Ezekben az esetekben dátumokat használ egy adott időszakra vonatkozó értékesítési tranzakciók vagy egyenlegek csoportosítására és összesítésére.
Power View-jelentés
A dátumtáblák számos különböző dátum- és időábrázolást tartalmazhatnak. A dátumtáblák például gyakran tartalmaznak olyan oszlopokat, mint a Pénzügyi év, a Hónap, a Negyedév vagy az Időszak, amelyeket mezőlista mezőiként választhat ki, amikor kimutatásokban vagy Power View-jelentésekben szeleteli és szűri az adatokat.
A Power View mezőlistája
Ahhoz, hogy az olyan dátumoszlopok, mint a Year, a Month és a Quarter, a saját tartományukon belüli összes dátumot tartalmazzák, a dátumtáblának legalább egy olyan oszloppal kell rendelkeznie, amely egybefüggő dátumkészlettel rendelkezik. Ez azt jelzi, hogy ennek az oszlopnak minden naphoz egy sort kell tartalmaznia a dátumtábla minden évéhez.
Ha például a böngészni kívánt adatok 2010. február 1. és 2012. november 30. közötti dátumokat tartalmaznak, és egy naptári évről számolnak be, akkor egy legalább 2010. január 1. és 2012. december 31. közötti dátumtartományt tartalmazó dátumtáblát kell használnia. A dátumtáblázat minden évének tartalmaznia kell az év összes napját. Ha rendszeresen frissíti az adatokat újabb adatokkal, érdemes lehet a befejezési dátumot egy-két évvel túllépni, hogy ne kelljen frissítenie a dátumtáblát az idő múlásával.
Dátumtábla egybefüggő dátumkészlettel
Ha pénzügyi évről készít jelentést, létrehozhat egy dátumtáblát, amely az egyes pénzügyi évekhez tartozó dátumok egybefüggő készletét tartalmazza. Ha például a pénzügyi év március 1-jén kezdődik, és a 2010-es pénzügyi évek adatai az aktuális dátumig (például 2013. februárban) vannak megadva, létrehozhat egy dátumtáblát, amely 2009.03.01-én kezdődik, és a 2013-as pénzügyi év utolsó dátumáig minden pénzügyi évben legalább minden napot tartalmaz.
Ha a naptári évről és a pénzügyi évről is jelentést készít, nem kell külön dátumtáblákat létrehoznia. Egyetlen dátumtábla tartalmazhat oszlopokat egy naptári évhez, pénzügyi évhez és akár tizenhárom négyhetes időszaknaptárhoz is. A legfontosabb, hogy a dátumtáblázat az összes belefoglalt évre vonatkozó dátumok egybefüggő készletét tartalmazza.
Dátumtábla hozzáadása az adatmodellhez
Az adatmodellhez többféleképpen adhat dátumtáblát:
-
Importálás relációs adatbázisból vagy más adatforrásból.
-
Hozzon létre egy dátumtáblázatot az Excelben, majd másolja vagy csatolja az új táblázatot a Power Pivotban.
-
Importálás Microsoft Azure Marketplace-ről.
Vizsgáljuk meg ezeket közelebbről.
Importálás relációs adatbázisból
Ha az adatok egy részét vagy egészét egy adattárházból vagy más típusú relációs adatbázisból importálja, valószínű, hogy már létezik egy dátumtábla és a közötte és az importált többi adat közötti kapcsolatok. A dátumok és a formátum valószínűleg megegyezik a tényadatokban szereplő dátumokkal, és a dátumok valószínűleg a múltban kezdődnek, és messze a jövőbe mennek. Az importálni kívánt dátumtábla nagyon nagy lehet, és olyan dátumtartományt tartalmazhat, amelyet az adatmodellben fel kell vennie. A Power Pivot Táblaimportáló varázslójának speciális szűrőfunkcióival szelektíven kiválaszthatja csak a kívánt dátumokat és oszlopokat. Ez jelentősen csökkentheti a munkafüzet méretét és javíthatja a teljesítményt.
Tábla importálása varázsló
A legtöbb esetben nem kell további oszlopokat létrehoznia, például a Pénzügyi év, a Hét, a Hónap neve stb. oszlopokat, mert ezek már léteznek az importált táblában. Bizonyos esetekben azonban, miután importálta a dátumtáblát az adatmodellbe, előfordulhat, hogy egy adott jelentési igénytől függően további dátumoszlopokat kell létrehoznia. Szerencsére ez könnyen elvégezhető a DAX használatával. A dátumtábla mezőinek létrehozásáról később olvashat bővebben. Minden környezet más. Ha nem biztos abban, hogy az adatforrások kapcsolódó dátum- vagy naptártáblával rendelkeznek-e, forduljon az adatbázis rendszergazdájához.
Dátumtábla létrehozása az Excelben
Létrehozhat egy dátumtáblát az Excelben, majd átmásolhatja egy új táblába az adatmodellben. Ez nagyon könnyen elvégezhető, és sok rugalmasságot biztosít.
Amikor létrehoz egy dátumtáblát az Excelben, egyetlen oszloppal kezdi a dátumok egybefüggő tartományát. Ezután excel-képletekkel további oszlopokat is létrehozhat az Excel-munkalapon, például Év, Negyedév, Hónap, Pénzügyi év, Időszak stb., vagy miután a táblázatot az adatmodellbe másolta, számított oszlopokként is létrehozhatja őket. Ha további dátumoszlopokat hoz létre a Power Pivotban, a cikk Új dátumoszlopok hozzáadása a dátumtáblázathoz című szakaszában olvashat.
Útmutató: Dátumtábla létrehozása az Excelben, majd másolása az adatmodellbe
-
Az Excelben egy üres munkalap A1 cellájába írja be az oszlopfejléc nevét a dátumtartomány azonosításához. Ez általában a Következőhöz hasonló:Date, DateTime vagy DateKey.
-
Az A2 cellába írjon be egy kezdő dátumot. Például : 2010.01.01.
-
Kattintson a kitöltőjelre, és húzza le egy záró dátumot tartalmazó sorszámra. Például : 2016. 12. 31.
-
Jelölje ki a Date oszlop összes sorát (beleértve az A1 cellában lévő fejlécnevet is).
-
A Stílusok csoportban kattintson a Formázás táblázatként elemre, majd válasszon egy stílust.
-
A Formázás táblázatként párbeszédpanelen kattintson az OK gombra.
-
Másolja ki az összes sort, beleértve a fejlécet is.
-
A Power Pivot Kezdőlap lapján kattintson a Beillesztés gombra.
-
A Minta beillesztése > Táblanév mezőbe írjon be egy nevet, például Dátum vagy Naptár. Hagyja bejelölve az Első sor használata oszlopfejléckéntjelölőnégyzetet, majd kattintson az OK gombra.
A Power Pivot új dátumtáblája (ebben a példában Naptár néven) a következőképpen néz ki:
Megjegyzés: Csatolt táblát is létrehozhat a Hozzáadás az adatmodellhez paranccsal. Ez azonban szükségtelenül nagy méretűvé teszi a munkafüzetet, mert a munkafüzet a dátumtábla két verziójával rendelkezik; egy az Excelben és egy a Power Pivotban.
Megjegyzés: A névdátum egy kulcsszó a Power Pivotban. Ha elnevezi a Power Pivot Dateben létrehozott táblát, akkor a tábla nevét minden olyan DAX-képletbe be kell írnia, amely egy argumentumban hivatkozik rá. A cikkben szereplő összes példakép és képlet a Power Pivot Naptár nevű dátumtáblázatára hivatkozik.
Most már van egy dátumtábla az adatmodellben. A DAX használatával új dátumoszlopokat adhat hozzá, például Év, Hónap stb.
Új dátumoszlopok hozzáadása a dátumtáblához
A dátumtartomány összes dátumának meghatározásához fontos, hogy a dátumtáblázat egyetlen dátumoszloppal rendelkezik, amely minden évhez egy-egy sort tartalmaz. A ténytábla és a dátumtábla közötti kapcsolat létrehozásához is szükséges. Az egyetlen dátumoszlop, amely minden nap egy sort tartalmaz, nem hasznos, ha dátumok alapján elemez egy kimutatásban vagy Power View-jelentésben. Azt szeretné, hogy a dátumtábla olyan oszlopokat tartalmazzon, amelyek segítenek az adatok egy tartomány vagy dátumcsoport összesítésében. Előfordulhat például, hogy az értékesítési összegeket hónap vagy negyedév szerint szeretné összegzni, vagy létrehozhat egy mértéket, amely az évenkénti növekedést számítja ki. Ezekben az esetekben a dátumtáblának év, hónap vagy negyedév oszlopra van szüksége, amelyek lehetővé teszik az adott időszakra vonatkozó adatok összesítését.
Ha relációs adatforrásból importálta a dátumtáblát, az már tartalmazhat különböző típusú dátumoszlopokat. Bizonyos esetekben érdemes lehet módosítani néhány oszlopot, vagy további dátumoszlopokat létrehozni. Ez különösen akkor igaz, ha saját dátumtáblázatot hoz létre az Excelben, és az adatmodellbe másolja. Szerencsére a DAX dátum- és időfüggvényeivel egyszerűen hozhat létre új dátumoszlopokat a Power Pivotban.
Tipp: Ha még nem használta a DAX-ot, a gyorsútmutatóval nagyszerűen kezdheti el a tanulást : A DAX alapjai 30 perc alatt elsajátítása Office.com.
DAX dátum- és időfüggvények
Ha már dolgozott már dátum- és időfüggvényekkel az Excel-képletekben, akkor valószínűleg már ismeri a Dátum és idő függvényt. Bár ezek a függvények hasonlóak az Excel megfelelőihez, van néhány fontos különbség:
-
A DAX dátum- és időfüggvények datetime adattípust használnak.
-
Egy oszlop értékeit argumentumként vehetik fel.
-
Dátumértékek visszaadására és/vagy módosítására használhatók.
Ezeket a függvényeket gyakran használják egyéni dátumoszlopok dátumtáblákban való létrehozásához, ezért fontos megérteni őket. Ezen függvények közül számosat használunk az Év, a Negyedév, a FiscalMonth stb. oszlopainak létrehozásához.
Megjegyzés: A DAX dátum- és időfüggvényei nem azonosak az Időintelligencia függvényeivel. További információ az Időintelligencia szolgáltatásról az Excel Power Pivotban.
A DAX a következő dátum- és időfüggvényeket tartalmazza:
A képletekben számos más DAX-függvény is használható. Az itt ismertetett képletek közül sok például matematikai és trigonometriai függvényeket használ, például MOD és TRUNC függvényeket, logikai függvényeket ( például HA) és szövegfüggvényeket (például FORMAT ). További információt a jelen cikk További források című szakaszában talál.
Képlet példák egy naptári évre
Az alábbi példák a Naptár nevű dátumtábla további oszlopainak létrehozásához használt képleteket ismertetik. Egy Dátum nevű oszlop már létezik, és 2010. 01. 01. és 2016. 12. 31. között folytonos dátumtartományt tartalmaz.
Év
=ÉV([dátum])
Ebben a képletben a YEAR függvény a Dátum oszlopban lévő értékből adja vissza az évet. Mivel a Date oszlopban szereplő érték datetime adattípusú, az ÉV függvény tudja, hogyan kell visszaadni belőle az évet.
Hónap
=HÓNAP([dátum])
Ebben a képletben a YEAR függvényhez hasonlóan a HÓNAP függvénnyel egyszerűen visszaadhatunk egy hónapértéket a Date oszlopból.
Negyedév
=INT(([Hónap]+2)/3)
Ebben a képletben az INT függvénnyel egy dátumértéket adunk vissza egész számként. Az INT függvényhez megadott argumentum a Hónap oszlop értéke, 2 hozzáadása, majd a 3-mal való osztás a negyedév 1-től 4-ig történő lekéréséhez.
Hónap neve
=FORMÁTUM([dátum];"mmmm")
Ebben a képletben a hónap nevének lekéréséhez a FORMAT függvénnyel alakítunk át egy numerikus értéket a Date oszlopból szöveggé. Első argumentumként a Date oszlopot, majd a formátumot adjuk meg; azt szeretnénk, hogy a hónap neve minden karaktert megjelenítsen, ezért az "mmmm" karaktert használjuk. Az eredmény a következőképpen néz ki:
Ha a hónap nevét három betűre rövidítve szeretnénk visszaadni, akkor a formátum argumentumban az "mmm" értéket használjuk.
A hét napja
=FORMAT([dátum];"ddd")
Ebben a képletben a FORMAT függvénnyel lekérjük a nap nevét. Mivel csak egy rövidített napnevet szeretnénk, a formátum argumentumban a "ddd" értéket adjuk meg.
Kimutatásminta
Ha már vannak olyan dátummezői, mint az Év, a Negyedév, a Hónap stb., használhatja őket egy kimutatásban vagy kimutatásban. Az alábbi képen például a SALES ténytáblából származó SalesAmount mező látható a VALUES táblában, az Év és a Negyedév pedig a Calendar dimenziótáblából a SOROKban. A SalesAmount az év és a negyedév kontextusában van összesítve.
Példák képletekre egy pénzügyi évre
Költségvetési év
=HA([Hónap]<= 6,[Év],[Év]+1)
Ebben a példában a pénzügyi év július 1-jén kezdődik.
Nincs olyan függvény, amely kinyerhet egy pénzügyi évet egy dátumértékből, mert a pénzügyi év kezdő és záró dátumai gyakran eltérnek a naptári év kezdő és záró dátumaitól. A pénzügyi év lekéréséhez először egy HA függvénnyel teszteljük, hogy a Month értéke kisebb-e, mint 6. Ha a második argumentumban a Month értéke kisebb vagy egyenlő 6-nál, akkor az Év oszlop értékét adja vissza. Ha nem, akkor adja vissza a Year (Év) értéket, és adja hozzá az 1 értéket.
A pénzügyi év záró hónapjának értékének megadásának másik módja egy olyan mérték létrehozása, amely egyszerűen meghatározza a hónapot. Például: FYE:=6. Ezután hivatkozhat a mérték nevére a hónap száma helyett. Például: =HA([Hónap]<=[Év],[Év],[Év]+1). Ez nagyobb rugalmasságot biztosít, ha több különböző képletben hivatkozik a pénzügyi év záró hónapjára.
Pénzügyi hónap
=HA([Hónap]<= 6, 6+[Hónap], [Hónap]- 6)
Ebben a képletben azt adjuk meg, hogy a [Hónap] értéke kisebb vagy egyenlő-e 6-nál, akkor 6-ot veszünk fel, és hozzáadjuk a Month értéket, ellenkező esetben kivonunk 6-ot a [Month] értékből.
Pénzügyi negyedév
=INT(([FiscalMonth]+2)/3)
A FiscalQuarter képlete nagyjából megegyezik a naptári év negyedéves képletével. Az egyetlen különbség, hogy a [Month] helyett a [FiscalMonth] értéket adjuk meg.
Ünnepek vagy különleges dátumok
Előfordulhat, hogy olyan dátumoszlopot szeretne felvenni, amely azt jelzi, hogy bizonyos dátumok ünnepnapok vagy más különleges dátumok. Például összeadhatja az újévi napok értékesítési összegét úgy, hogy hozzáad egy Ünnep mezőt egy kimutatáshoz szeletelőként vagy szűrőként. Más esetekben érdemes lehet kizárni ezeket a dátumokat más dátumoszlopokból vagy egy mértékből.
Az ünnepnapokat és a különleges napokat is beleértve elég egyszerű. Az Excelben létrehozhat egy táblázatot, amely tartalmazza a belefoglalni kívánt dátumokat. Ezután másolhatja vagy használhatja a Hozzáadás az adatmodellhez lehetőséget, hogy csatolt táblaként adja hozzá az adatmodellhez. A legtöbb esetben nem szükséges kapcsolatot létrehozni a tábla és a Naptár tábla között. Az arra hivatkozó képletek a LOOKUPVALUE függvénnyel adhatnak vissza értékeket.
Az alábbiakban egy excelben létrehozott táblázat látható, amely a dátumtáblához hozzáadandó ünnepnapokat tartalmazza:
|
Dátum |
Ünnepnap |
|---|---|
|
1/1/2010 |
Új évek |
|
11/25/2010 |
Hálaadás |
|
12/25/2010 |
Karácsony |
|
2011.01.01. |
Új évek |
|
11/24/2011 |
Hálaadás |
|
12/25/2011 |
Karácsony |
|
2012.01.01 |
Új évek |
|
2012.11.22. |
Hálaadás |
|
12/25/2012 |
Karácsony |
|
1/1/2013 |
Új évek |
|
11/28/2013 |
Hálaadás |
|
12/25/2013 |
Karácsony |
|
11/27/2014 |
Hálaadás |
|
12/25/2014 |
Karácsony |
|
2014.01.01. |
Új évek |
|
11/27/2014 |
Hálaadás |
|
12/25/2014 |
Karácsony |
|
1/1/2015 |
Új évek |
|
11/26/2014 |
Hálaadás |
|
12/25/2015 |
Karácsony |
|
2016.01.01. |
Új évek |
|
11/24/2016 |
Hálaadás |
|
12/25/2016 |
Karácsony |
A dátumtáblában létrehozunk egy Holiday nevű oszlopot, és az alábbi képletet használjuk:
=KERES.ÉRTÉK(Ünnepek[Ünnepnap];Ünnepek[dátum];Naptár[dátum])
Vizsgáljuk meg alaposabban ezt a képletet.
A LOOKUPVALUE függvénnyel az Ünnepnapok tábla Ünnepnap oszlopában található értékek lekérésére használjuk. Az első argumentumban azt az oszlopot adjuk meg, ahol az eredmény értéke lesz. Az Ünnepnapok táblában az Ünnepnap oszlopot adhatja meg, mert ezt az értéket szeretnénk visszaadni.
=KERES.ÉRTÉK(Ünnepek[Ünnepnap];Ünnepek[dátum];Naptár[dátum])
Ezután megadjuk a második argumentumot, a keresési oszlopot, amely tartalmazza a keresett dátumokat. Az Ünnepnapok táblában a Dátum oszlopot a következőképpen adhatja meg:
=KERES.ÉRTÉK(Ünnepek[Ünnepnap];Ünnepek[dátum];Naptár[dátum])
Végül megadjuk a Naptár táblában azt az oszlopot, amely azokat a dátumokat tartalmazza, amelyekre keresni szeretnénk az Ünnep táblában . Ez természetesen a Naptár tábla Dátum oszlopa.
=KERES.ÉRTÉK(Ünnepek[Ünnepnap];Ünnepek[dátum];Naptár[dátum])
Az Ünnep oszlop minden olyan sorhoz visszaadja az ünnepnap nevét, amelynek dátumértéke megegyezik az Ünnepnapok táblában szereplő dátummal.
Egyéni naptár – tizenhárom négyhetes időszak
Egyes szervezetek, például a kiskereskedelem vagy az élelmiszer-szolgáltatás, gyakran különböző időszakokról, például tizenhárom négyhetes időszakról számolnak be. Tizenhárom négyhetes időszaknaptár esetén minden időszak 28 nap; ezért minden időszak négy hétfőt, négy keddet, négy szerdai és így tovább. Minden időszak ugyanannyi napot tartalmaz, és az ünnepnapok általában minden évben ugyanabban az időszakban lesznek. Dönthet úgy, hogy a hét bármely napján elindít egy időszakot. A naptárban vagy pénzügyi évben lévő dátumokhoz hasonlóan a DAX-ban is létrehozhat további oszlopokat egyéni dátumokkal.
Az alábbi példákban az első teljes időszak a pénzügyi év első vasárnapján kezdődik. Ebben az esetben a pénzügyi év 7/1-én kezdődik.
Hét
Ez az érték a hét számát adja meg a pénzügyi év első teljes hetével kezdődően. Ebben a példában az első teljes hét vasárnap kezdődik, így a Naptár tábla első pénzügyi évének első teljes hete ténylegesen 2010.07.04-én kezdődik, és a Naptár tábla utolsó teljes hetén folytatódik. Bár maga az érték nem olyan hasznos az elemzésben, ki kell számítani más 28 napos időszak képleteiben való használatra.
=INT([dátum]-40356)/7)
Vizsgáljuk meg alaposabban ezt a képletet.
Először létrehozunk egy képletet, amely a Date oszlop értékeit egész számként adja vissza, az alábbihoz hasonlóan:
=INT([dátum])
Ezután az első pénzügyi év első vasárnapjára szeretnénk keresni. Látjuk, hogy ez 2010.07.04.
Most vonja ki a 40356-ot (amely a 2010. 06. 27., az előző pénzügyi év utolsó vasárnapjának egésze) ebből az értékből, hogy lekérje a Napok kezdete óta eltelt napok számát a Naptár táblában a következőképpen:
=INT([dátum]-40356)
Ezután ossza el az eredményt 7-el (egy hét napjaival), az alábbihoz hasonlóan:
=INT(([dátum]-40356)/7)
Az eredmény így néz ki:
Period
Az egyéni naptárban szereplő időszak 28 napot tartalmaz, és mindig vasárnap kezdődik. Ez az oszlop az első pénzügyi év első vasárnapjával kezdődő időszak számát adja vissza.
=INT(([Hét]+3)/4)
Vizsgáljuk meg alaposabban ezt a képletet.
Először létrehozunk egy képletet, amely a Hét oszlopban szereplő értéket egész számként adja vissza, az alábbihoz hasonlóan:
=INT([Hét])
Ezután adja hozzá a 3 értéket ehhez az értékhez, a következőképpen:
=INT([Hét]+3)
Ezután ossza el az eredményt 4-el, az alábbihoz hasonlóan:
=INT(([Hét]+3)/4)
Az eredmény így néz ki:
Időszak pénzügyi éve
Ez az érték egy időszak pénzügyi évét adja vissza.
=INT(([Időszak]+12)/13)+2008
Vizsgáljuk meg alaposabban ezt a képletet.
Először létrehozunk egy képletet, amely egy értéket ad vissza a Period függvényből, és hozzáadunk 12-t:
= ([Időszak]+12)
Az eredményt 13-tal osztjuk el, mert a pénzügyi évben tizenhárom nap van:
=(([Időszak]+12)/13)
2010-et adunk hozzá, mert ez az első év a táblában:
=(([Időszak]+12)/13)+2010
Végül az INT függvénnyel eltávolítjuk az eredmény töredékét, és egy egész számot adunk vissza, ha 13-mal osztjuk el, az alábbi módon:
=INT(([Időszak]+12)/13)+2010
Az eredmény így néz ki:
Pénzügyi év időszaka
Ez az érték az 1–13. időszakszámot adja vissza az egyes pénzügyi évek első teljes időszakával kezdve (vasárnaptól kezdve).
=HA(MOD([Időszak];13), MOD([Időszak];13);13)
Ez a képlet egy kicsit összetettebb, ezért először egy olyan nyelven fogjuk leírni, amelyet jobban megértünk. Ez a képlet azt állítja, hogy az [Időszak] értékét 13-tal osztva kap egy időszakszámot (1-13) az év során. Ha ez a szám 0, akkor 13-at ad vissza.
Először létrehozunk egy képletet, amely az időszak és 13 közötti érték fennmaradó részét adja vissza. A MOD (matematikai és trigonometriai függvények) a következőképpen használhatók:
=MOD([Időszak];13)
Ez a legtöbb esetben a kívánt eredményt adja, kivéve, ha a Period értéke 0, mivel ezek a dátumok nem az első pénzügyi évbe esnek, mint a példaNaptár dátumtáblázatának első öt napjában. Ezt egy HA függvénnyel tudjuk elintézni. Ha az eredmény 0, 13-at adunk vissza, a következőképpen:
=HA(MOD([Időszak];13);MOD([Időszak];13);13)
Az eredmény így néz ki:
Kimutatásminta
Az alábbi képen egy kimutatás látható, amely a SALES ténytáblából származó SalesAmount mezőt tartalmazza a VALUES táblában, valamint a PeriodFiscalYear és PeriodInFiscalYear mezőket a Calendar date dimension táblából a SOROKban. A SalesAmount a pénzügyi év és a pénzügyi év 28 napos időszaka szerint van összesítve a kontextusban.
Kapcsolatok
Miután létrehozott egy dátumtáblát az adatmodellben, az adatok kimutatásokban és jelentésekben való böngészéséhez, valamint a dátumdimenzió-tábla oszlopai alapján történő összesítéséhez kapcsolatot kell létrehoznia a ténytábla és a tranzakciós adatok és a dátumtábla között.
Mivel dátumok alapján kell kapcsolatot létrehoznia, mindenképpen létre kell hoznia ezt a kapcsolatot az olyan oszlopok között, amelyeknek az értékei dátum/idő (dátum) adattípusúak.
A ténytábla minden dátumértékéhez a dátumtábla kapcsolódó keresési oszlopának egyező értékeket kell tartalmaznia. Például a Sales ténytábla egy sorának (tranzakciórekordjának) a DateKey oszlopban 8/15/2012 12:00 AM értékkel kell rendelkeznie a date (calendar) tábla kapcsolódó Dátum oszlopában. Ez az egyik legfontosabb oka annak, hogy a dátumtábla dátumoszlopa olyan folytonos dátumtartományt tartalmazzon, amely a ténytáblában bármilyen lehetséges dátumot tartalmaz.
Megjegyzés: Bár az egyes táblák dátumoszlopának azonos adattípusúnak (Dátum) kell lennie, az egyes oszlopok formátuma nem számít.
Megjegyzés: Ha a Power Pivot nem teszi lehetővé a két tábla közötti kapcsolatok létrehozását, előfordulhat, hogy a dátummezők nem azonos pontossággal tárolják a dátumot és az időt. Az oszlopok formázásától függően előfordulhat, hogy az értékek ugyanúgy néznek ki, de másképpen lesznek tárolva. További információ az idő használatával kapcsolatban.
Megjegyzés: Ne használjon egész szám helyettesítő kulcsokat a kapcsolatokban. Amikor relációs adatforrásból importál adatokat, a dátum- és időoszlopokat gyakran helyettes kulcs jelöli, amely egy egyedi dátumot ábrázoló egész szám. A Power Pivotban ne hozzon létre kapcsolatokat egész szám típusú dátum-/időkulcsokkal, és ehelyett olyan oszlopokat használjon, amelyek egyedi értékeket tartalmaznak dátum adattípussal. Bár a helyettesítő kulcsok használata a hagyományos adattárházakban ajánlott eljárásnak számít, a Power Pivotban nincs szükség egész kulcsokra, és megnehezítheti az értékek kimutatásokban való csoportosítását különböző dátum-időszakok szerint.
Ha típuseltérési hibát kap egy kapcsolat létrehozásakor, annak valószínűleg az az oka, hogy a ténytábla oszlopa nem Date adattípusú. Ez akkor fordulhat elő, ha a Power Pivot nem tudja automatikusan dátum típusúvá alakítani a nem dátum típusú adatokat (általában szöveges adattípusokat). A ténytáblában továbbra is használhatja az oszlopot, de az adatokat dax-képlettel kell konvertálnia egy új számított oszlopban. Lásd: A szöveges adattípus dátumainak átalakítása dátum adattípussá a függelék későbbi részében.
Több kapcsolat
Bizonyos esetekben szükség lehet több kapcsolat létrehozására vagy több dátumtábla létrehozására. Ha például az Értékesítés ténytáblában több dátummező is szerepel, például DateKey, ShipDate és ReturnDate, akkor mind rendelkezhetnek kapcsolattal a Naptár dátumtábla Dátum mezőjével, de ezek közül csak az egyik lehet aktív kapcsolat. Ebben az esetben, mivel a DateKey a tranzakció dátumát jelöli, ezért ez a legfontosabb dátum, ez a legjobb, ha aktív kapcsolatként szolgál. A többinek inaktív kapcsolatai vannak.
Az alábbi kimutatás a teljes értékesítést pénzügyi év és pénzügyi negyedév szerint számítja ki. A Total Sales (Összes értékesítés) nevű mérték a Total Sales:=SUM([SalesAmount])) képlettel az ÉRTÉKEK, a Calendar date tábla FiscalYear és FiscalQuarter mezői pedig SOROK értékre kerülnek.
Ez az egyenes előremutató kimutatás megfelelően működik, mert a teljes értékesítést a DateKey tranzakciósdátumával szeretnénk összegzni. A Total Sales mérték a DateKey dátumait használja, és a pénzügyi év és a pénzügyi negyedév szerint van összegezve, mivel a Sales tábla DateKey és a Calendar date tábla Date oszlopa között van kapcsolat.
Inaktív kapcsolatok
De mi a teendő, ha a teljes értékesítést nem tranzakciós dátum, hanem szállítási dátum szerint szeretnénk összegezni? Kapcsolatra van szükségünk a Sales tábla ShipDate oszlopa és a Naptár tábla Dátum oszlopa között. Ha nem hozjuk létre ezt a kapcsolatot, az összesítések mindig a tranzakció dátumán alapulnak. Több kapcsolat is lehet azonban, annak ellenére, hogy csak egy lehet aktív, és mivel a tranzakció dátuma a legfontosabb, aktív kapcsolatot kap a Naptár táblával.
Ebben az esetben a ShipDate inaktív kapcsolattal rendelkezik, ezért a szállítási dátumok alapján az adatok összesítésére létrehozott mértékképletnek meg kell adnia az inaktív kapcsolatot a USERELATIONSHIP függvénnyel.
Mivel például a Sales tábla ShipDate oszlopa és a Naptár tábla Dátum oszlopa között inaktív kapcsolat áll fenn, létrehozhatunk egy mértéket, amely a teljes értékesítést szállítási dátum szerint összegzi. Az alábbihoz hasonló képletet használunk a használandó kapcsolat megadásához:
Összes értékesítés szállítási dátum szerint:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))
Ez a képlet egyszerűen kimondja: A SalesAmount összegének kiszámítása, de szűrés a Sales tábla ShipDate oszlopa és a Naptár tábla Dátum oszlopa közötti kapcsolat alapján.
Most, ha létrehozunk egy kimutatást, és a Total Sales by Ship Date (Összes értékesítés szállítási dátum szerint) mértéket értékekben, a Fiscal Year (Pénzügyi év) és a Fiscal Quarter (Pénzügyi negyedév) oszlopban sorokban helyezzük el, akkor ugyanazt a végösszeget látjuk, de a pénzügyi év és a pénzügyi negyedév összes többi összege eltérő, mert a szállítási dátumon és nem a tranzakció dátumán alapulnak.
Az inaktív kapcsolatok használata lehetővé teszi, hogy csak egy dátumtáblát használjon, de ehhez minden mértéknek (például a Teljes értékesítés szállítási dátum szerint) hivatkoznia kell az inaktív kapcsolatra a képletében. Van egy másik alternatíva is, azaz több dátumtábla használata.
Több dátumtábla
A ténytábla több dátumoszlopával is dolgozhat, ha több dátumtáblát hoz létre, és külön aktív kapcsolatokat hoz létre közöttük. Tekintsük meg újra a Sales tábla példáját. Három olyan oszlopunk van, amelyekben dátumokat szeretnénk összesíteni:
-
Egy DateKey az egyes tranzakciók értékesítésének dátumával.
-
ShipDate (SzállításiDátum) – az eladott cikkek vevőnek történő kiszállításának dátuma és időpontja.
-
ReturnDate (VisszatérésiDátum) – egy vagy több visszaadott elem beérkezésének dátumával és időpontjával.
Ne feledje, hogy a tranzakció dátumát tartalmazó DateKey mező a legfontosabb. A legtöbb aggregációt ezen dátumok alapján fogjuk elvégezni, ezért minden bizonnyal szeretnénk kapcsolatot létesíteni közöttük és a Naptár tábla Dátum oszlopa között. Ha nem szeretnénk inaktív kapcsolatokat létrehozni a ShipDate és a ReturnDate mező és a Naptár tábla Dátum mezője között, ami speciális mértékképleteket igényel, további dátumtáblákat hozhatunk létre a szállítási dátumhoz és a visszaküldés dátumához. Ezután aktív kapcsolatokat hozhatunk létre közöttük.
Ebben a példában egy másik dátumtáblát hoztunk létre ShipCalendar néven. Ez természetesen további dátumoszlopok létrehozását is jelenti, és mivel ezek a dátumoszlopok egy másik dátumtáblában találhatók, olyan nevet szeretnénk nekik adni, amely megkülönbözteti őket a Naptár tábla ugyanazon oszlopaitól. Létrehoztunk például ShipYear, ShipMonth, ShipQuarter stb. nevű oszlopokat.
Ha létrehozzuk a kimutatást, és a Total Sales mértéket ÉRTÉKEK, a ShipFiscalYear és a ShipFiscalQuarter értéket pedig SOROK értékre állítjuk, ugyanazokat az eredményeket látjuk, mint amikor létrehoztunk egy inaktív kapcsolatot és egy speciális Total Sales by Ship Date számított mezőt.
Ezen megközelítések mindegyike gondos megfontolást igényel. Ha több kapcsolatot használ egyetlen dátumtáblázattal, előfordulhat, hogy a USERELATIONSHIP függvénnyel olyan speciális mértékeket kell létrehoznia, amelyek az inaktív kapcsolatokat átvitik. A több dátumtáblázat létrehozása viszont zavaró lehet egy mezőlistában, és mivel az adatmodellben több tábla található, több memóriára lesz szükség. Kísérletezzen az Ön számára legmegfelelőbb megoldással.
Date Table tulajdonság
A Date Table tulajdonság beállítja Time-Intelligence függvények (például TOTALYTD, PREVIOUSMONTH és DATESBETWEEN) megfelelő működéséhez szükséges metaadatokat. Ha ezen függvények valamelyikével végez számítást, a Power Pivot képletmotorja tudja, hogy hová kell mennie a szükséges dátumok lekéréséhez.
Figyelmeztetés: Ha ez a tulajdonság nincs beállítva, előfordulhat, hogy a DAX-Time-Intelligence függvényeket használó mértékek nem adnak vissza helyes eredményeket.
A Date Table tulajdonság beállításakor meg kell adnia egy dátumtáblát és egy dátumoszlopot a Date (datetime) adattípushoz.
Útmutató: A Date Table tulajdonság beállítása
-
A PowerPivot ablakban válassza a Naptár táblát.
-
A Tervezés lapon kattintson a Megjelölés dátumtáblázatként elemre.
-
A Megjelölés dátumtáblázatként párbeszédpanelen válasszon ki egy egyedi értékeket és Dátum adattípust tartalmazó oszlopot.
Idő használata
Az Excelben vagy az SQL Serveren Dátum adattípusú dátumértékek valójában egy szám. Ebben a számban az adott időpontra hivatkozó számjegyek szerepelnek. Sok esetben minden sorban éjfél van. Ha például egy Sales ténytábla DateTimeKey mezőjének értéke 2010.10.19. 12:00:00, az azt jelenti, hogy az értékek napi pontosságúak. Ha a DateTimeKey mező értéke tartalmaz egy időpontot, például 2010. 10. 19. 08:44:00, az azt jelenti, hogy az értékek a perc pontossági szintre esnek. Az értékek az óra vagy akár másodperc pontosságú pontosságot is jelenthetik. Az időérték pontosságának szintje jelentős hatással lesz a dátumtábla létrehozására, valamint a dátumtábla és a ténytábla közötti kapcsolatokra.
Meg kell határoznia, hogy az adatokat napi pontossággal vagy időszintre összesíti-e. Más szóval érdemes lehet a dátumtáblázatban olyan oszlopokat használni, mint a Reggel, a Délután vagy az Óra dátummező a kimutatás Sor, Oszlop vagy Szűrő területén.
Megjegyzés: A dax időintelligencia-függvények által használható legkisebb időegység a napok száma. Ha nem kell időértékekkel dolgoznia, csökkentenie kell az adatok pontosságát, hogy a napokat használja minimális egységként.
Ha az adatokat az időszintre szeretné összesíteni, akkor a dátumtáblának szüksége lesz egy dátumoszlopra, amely tartalmazza az idő értékét. Valójában szüksége lesz egy olyan dátumoszlopra, amely egy sort tartalmaz a nap minden órájára vagy akár percenként, a dátumtartomány minden évére vonatkozóan. Ennek az az oka, hogy a ténytábla DateTimeKey oszlopa és a dátumtábla dátumoszlopa közötti kapcsolat létrehozásához egyező értékekkel kell rendelkeznie. Ahogy el tudod képzelni, ha sok évet belefoglalsz, ez egy nagyon nagy dátumtáblázatot hozhat létre.
A legtöbb esetben azonban csak a napra szeretné összesíteni az adatokat. Más szóval olyan oszlopokat fog használni, mint az Év, a Hónap, a Hét vagy a Hét napja a kimutatás Sor, Oszlop vagy Szűrő területének mezőiként. Ebben az esetben a dátumtábla dátumoszlopának csak egy sort kell tartalmaznia egy év minden napjára, ahogy azt korábban ismertettük.
Ha a dátumoszlop pontossági szinttel rendelkezik, de csak egy napra lesz összesítve, akkor a ténytábla és a dátumtábla közötti kapcsolat létrehozásához módosítania kell a ténytáblát úgy, hogy létrehoz egy új oszlopot, amely a dátumoszlop értékeit egy nap értékre csonkítja. Más szóval konvertáljon egy értéket 2010.10.19.08:44:0010/19/2010 12:00:00-ra. Ezután létrehozhatja a kapcsolatot az új oszlop és a dátumtábla dátumoszlopa között, mert az értékek megegyeznek.
Lássunk egy példát. A képen egy DateTimeKey oszlop látható a Sales ténytáblában. A táblázatban szereplő adatok összesítésének csak a nap szintjére kell esnie, a Calendar date tábla oszlopainak (például Év, Hónap, Negyedév stb.) használatával. Az értékben foglalt idő nem releváns, csak a tényleges dátum.
Mivel ezeket az adatokat nem kell időszinten elemeznünk, nincs szükség a Naptár dátumtáblázatÁnak Dátum oszlopára, hogy minden évben minden órában és minden nap minden percében szerepeljen egy sor. A dátumtábla Date oszlopa így néz ki:
Ha kapcsolatot szeretne létrehozni a Sales tábla DateTimeKey oszlopa és a Naptár tábla Dátum oszlopa között, létrehozhatunk egy új számított oszlopot a Sales ténytáblában, és a TRUNC függvénnyel a DateTimeKey oszlop dátum- és időértékét egy olyan dátumértékké alakíthatjuk, amely megfelel a Naptár tábla Dátum oszlopában szereplő értékeknek. A képlet a következőképpen néz ki:
=TRUNC([DateTimeKey];0)
Ez egy új (DateKey nevű) oszlopot ad nekünk a DateTimeKey oszlop dátumával, és minden sorhoz 12:00:00 időpontot:
Most létrehozhatunk egy kapcsolatot az új (DateKey) oszlop és a Naptár tábla Dátum oszlopa között.
Hasonlóképpen létrehozhatunk egy számított oszlopot a Sales táblában, amely a DateTimeKey oszlop idő pontosságát az óra pontosságára csökkenti. Ebben az esetben a TRUNC függvény nem fog működni, de továbbra is használhatunk más DAX dátum- és időfüggvényeket egy új érték kinyerésére és újraegyesítésére óránkénti pontossággal. Az alábbihoz hasonló képletet használhatunk:
= DATE (YEAR([DateTimeKey]), MONTH([DateTimeKey]), DAY([DateTimeKey]) ) ) + TIME (HOUR([DateTimeKey]), 0, 0)
Az új oszlop a következőképpen néz ki:
Feltéve, hogy a dátumtábla Date oszlopa óránkénti pontosságú értékekkel rendelkezik, akkor kapcsolatot hozhatunk létre közöttük.
Dátumok használhatóbbá tétele
A dátumtáblában létrehozott dátumoszlopok nagy része szükséges más mezőkhöz, de az elemzésben nem minden hasznos. A cikk során hivatkozott és megjelenített Sales tábla DateKey mezője például azért fontos, mert minden tranzakció esetében az adott tranzakció egy adott dátumon és időpontban történik. Elemzési és jelentéskészítési szempontból azonban nem minden hasznos, mert nem használhatjuk sorként, oszlopként vagy szűrőmezőként egy kimutatásban vagy jelentésben.
Hasonlóképpen, a példánkban a Naptár tábla Dátum oszlopa nagyon hasznos, valójában kritikus, de nem használhatja dimenzióként a kimutatásokban.
Ahhoz, hogy a táblák és a bennük lévő oszlopok a lehető legláncsosabbak maradjanak, és hogy a kimutatások vagy a Power View-jelentések mezőlistái könnyebben navigálhatók legyenek, fontos elrejteni a szükségtelen oszlopokat az ügyféleszközök elől. Előfordulhat, hogy bizonyos táblákat is el szeretne rejteni. A korábban bemutatott Ünnepnapok tábla olyan ünnepnapokat tartalmaz, amelyek fontosak a Naptár tábla bizonyos oszlopaihoz, de maguk az Ünnepnapok tábla Dátum és Ünnep oszlopa nem használható kimutatás mezőiként. A mezőlisták könnyebb navigálásához itt is elrejtheti a teljes Ünnepnapok táblát.
A dátumok használatának egy másik fontos szempontja az elnevezési konvenciók. A Power Pivotban tetszőleges nevet adhat a tábláknak és az oszlopoknak. Ne feledje azonban, hogy különösen akkor, ha a munkafüzetet más felhasználókkal is megosztja, a jó elnevezési konvenció megkönnyíti a táblák és dátumok azonosítását, nemcsak a mezőlistákban, hanem a Power Pivotban és a DAX-képletekben is.
Miután létrehozott egy dátumtáblát az adatmodellben, megkezdheti a mértékek létrehozását, amelyek segítenek a legtöbbet kihozni az adatokból. Néhány lehet olyan egyszerű, mint az aktuális évre vonatkozó értékesítési összegek összegzése, mások pedig összetettebbek lehetnek, ahol az egyedi dátumok adott tartományára kell szűrni. További információ: Mértékek a Power Pivotban és az Időintelligencia-függvényekben.
Függelék
Szöveges adattípus dátummá alakítása dátum adattípussá
Bizonyos esetekben a tranzakciós adatokat tartalmazó ténytáblák szöveges adattípusú dátumokat tartalmazhatnak. Vagyis a 2012-12-04T11:47:09 formátumban megjelenő dátum valójában egyáltalán nem dátum, vagy legalábbis a Power Pivot által értelmezhető dátum típusa. Ez tényleg csak egy szöveg, ami úgy olvas, mint egy dátum. A ténytábla dátumoszlopa és egy dátumtábla dátumoszlopa közötti kapcsolat létrehozásához mindkét oszlopnak Date adattípusúnak kell lennie.
Amikor szöveges adattípusú dátumoszlop adattípusát dátum adattípusra próbálja módosítani, a Power Pivot képes automatikusan értelmezni a dátumokat, és átalakítani valódi dátum adattípussá. Ha a Power Pivot nem tud adattípus-konverziót végezni, típuseltérési hibát fog kapni.
A dátumokat azonban továbbra is átalakíthatja valódi dátum adattípussá. Létrehozhat egy új számított oszlopot, és EGY DAX-képlettel elemezheti az évet, hónapot, napot, időt stb. a szöveges sztringekből, majd összefűzheti őket úgy, hogy a Power Pivot valódi dátumként olvassa fel.
Ebben a példában egy Sales (Értékesítések) nevű ténytáblát importáltunk a Power Pivotba. Tartalmaz egy DateTime nevű oszlopot. Az értékek a következőképpen jelennek meg:
Ha a Power Pivot Kezdőlap lapjának Formázás csoportjában az Adattípus elemet vizsgáljuk meg, azt látjuk, hogy szöveg adattípusról van szó.
Nem hozható létre kapcsolat a DateTime oszlop és a dátumtábla Dátum oszlopa között, mert az adattípusok nem egyeznek. Ha az adattípust Dátum értékre próbáljuk módosítani, a típuseltérési hiba jelenik meg:
Ebben az esetben a Power Pivot nem tudta szöveggé alakítani az adattípust. Ezt az oszlopot továbbra is használhatjuk, de ahhoz, hogy valódi dátum adattípusúvá tegyük, létre kell hoznunk egy új oszlopot, amely elemzi a szöveget, és újra létrehozza azt egy olyan értékben, amelyet a Power Pivot dátum típusúvá tehet.
Ne feledje, hogy a cikk korábbi, Az idő használata című szakaszában; Hacsak nem szükséges, hogy az elemzés pontos idő-idő szintű legyen, a ténytáblában lévő dátumokat napi pontosságúra kell konvertálnia. Ezt szem előtt tartva azt szeretnénk, hogy az új oszlop értékei a pontosság napi szintjén legyenek (az idő kivételével). A DateTime oszlop értékeit egyaránt átalakíthatjuk dátum adattípussá, és eltávolíthatjuk a pontosság időszintjét a következő képlettel:
=DÁTUM(LEFT([DateTime];4), MID([DateTime];6,2), MID([DateTime],9,2))
Ez egy új oszlopot ad nekünk (ebben az esetben Date néven). A Power Pivot még dátumként is észleli az értékeket, és automatikusan Dátum értékre állítja az adattípust.
Ha meg szeretnénk őrizni a pontosság időszintét, egyszerűen kiterjesztjük a képletet úgy, hogy tartalmazza az órákat, a perceket és a másodperceket.
=DÁTUM(LEFT([DateTime];4), MID([DateTime];6,2), MID([DateTime],9,2)) +
TIME(MID([DateTime],12,2), MID([DateTime],15,2), MID([DateTime],18,2))
Most, hogy már rendelkezünk Egy Dátum adattípusú Dátum oszloppal, létrehozhatunk egy kapcsolatot a dátum és egy dátumoszlop között.
További erőforrások
QuickStart útmutató: A DAX alapjai 30 percben
