A dátumtáblák a Power Pivotban nélkülözhetetlenek az adatok időben való böngészéséhez és számításához. Ez a cikk alaposan bemutatja a dátumtáblázatokat, és bemutatja, hogy miként hozhatja létre őket a Power Pivotban. Ez a cikk a következőket ismerteti:
-
Miért fontos egy dátumtábla az adatok dátum és idő szerint való böngészése és kiszámítása során?
-
Dátumtábla felvétele az adatmodellbe a Power Pivot használatával.
-
Új dátumoszlopok (például Év, Hónap és Időszak) létrehozása a dátumtáblákban.
-
Kapcsolat létrehozása dátumtáblák és ténytáblák között.
-
Hogyan dolgozhat az időpontokkal?
Ez a cikk a Power Pivot új felhasználóinak készült. Fontos azonban, hogy már jól tudja importálni az adatokat, kapcsolatokat létrehozni, valamint számított oszlopokat és értékeket létrehozni.
Ez a cikk nem ismerteti, hogy miként használhatja a DAX Time-Intelligence mértékképletekben. A DAX időintelligencia-függvényekkel való létrehozására vonatkozó további információkért lásd: Időintelligencia a Power Pivot programban a Excel.
Megjegyzés: A Power Pivotban a "mérték" és a "számított mező" név szinonimát jelent. A név mértékét a cikk teljes ideje alatt használjuk. További információ: Measures in Power Pivot.
Tartalom
A dátumtáblák ismertetése
Szinte minden adatelemzéshez hozzá kell tartozik az adatok dátumok és időpontok szerinti böngészése és összehasonlítása. Előfordulhat például, hogy össze szeretné számolni az elmúlt pénzügyi negyedév értékesítési összegét, majd össze szeretné hasonlítani ezeket a végösszegeket más negyedévekkel, vagy egy számla hó végi záróegyenlegét szeretné kiszámítani. Minden ilyen esetben dátumok használatával csoportosítja és összesíti az adott időszakra vonatkozó értékesítési tranzakciókat vagy egyenlegeket.
Power View jelentés
A dátumtáblák a dátumok és időpontok számos különböző megjelenítését tartalmazják. 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, amelyek mezőkként választhatók ki a mezőlistából kimutatások vagy kimutatások adatainak szeletelése és Power View során.
Power View mezőlista
Ahhoz, hogy a dátumoszlopok (például Év, Hónap és Negyedév) a megfelelő tartományukba tartozó összes dátumot tartalmazzák, a dátumtáblának legalább egy oszloppal kell lennie, amely összefüggő dátumkészletet tartalmaz. Ez azt jelenti, hogy az oszlopban minden nap egy sornak kell lennie a dátumtáblában szereplő minden egyes évhez.
Ha például a tallózáshoz szükséges adatok 2010. február 1. és 2012. november 30. között vannak dátumok, és naptári évre jelentést készít, akkor szeretne egy dátumtáblát, amely legalább 2010. január 1. és 2012. december 31. között van dátumtartományban. A dátumtáblában minden évben az egyes napoknak tartalmaznia kell. Ha rendszeresen frissíteni szeretné az adatokat újabb adatokkal, érdemes lehet egy vagy két évvel elévülni a befejezési dátumot, így nem kell frissítenie a dátumtáblát az idő múlása során.
Date table with a contiguous set of dates
Ha egy pénzügyi évre jelentést készít, létrehozhat egy dátumtáblát, amely az egyes pénzügyi évekre vonatkozó dátumok összefüggő halmazá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. januárban), létrehozhat egy dátumtáblát, amely 2009.03.01-től kezdődik, és a 2013.03.01.03.-án kezdődik, és az egyes pénzügyi évek legalább minden napját tartalmazza a 2013-as pénzügyi év utolsó dátumig.
Ha a naptári és a pénzügyi évre is jelentést készít, nem kell külön dátumtáblákat létrehoznia. Az egyetlen dátumtáblában a naptári év, a pénzügyi év és akár tizenhárom négyhetes időszak naptára is tartalmazhat oszlopokat. A fontos az, hogy a dátumtáblában összefüggő dátumok szerepelnek az összes évhez.
Dátumtábla hozzáadása az adatmodellhez
Dátumtáblát többféleképpen is hozzáadhat az adatmodellhez:
-
Importálás relációs adatbázisból vagy más adatforrásból
-
Hozzon létre egy dátumtáblát a Excel majd másolja vagy csatolja egy új táblához a Power Pivotban.
-
Importálás a Microsoft Azure Piactérről.
Ezeket közelebbről is át kell nézni.
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, jó eséllyel már van egy dátumtábla és a közötte és a többi importált adat között. A dátumok és a formátum valószínűleg meg fog egyezni az tényadatokban megadott dátumokkal, és a dátumok a múltban kezdődnek, és a jövőbe nyúlnak. Az importálni kívánt dátumtábla lehet, hogy nagyon nagy, és az adatmodellben szerepelni kívánt dátumtartományon túl esik. A Power Pivot Táblázat importálása varázslója speciális szűrési funkcióival szelektíven kiválaszthatja csak a dátumokat és az adott oszlopokat, amelyekre valóban szüksége van. 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 Pénzügyi év, Hét, Hónap neve stb.), mert azok már léteznek az importált táblában. Bizonyos esetekben azonban, miután a dátumtáblát importálta az adatmodellbe, előfordulhat, hogy további dátumoszlopokat is létre kell hoznia egy adott jelentési készítési szükségtől függően. Szerencsére ez a módszer egyszerűen használható a DAX használatával. A dátumtáblák mezőinek létrehozásáról később olvashat bővebben. Minden környezet különbözik. Ha nem biztos abban, hogy az adatforrások kapcsolódó dátum- vagy naptártáblát tartalmaznak-e, forduljon az adatbázis rendszergazdájához.
Dátumtábla létrehozása a Excel
Létrehozhat egy dátumtáblát a Excel majd átmásolhatja egy új táblába az adatmodellben. Ez a feladat nagyon egyszerű, és nagy rugalmasságot biztosít.
Amikor egy dátumtáblát hoz létre a Excel, egyetlen oszloppal kezd, amely összefüggő dátumtartományt ad meg. Ezután Excel-képletek használatával további oszlopokat is létrehozhat, például Év, Negyedév, Hónap, Pénzügyi év, Időszak stb Excel. Excel munkalapon, vagy miután Excel táblázatot az adatmodellbe másolja, számított oszlopokként is létrehozhatja őket. A további dátumoszlopok Power Pivotban való létrehozásáról a jelen cikk későbbi, Új dátumoszlopok hozzáadása a dátumtáblázathoz című szakaszában olvashat.
How to: Create a date table in Excel and copy it into the Data Model
-
A Excel egy üres munkalap A1cellájába írjon be egy oszlopfejlécnevet a dátumtartomány azonosításához. Ez általában a Dátum, a DateTime vagy a DateKey értékhez hasonló.
-
Az A2 cellábaírjon be egy kezdő dátumot. Például 2010.01.01.
-
Kattintson a kitöltőpontra, és húzza lefelé egy sorszámra, amely tartalmazza a befejezési dátumot. Például 2016.12.31.
-
Az összes sor kijelölése a Dátum oszlopban (az A1 cellában lévő fejléc nevével együtt).
-
A Stílusok csoportban kattintson a Formázás táblázatként gombra,majd válasszon egy stílust.
-
A Formázás táblázatként párbeszédpanelen kattintson az OK gombra.
-
Az összes sor másolása a fejlécet is beleértve.
-
A Power Pivot Kezdőlap lapján kattintson a Beillesztés gombra.
-
A Beillesztés előnézete > tábla neve mezőbe írjon be egy nevet, például Dátum vagy Naptár. Hagyja bejelölve Az első sor használata oszlopfejlécként mezőt, és kattintson az OK gombra.
A Power Pivot új dátumtáblája (ebben a példában Naptár) az alábbihoz hasonló:
Megjegyzés: Csatolt táblát a Hozzáadás az adatmodellhez használatával is létrehozhat. Ez azonban szükségtelenné teszi a munkafüzetet, mert a munkafüzet a dátumtáblának két verzióját tartalmazza; egyet a Excel, a másikat a Power Pivotban.
Megjegyzés: A névdátum egy kulcsszó a Power Pivotban. Ha a Power Pivot Dátum nézetében létrehozott táblázatnak nevet ad, akkor a táblázat nevét egy-egy idézőjelek közé kell tennie minden olyan DAX-képletben, amely hivatkozik rá egy argumentumban. A cikkben található összes példakép és képlet egy Naptár nevű, Power Pivotban létrehozott dátumtáblára hivatkozik.
Most már van egy dátumtáblája az adatmodellben. A DAX használatával új dátumoszlopokat (például Year, Month stb.) is felvehet.
Új dátumoszlopok hozzáadása a dátumtáblához
A dátumtartományok dátumtartományának meghatározásához fontos, hogy egy dátumtáblában minden egyes nap egy sor legyen egy dátumoszloppal. Az is szükséges, hogy kapcsolatot hozzon létre az ténytábla és a dátumtábla között. Ez az egyetlen dátumoszlop, amely minden nap egy sort jelent, nem Power View kimutatás dátumok alapján való elemzésekor. Azt szeretné, hogy a dátumtáblában olyan oszlopok szerepeljenek, amelyek segítenek egy dátumtartomány vagy dátumcsoport adatainak összesítésében. Összeadhatja például az értékesítési összegeket hónap vagy negyedév szerint, vagy létrehozhat egy mértéket, amely az éves 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 a dátumtáblát relációs adatforrásból importálta, előfordulhat, hogy már tartalmazza a kívánt dátumoszloptípusokat. Bizonyos esetekben szükség lehet az oszlopok némelyikének módosítására vagy további dátumoszlopok létrehozására. Ez különösen akkor igaz, ha saját dátumtáblát hoz létre a Excel és az adatmodellbe másolja. A DAX dátum- és időfunkcióinak segítségével szerencsére egyszerűen lehet új dátumoszlopokat létrehozni a Power Pivotban.
Tipp: Ha még nem dolgozott a DAX-val, a gyorsútmutatóval nagyszerűen kezdheti el a tanulást: Ismerkedés a DAX alapjaival a Office.com webhelyen.
DAX dátum- és időfunkciók
Ha már dolgozott dátum- és idő függvényekkel Excel képletekben, akkor valószínűleg ismeri a dátum- és idő függvényeket. Bár ezek a függvények hasonlóak a szervezetben Excel, van néhány fontos különbség:
-
A DAX Dátum és Idő függvénye datetime adattípust használ.
-
Argumentumként egy oszlop értékeit is kiveheti.
-
Dátumértékek visszaadására és/vagy a dátumértékek adására és/vagy az adatokon való módosítására használhatók.
Ezeket a függvényeket gyakran használják egyéni dátumoszlopok létrehozásakor a dátumtáblákban, ezért fontos őket megérteni. A következő függvények közül számosat használva hozunk létre oszlopokat a Year (Év), a Quarter (Negyedév) és a FiscalMonth (Pénzügyi hónap) oszlophoz stb.
Megjegyzés: A DAX dátum- és idő függvénye nem azonos az időintelligencia-függvényekkel. További információ a Power Pivot 2013-ban Excel időintelligencia-ről.
A DAX az alábbi dátum- és időfunkciókat tartalmazza:
Számos más DAX-függvényt is használhat a képletekben. Az itt ismertetett képletek közül sok például matematikai és trigonometriai függvényeket használ, például a MARADÉK és a CSONKfüggvényt, a LOGIKAI függvényeket (például HA)és a szövegfunkciókat (például FORMAT) a további DAX-függvényekkel kapcsolatban, 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áblában további oszlopok 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 összefüggő dátumtartományt tartalmaz.
Év
=ÉV([dátum])
Ebben a képletben az ÉV függvény a Dátum oszlopban lévő értékből adja vissza az évet. Mivel a Dátum oszlop értéke dátumidő adattípussal rendelkezik, az ÉV függvény tudja, hogy miként kell visszaadni az évet a függvényből.
Hónap
=HÓNAP([dátum])
Ebben a képletben – az ÉV függvényhez hasonlóan – egyszerűen a HÓNAP függvényt használva kaphatunk hónap értéket a Dátum oszlopból.
Negyedév
=INT(([Hónap]+2)/3)
Ebben a képletben az INT függvényt használva a dátumértéket egész számként adja vissza. Az INT függvény argumentuma a Hónap oszlopban lévő érték, adja hozzá a 2-t, majd ossza el 3-val a negyedévünk 1 és 4 közötti értékének a be szereznie.
Hónap neve
=FORMAT([dátum],"mmmm")
Ebben a képletben a hónap nevének nevére a FORMAT függvényt használva alakítunk át egy numerikus értéket a Dátum oszlopból szöveggé. A Dátum oszlopot első argumentumként adhatja meg, majd a formátumot; Azt szeretnénk, hogy a hónap neve az összes karaktert mutassa, ezért a "mmmm" karaktereket használjuk. Az eredmény így néz ki:
Ha a hónap nevét három betűre rövidítve szeretnénk visszaadni, a formátum argumentumban a "mmm" formátumot használjuk.
A hét napja
=FORMAT([dátum],"nnn")
Ebben a képletben a FORMÁTUM függvényt használjuk a nap nevének nevére. Mivel csak egy rövidített nevet szeretnénk megadni, a formátum argumentumban a "ddd" értéket adhatja meg.
Kimutatásminta
Ha már tartalmaz mezőket a dátumokhoz (év, negyedév, hónap stb.), használhatja őket kimutatásban vagy kimutatásban. Az alábbi képen például a SalesAmount (Értékesítés) mező látható az ÉRTÉKEK oszlop értékesítésre vonatkozó adattáblájában, illetve a Year (Év) és a Quarter (Negyedév) mező a SOROK oszlop Naptár dimenziótáblájában. Az Értékesítés összegző érték az év és a negyedév kontextusa szerint összesítve.
Képlet példák egy pénzügyi évhez
Pénzügyi év
=HA([Hónap]<= 6,[Év],[Év]+1)
Ebben a példában a pénzügyi év július 1-jét veszi kezdetét.
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átumában. A pénzügyi év be szereznie először a HA függvényt használva teszteli, hogy a Hónap értéke kisebb-e, mint 6. Ha a Hónap argumentum értéke kisebb vagy egyenlő 6-ot, akkor a Year oszlopból adja vissza az értéket. Ha nem, akkor adja vissza a Year értéket, és adjon hozzá 1-et.
A pénzügyi év záró hónapját úgy is megadhatja, ha létrehoz egy olyan mértéket, amely egyszerűen meghatározza a hónapot. Például YE:=6. Ezután hivatkozhat a mérték nevére a hónapszám helyén. Például: =HA([Hónap]<=[ÉÉÉ];[Év];[Év]+1). Ez több különböző képletben nagyobb rugalmasságot biztosít a pénzügyi év záró hónapra valóhivatkozáskor.
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-e, mint 6, akkor vegye fel a 6-ot, és adja hozzá a Hónap értékből, ellenkező esetben vonja ki a 6-ot a [Hónap] értékből.
Pénzügyi negyedév
=INT(([FiscalMonth]+2)/3)
A FiscalQuarterhez használt képlet nagyban megegyezik a naptári év negyedévi képletével. Az egyetlen különbség az, hogy a [PénzügyiHónap] értéket adhatja meg a [Hónap] helyett.
Ünnepnapok vagy különleges dátumok
Előfordulhat, hogy szeretne egy dátumoszlopot is szerepeltesse, amely jelzi, hogy bizonyos dátumok ünnepnapok vagy más speciális dátumok. Előfordulhat például, hogy egy Ünnepnap mezőt hozzáadva, szeletelőként vagy szűrőként meg szeretné összegni az új évek napi értékesítési összegét. Más esetekben előfordulhat, hogy ezeket a dátumokat ki szeretné zárni más dátumoszlopból vagy mértékből.
Az ünnepnapok és a különleges napok is meglehetősen egyszerűek. Létrehozhat egy táblázatot a Excel, amely tartalmazza a kívánt dátumokat. Ezután a Hozzáadás az adatmodellhez gombra másolva vagy használhatja a hozzáadást az adatmodellhez csatolt táblaként. A legtöbb esetben nincs szükség kapcsolat létrehozására a tábla és a Naptár tábla között. Az arra hivatkozó képletek a KERES.ÉRTÉK függvényt használva is visszaadnak értékeket.
Az alábbi táblázat egy táblázatot tartalmaz egy Excel, amely tartalmazza a dátumtáblához hozzáadható ünnepnapokat:
|
Dátum |
Ünnepnap |
|---|---|
|
1/1/2010 |
Új év |
|
11/25/2010 |
Visszatért |
|
12/25/2010 |
Karácsonyi |
|
2011.01.01. |
Új év |
|
11/24/2011 |
Visszatért |
|
12/25/2011 |
Karácsonyi |
|
2012.01.01 |
Új év |
|
2012.11.22. |
Visszatért |
|
12/25/2012 |
Karácsonyi |
|
1/1/2013 |
Új év |
|
11/28/2013 |
Visszatért |
|
12/25/2013 |
Karácsonyi |
|
11/27/2014 |
Visszatért |
|
12/25/2014 |
Karácsonyi |
|
2014.01.01. |
Új év |
|
11/27/2014 |
Visszatért |
|
12/25/2014 |
Karácsonyi |
|
1/1/2015 |
Új év |
|
11/26/2014 |
Visszatért |
|
12/25/2015 |
Karácsonyi |
|
2016.01.01. |
Új év |
|
11/24/2016 |
Visszatért |
|
12/25/2016 |
Karácsonyi |
A dátumtáblában létrehozunk egy Ünnep oszlopot, és az alábbihoz hasonló képletet használjuk:
=KERES.ÉRTÉK(Ünnepek[Ünnepek];Ünnepek[dátum];Naptár[dátum])
Nézzük meg még jobban ezt a képletet.
A KERES.ÉRTÉK függvény használatával az Ünnepnapok táblázat Ünnepnap oszlopában lévő értékeket használhatja. Az első argumentumban azt az oszlopot adhatja meg, ahol az eredményértéket meg fogja adni. Azért a Munkaszüneti napok táblában adhatja meg az Ünnepnap oszlopot, mert ezt az értéket szeretnénk visszakapni.
=KERES.ÉRTÉK(Ünnepek[Ünnepek];Ünnepek[dátum];Naptár[dátum])
Ezután megadhatja a második argumentumot, a keresett dátumokat tartalmazó keresőoszlopot. Az Ünnepnapok táblában a Dátum oszlopot adhatja meg, az alábbihoz hasonlóan:
=KERES.ÉRTÉK(Ünnepek[Ünnepek];Ünnepek[dátum];Naptár[dátum])
Végül megadhatja a Naptár táblázatban azt az oszlopot, amely a Kívánt dátumokat tartalmazza az Ünnep táblában. Ez természetesen a Naptár tábla Dátumoszlopa.
=KERES.ÉRTÉK(Ünnepek[Ünnepek];Ünnepek[dátum];Naptár[dátum])
Az Ünnep oszlop minden olyan sor ünnepnapnevét adja vissza, amely az Ünnepnapok táblázat egyik dátumértékével egyezik.
Egyéni naptár – tizenhárom négy hetes időszak
Egyes szervezetek, például a kiskereskedelmi vagy élelmiszer-szolgáltatás, gyakran különböző időszakokról számolnak be, például tizenhárom négyhetes időszakról. Tizenhárom négy hetes időszak naptárral minden időszak 28 nap; ezért minden időszak négy hétfőt, négy keddet, négy szerdát és így tovább. Minden időszak azonos számú napot tartalmaz, és az ünnepnapok általában minden évben ugyanazokba az időszakba esnek. Választhat, hogy a hét bármely napján kezd egy időszakot. A naptári vagy pénzügyi év dátumaihoz hasonlóan a DAX segítségével további oszlopokat is létrehozhat 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-től kezdődik.
Hét
Ez az érték a pénzügyi év első teljes hetével kezdődő hétszámot adja meg. Ebben a példában az első teljes hét vasárnaptól kezdődik, így a Naptár tábla első pénzügyi évének első teljes hete valójában 2010.07.04-én kezdődik, és a Naptár táblázat utolsó teljes hete alatt folytatódik. Bár maga az érték nem igazán hasznos az elemzésben, számításra van szükség más 28 napos időszakképletekben való használatra.
=INT([dátum]-40356)/7)
Nézzük meg még jobban ezt a képletet.
Először létrehozunk egy képletet, amely a Dátum oszlop értékeit adja vissza egész számként, az alábbihoz hasonlóan:
=INT([dátum])
Ezután az első pénzügyi év első vasárnapját szeretnénk keresni. Látjuk, hogy 2010.07.04-e van.
Most vonja ki ebből az értékből a 40356-ot (amely a 2010.06.27., az előző pénzügyi év utolsó vasárnapja) egészét abból az értékből, hogy a naptártáblában a napok kezdete óta hány nap múlva indulva le a következőt:
=INT([dátum]-40356)
Ezután ossza el az eredményt 7-tel (egy hét napjaival), a következő szerint:
=INT(([dátum]-40356)/7)
Az eredmény így néz ki:
Period
Az egyéni naptár időszaka 28 napot tartalmaz, és mindig vasárnapon fog kezdődni. Ez az oszlop az első pénzügyi év első vasárnappal kezdődő időszakának számát adja vissza.
=INT(([Hét]+3)/4)
Nézzük meg még jobban ezt a képletet.
Először létrehozunk egy képletet, amely a Hét oszlop értékét adja eredményül egész számként, az alábbihoz hasonló:
=INT([Hét])
Ezután adjon hozzá 3-as értéket ehhez az értékhez:
=INT([Hét]+3)
Ezután ossza el az eredményt 4-el, a következő szerint:
=INT(([Hét]+3)/4)
Az eredmény így néz ki:
Pénzügyi év időszaka
Ez az érték egy időszak pénzügyi évét adja eredményül.
=INT(([Időszak]+12)/13)+2008
Nézzük meg még jobban ezt a képletet.
Először létrehozunk egy képletet, amely visszaad egy értéket a Periódus értékből, és hozzáad 12-et:
= ([Időszak]+12)
Az eredményt 13-sal osztjuk el, mivel a pénzügyi év 28 napos időszaka van:
=(([Időszak]+12)/13)
2010-et adunk hozzá, mert ez az első év a táblázatban:
=(([Időszak]+12)/13)+2010
Végül az INT függvény segítségével eltávolítjuk az eredmény törtrészét, és a következő módon, 13- as számmal elosztva teljes számot adunk vissza:
=INT(([Időszak]+12)/13)+2010
Az eredmény így néz ki:
Időszak a FiscalYearben
Ez az érték az időszak számát (1–13) adja vissza, az egyes pénzügyi évek első teljes (vasárnaptól kezdődő) időszakával kezdve.
=HA(MARADÉK([Időszak];13); MARADÉK([Időszak];13);13)
Ez a képlet egy kicsit bonyolultabb, ezért először egy jobban megértő nyelven fogjuk leírni. Ez a képlet azt állítja, hogy az [Időszak] értékét 13-ával el kell osztani, hogy az év egy időszakszámát (1-13) kapják. Ha ez a szám 0, akkor az visszaadott szám 13.
Először létrehozunk egy képletet, amely visszaadja az Időszak érték fennmaradó részét 13-ával. A MARADÉK (matematikai és trigonometriai) függvényeket a következő használhatja:
=MARADÉK([Időszak];13)
Ez a legtöbb esetben a kívánt eredményt adja, kivéve azt a részt, ahol az Időszak értéke 0, mert ezek a dátumok nem esnek az első pénzügyi évbe, mint a példában használt Naptár dátumtábla első öt napjában. Ezt egy HA függvény segítségével tudjuk elintézni. Ha az eredmény 0, akkor a 13-as eredményt jükjük, így:
=HA(MARADÉK([Időszak],13),MARADÉK([Időszak],13),13)
Az eredmény így néz ki:
Kimutatásminta
Az alábbi képen egy kimutatás látható, amelynél a SALES fact (Értékesítési adatok) táblából származó SalesAmount (Értékesítés) mező látható az ÉRTÉKEK oszlopban, a PeriodFiscalYear és a PeriodInFiscalYear (IdőszakInFiscalYear) mező pedig a Calendar date dimension (Sorok) tábla SalesAmount (Értékesítés) mezőjével. Az Értékesítés összegző érték a pénzügyi év és a pénzügyi év 28 napos időszaka szerint összesítve.
Kapcsolatok
Miután létrehozott egy dátumtáblát az adatmodellben, a kimutatások és jelentések adatainak böngészéséhez és a dátumdimenzió táblázat oszlopai alapján összesített adatok ö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, meg kell győződni arról, hogy a kapcsolatot a datetime (Date) adattípusú oszlopok között hozza létre.
Az ténytábla minden dátumértékében a dátumtáblában a kapcsolódó keresési oszlopnak egyező értékeket kell tartalmaznia. A 2012.08.15.12 12:00 értéket tartalmazó Értékesítési adatok táblában lévő egyik sornak (tranzakciórekordnak) például a DateKey oszlopban egy megfelelő értékkel kell bírni a dátum (naptár) tábla kapcsolódó Dátum oszlopában. Ez az egyik legfontosabb ok, amiért azt szeretné, hogy a dátumtáblában a dátumoszlop összefüggő dátumtartományt tartalmaz, amely a lehetséges dátumokat is tartalmazza a ténytáblában.
Megjegyzés: Bár az egyes táblázatban a dátumoszlopnak azonos adattípusúnak kell lennie (Dátum), az egyes oszlopok formátuma nem számít.
Megjegyzés: Ha a Power Pivot nem enged kapcsolatokat létrehozni a két tábla között, előfordulhat, hogy a dátummezők nem pontosan pontosan tárolják a dátumot és az időt. Az oszlopformázástól függően előfordulhat, hogy az értékek ugyanúgy néznek ki, de másképpen tárolódnak. További információ az időről.
Megjegyzés: Kerülje a kapcsolatokban az egész helyettesítő kulcsok használatával való használatot. Amikor relációs adatforrásból importál adatokat, a dátum- és időoszlopokat gyakran egy helyettesítő kulcs jelképez, amely egy egyedi dátum ábrázolásaként használt egész oszlop. A Power Pivotban ne hozzon létre kapcsolatokat egész dátum/idő billentyűkkel, hanem használjon olyan oszlopokat, amelyek dátum adattípussal egyedi értékeket tartalmaznak. Bár a helyettesítő kulcsok használata a hagyományos adat raktárakban ajánlott gyakorlatnak számít, az egész kulcsokat nem szükséges használni a Power Pivotban, és megnehezítheti az értékek csoportosítását különböző dátumidőszakok szerint.
Ha a kapcsolat létrehozásakor típusel kapcsolatos eltérést kap, annak valószínűleg az az oka, hogy az adattábla oszlopa nem Dátum típusú. Ez olyankor fordulhat elő, ha a Power Pivot nem tud automatikusan dátum adattípusúvá alakítani egy nem dátum adattípust (általában szöveges adattípust). Az oszlopot továbbra is használhatja a ténytáblában, de egy új számított oszlopban DAX-képlettel kell konvertálni az adatokat. Lásd: Szöveg adattípus dátumának dátum adattípusra konvertálása a függelék későbbi, Későbbiakban.
Több kapcsolat
Bizonyos esetekben szükség lehet több kapcsolat létrehozására vagy dátumtáblák létrehozására. Ha például az Értékesítési adatok táblában több dátummező is található (például DateKey, ShipDate és ReturnDate, akkor mindnek lehet kapcsolata a Naptár dátumtáblájában a Dátum mezővel, de ezek közül csak az egyik lehet aktív kapcsolat). Ebben az esetben, mivel a DateKey a tranzakció dátumát képviseli, ezért a legfontosabb dátum, ez a legjobb esetben aktív kapcsolatként szolgál. A többiek inaktív kapcsolatban vannak.
Az alábbi kimutatás kiszámítja a teljes értékesítést Pénzügyi év és Pénzügyi negyedév szerint. A Total Sales (Összes eladás) nevű mérték a Total Sales:=SUM([SalesAmount])képlettel az ÉRTÉKEK, a FiscalYear és a FiscalQuarter mezők pedig a SOROK sorba kerülnek.
Ez az egyenesre ható kimutatás helyesen működik, mert szeretnénk összegni a teljes értékesítést a DateKey mezőbena tranzakció dátuma szerint. A Total Sales (Összes eladás) mérték a DateKey oszlopban megadott dátumokat használja, és pénzügyi év és pénzügyi negyedév szerint összegz, mivel van kapcsolat a Sales (Értékesítés) tábla DateKey (Értékesítés) és Date (Dátum) oszlopa között a Naptár dátumtáblájában.
Inaktív kapcsolatok
De mi a célunk, ha nem tranzakciós dátum, hanem szállítási dátum szerint szeretnénk összegni a teljes értékesítést? Kapcsolatra van szükségünk az Értékesítés tábla SzállításiDátum oszlopa és a Naptár tábla Dátum oszlopa között. Ha nem hozunk létre ilyen kapcsolatot, az összegzéseink mindig a tranzakció dátumán alapulnak. Több kapcsolat azonban lehet, bár 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 SzállításiDátum inaktív kapcsolatban áll, ezért minden olyan mértékképletnek, amelyet a szállítási dátumok alapján az adatok összesítésére hozott létre, meg kell határoznia az inaktív kapcsolatot a USERELATIONSHIP függvénnyel.
Mivel például inaktív kapcsolat van az Értékesítés tábla SzállításiDátum oszlopa és a Naptár tábla Dátum oszlopa között, létrehozhatunk egy mértéket, amely a teljes értékesítést szállítási dátum szerint összegzi. A következő képlettel adhatja meg a használni kívánt kapcsolatot:
Total Sales by Ship Date:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))
Ez a képlet egyszerűen kimondja: Az Értékesítés összegének kiszámítása, de szűrés az Értékesítés tábla SzállításiDátum oszlopa és a Naptár tábla Dátum oszlopa közötti kapcsolat alapján.
Ha most létrehozunk egy kimutatást, és a Teljes értékesítés szállítási dátum szerint mértéket az ÉRTÉKEK, a Pénzügyi év és a Pénzügyi negyedév értéket pedig a SOROK sorba jük, 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 azok 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 megköveteli, hogy bármely tevékenység (például a Szállítási dátum szerint összes eladás) hivatkozni legyen az inaktív kapcsolatra a képletében. Használhatja több dátumtáblát is.
Több dátumtáblából
Úgy is dolgozhat több dátumoszloppal az ténytáblában, hogy több dátumtáblát hoz létre, és külön aktív kapcsolatokat hoz létre közöttük. Nézzük ismét az Értékesítés táblázatos példát. Három oszlopban dátumok vannak, amelyek alapján összeg szeretnénk összesíteni az adatokat:
-
A DateKey az egyes tranzakciók értékesítési dátumával.
-
A SzállításiDátum – az a dátum és időpont, amikor az eladott cikkeket kiszállították a vevőnek.
-
A ReturnDate – egy vagy több elem visszaadott dátumával és időpontával.
Ne feledje, hogy a tranzakció dátumát is adó DateKey mező a legfontosabb. A dátumok alapján a legtöbb összesítést meg fogjuk tenni, ezért bizonyára szeretnénk, hogy legyen kapcsolat ez és a Naptár tábla Dátum oszlopa között. Ha nem szeretnénk inaktív kapcsolatokat létrehozni a SzállításiDátum és a VisszatérésiDátum és a Naptár tábla Dátum mezője között, így speciális mértékképleteket igényel, létrehozhatunk további dátumtáblákat a szállítási dátumhoz és a visszaküldés dátumához. Ezután létrehozhatunk közöttük aktív kapcsolatokat.
Ebben a példában létrehoztunk egy másik SzállításiCalendar nevű dátumtáblát. Ez természetesen azt is jelenti, hogy további dátumoszlopokat kell létrehoznia, és mivel ezek a dátumoszlopok egy másik dátumtáblában vannak, szeretnénk úgy nevezze el őket, hogy elkülönülnek a Naptár tábla ugyanazon oszlopaitól. Létrehoztunk például a ShipYear, a ShipMonth, a ShipQuarter stb. nevű oszlopokat.
Ha létrehozunk egy kimutatást, és a Total Sales (Összes eladás) mértéket AZ ÉRTÉKEK, a Szállítási AdatokYear és a ShipFiscalQuarter értéket a SOROK mezőben jükjük, akkor ugyanazt az eredményt látjuk, mint amikor inaktív kapcsolatot és egy speciális Total Sales by Ship Date számított mezőt hozunk létre.
A fenti módszerek mindegyikéhez gondos figyelmet kell fordítani. Ha több kapcsolatot használ egyetlen dátumtáblával, akkor előfordulhat, hogy létre kell hoznia speciális értékeket, amelyek az inaktív kapcsolatokat a USERELATIONSHIP függvény használatával átviteleikre használják. Ha azonban több dátumtáblát hoz létre, az összezavarhatja a mezőlistát, és mivel az adatmodellben több tábla található, több memóriára lesz szüksége. Kísérletezzen a legmegfelelőbb megoldásokkal.
Date Table tulajdonság
A Date Table tulajdonság beállítja a Time-Intelligence függvények (például TOTALYTD, PREVIOUSMONTH és DATESBETWEEN) megfelelő működését. Amikor egy számítást az alábbi függvények egyikének használatával futtat, a Power Pivot képletmotorja tudja, hogy hol kell a szükséges dátumokat kiszámítani.
Figyelmeztetés: Ha nincs beállítva ez a tulajdonság, előfordulhat, hogy a DAX-Time-Intelligence használó measures nem ad pontos eredményt.
A Dátumtábla tulajdonság beállításakor meg kell adnia egy dátumtáblát és egy dátumoszlopot, benne a Date (datetime) adattípussal.
Cél: A Dátumtábla tulajdonság beállítása
-
A naptár PowerPivot válassza a Naptár táblát.
-
A Tervezés lapon kattintson a Megjelölés dátumtáblaként gombra.
-
A Megjelölés dátumtáblaként párbeszédpanelen jelöljön ki egy egyedi értékeket tartalmazó oszlopot, és válassza a Dátum adattípust.
Munkaidő- és munka
A Dátum adattípusú dátumértékek számnak Excel vagy SQL Server számértékek. Ebben a számban az időpontra hivatkozó számjegyek szerepelnek. Sok esetben ez az idő minden egyes sorban éjfél. Ha például egy Értékesítési adatok tábla DateTimeKey mezőjében a 10/19/2010 12:00:00 de. érték látható, az azt jelenti, hogy az értékek a pontosság napi szintjére esnek. Ha a DateTimeKey mezőértékek tartalmaznak időpontot, például 2010.10.19.08:44:00, az azt jelenti, hogy az értékek a pontosság percben megadott szintjére esnek. Az értékeknek óraszintű vagy akár másodperces pontosságúnak is kell lennie. Az időérték pontosságának szintje jelentős hatással lesz a dátumtábla létrehozására, valamint a közötte és a ténytábla közötti kapcsolatokra.
Azt kell megállapítania, hogy az adatokat napi vagy időszintű pontossággal összesíti-e. Más szóval, a dátumtáblában oszlopokat is használhat a kimutatás dátummezőiként, például Reggel, Délután vagy Óra dátummezőként a kimutatás Sor, Oszlop vagy Szűrés területén.
Megjegyzés: A napok a DAX időintelligencia-függvényekkel használható legkisebb időegysége. Ha nem kell időértékekkel dolgoznia, akkor csökkentse az adatok pontosságát, hogy a napokat használják minimális egységként.
Ha az adatokat időszintre szeretné összesíteni, akkor a dátumtáblának szüksége lesz egy dátumoszlopra, amely tartalmazza az időt. Valójában egy olyan dátumoszlopra van szükség, amely minden órához egy sorral, vagy akár minden nap minden percében, a dátumtartomány minden évében egy sorral. Ennek az az oka, hogy az adattábla DateTimeKey oszlopa és a dátumtábla dátumoszlopa között egyező értékeket kell létrehozni. Képzelje el, hogy ha sok évet is bele foglal, az nagyon nagy dátumtáblát jelent.
A legtöbb esetben azonban csak a napra szeretné összesíteni az adatokat. Más szóval a kimutatás sor-, oszlop- vagy szűrőterületének mezőiként olyan oszlopokat kell használnia, mint a Év, a Hónap, a Hét vagy a Hét napja. Ebben az esetben a dátumtáblában a dátumoszlopnak csak egy sorra van szüksége az év minden egyes napja számára, ahogy azt korábban említettük.
Ha a dátumoszlop időszintű pontosságot tartalmaz, de csak egy napra lesz összesítve, hogy kapcsolatot hozzon létre az adattábla és a dátumtábla között, előfordulhat, hogy módosítania kell az adattáblát egy új oszlop létrehozásával, amely a dátumoszlop értékeit egy nap értékre csonkíti. Más szóval alakítsa át a 2010.10.19. 8:44:00 értéket 2010.10.19. 12:00:00 de. Ezután létrehozhatja az új oszlop és a dátumoszlop közötti kapcsolatot a dátumtáblában, mivel az értékek megegyeznek.
Nézzünk egy példát. Ezen a képen egy DateTimeKey oszlop látható a Sales fact (Értékesítési adatok) táblában. A táblázatban lévő adatok összes összesítésének csak a nap szintjén kell lennie a Naptár dátumtáblájában lévő oszlopok (év, hónap, negyedév stb.) használatával. Az értékben megadott időpont nem releváns, csak a tényleges dátum.
Mivel ezeket az adatokat nem kell időszinten elemezni, nincs szükségünk a Naptár dátumtáblájában a Dátum oszlopra, hogy minden egyes évben minden egyes nap és minden nap egy-egy percében egy sor legyen. Így tehát a dátumtáblában a Dátum oszlop az alábbihoz hasonló:
Ha kapcsolatot szeretnénk létrehozni az Értékesítés 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 fact (Értékesítési adat) táblában, és a CSONK függvény segítségével csonkuljuk a DateTimeKey oszlopban lévő dátum- és időértéket egy olyan dátumértékre, amely megfelel a Naptár tábla Dátum oszlopában lévő értékeknek. A képletünk így néz ki:
=CSONK([DateTimeKey],0)
Ez a cikk egy új oszlopot (DateKey nevet ad) a DateTimeKey oszlop dátumával, és 12:00:00 de. időpontot ad az egyes sorokhoz:
Most már kapcsolatot hozhatunk létre 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 az óra pontossági szintre csökkenti a DateTimeKey oszlop idő pontosságát. Ebben az esetben a CSONK függvény nem működik, de más DAX dátum- és időfunkciók segítségével egy óra pontossággal kinyerhet és újra össze lehet vonni egy új értéket. Ehhez hasonló képletet használhatunk:
= DÁTUM (ÉV([DateTimeKey]), HÓNAP([DateTimeKey]), NAP([DateTimeKey]) ) + TIME ([DateTimeKey]), 0, 0)
Az új oszlop a következő:
Ha a dátumtábla Dátum oszlopában az óra pontossági szintnek megfelelő értékek vannak, kapcsolatot hozhatunk létre közöttük.
Dátumok használhatóbbak
A dátumtáblában létrehozott dátumoszlopok nagy része szükséges más mezőkhöz, de valójában nem mind hasznos az elemzéshez. Az ebben a cikkben hivatkozott és látható Értékesítés tábla DateKey mezője például fontos, mert minden tranzakciónál a tranzakciót egy adott dátumon és időpontban előfordulóként rögzíti a rendszer. Egy elemzési és jelentési szempontból azonban nem minden hasznos, mert nem használható sor-, oszlop- vagy szűrőmezőként a kimutatásban vagy kimutatásban.
Hasonlóképpen, a példánkban a Naptár tábla Dátum oszlopa nagyon hasznos, valójában nagyon fontos, de nem használhatja dimenzióként a kimutatásban.
Ha a táblázatokat és az oszlopokat a lehető leghasznosabban meg szeretne tartani, és Power View kimutatások és kimutatások mezőlistái könnyebben navigálnak, fontos, hogy elrejtse 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 látható Ünnepnapok táblázat olyan ünnepnapokat tartalmaz, amelyek fontosak a Naptár tábla egyes oszlopai számára, de maguk az Ünnepnapok tábla Dátum és Ünnepnap oszlopát nem használhatja kimutatásmezőként. Itt is látható, hogy a mezőlisták könnyebben navigálhatóak legyen, elrejtheti a teljes Ünnepnapok táblát.
A dátumokkal való munka másik fontos szempontja az elnevezési konvenció. A PowerPivotban a kívánt nevet használhatja a táblázatoknak és az oszlopoknak. Tartsa azonban szem előtt, különösen, ha a munkafüzetet megosztja más felhasználókkal, egy jó elnevezési konvenció megkönnyíti a táblázatok é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étrehozta a dátumtáblát az adatmodellben, megkezdheti az olyan intézkedések létrehozását, amelyek segítenek a legtöbbet kihozni az adatokból. Lehetnek olyan egyszerűek, mint az aktuális év értékesítési összegének összegzése, míg mások összetettebbek, ahol egyedi dátumok adott tartományát kell szűrni. További információt a Measures in Power Pivot and Time Intelligence Functions (A Power Pivot és az időintelligencia-függvények) tartalmaz.
Függelék
Szöveg adattípus dátumok dátum adattípusra konvertálása
Bizonyos esetekben a tranzakciós adatokat tartalmazó adattábla szöveges adattípus dátumokat tartalmazhat. Ez azt jelenti, hogy a 2012-12-04T11:47:09 dátum valójában nem dátum, vagy legalábbis nem az a dátumtípus, amit a Power Pivot képes megérteni. Valójában csak dátumként felolvasott szöveg. Ahhoz, hogy kapcsolatot hozzon létre egy dátumoszlop és egy dátumtábla dátumoszlopa között, mindkét oszlopnak Dátum adattípussal 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 értelmezni a dátumokat, és automatikusan valódi dátum adattípusúvá alakítani őket. Ha a Power Pivot nem tud adattípus-konverziót lekonverziót, típuseltérésről kap hibaüzenetet.
A dátumokat azonban továbbra is valódi dátum adattípusra konvertálhatja. Létrehozhat egy új számított oszlopot, és egy DAX-képlettel elemezheti a szöveges karakterláncok évét, hónapját, napját, idejét stb., majd össze is egyesítheti úgy, ahogyan a Power Pivot valódi dátumként fel tudja olvasni azt.
Ebben a példában egy Értékesítés nevű ténytáblát importáltunk a Power Pivotba. Tartalmaz egy DateTime nevű oszlopot. Az értékek így jelennek meg:
Ha az Adattípus a Formázás csoport Power Pivot Kezdőlap lapján látható, láthatja, hogy az Szöveg adattípus.
A dátumtáblában nem lehet kapcsolatot létrehozni a DateTime és a Date oszlop között, mert az adattípusok nem egyeznek. Ha az adattípust Dátumtípusra módosítjuk, típuseloml hibaüzenet jelenik meg:
Ebben az esetben a Power Pivot nem tudta konvertálni az adattípust szövegből dátumra. Ezt az oszlopot továbbra is felhasználhatja, de ahhoz, hogy valódi dátum adattípusú legyen, létre kell hoznunk egy új oszlopot, amely elemezi a szöveget, majd újra létrehozza azt egy értékre, amelyből a Power Pivot dátum adattípust tud létrehozni.
Ne feledje, hogy a cikk korábbi, Munkaidő-beállítások című szakaszában; ha nem szükséges, hogy az elemzés napi szintű pontosságú legyen, a ténytáblában található dátumokat napi pontosságúra kell átalakítania. Ezt szem előtt tartva azt szeretnénk, hogy az új oszlop értékeinek pontossági szintje (az idő kivételével) legyen. Mindkét esetben dátum adattípusúvá alakítjuk a DateTime oszlopban lévő értékeket, és a következő képlettel töröljük a pontosság időszintjüket:
=DÁTUM(BAL([DateTime],4), KÖZÉP([DateTime],6,2), KÖZÉP([DateTime],9,2))
Ezzel egy új oszlopot ad meg nekünk (ebben az esetben Date nevű oszlopot). A Power Pivot azt is észleli, hogy az értékek dátumok, és az adattípust automatikusan Dátum típusra állítja.
Ha meg szeretné őrizni az időszintet, egyszerűen kiterjesztjük a képletet úgy, hogy tartalmazza az órákat, a perceket és a másodperceket.
=DÁTUM(BAL([DateTime],4), KÖZÉP([DateTime],6,2), KÖZÉP([DateTime],9,2)) +
TIME(KÖZÉP([DateTime],12,2), KÖZÉP([DateTime],15,2), KÖZÉP([DateTime],18,2))
Most, hogy létrehoztunk egy Dátum adattípust tartalmazó Dátum oszlopot, kapcsolatot hozhatunk létre közötte és egy dátumoszlop között.
További források
QuickStart útmutató: A DAX alapjai 30 percben
