Datų lentelės "Power Pivot" yra būtinos norint naršyti ir skaičiuoti duomenis per tam tikrą laiką. Šiame straipsnyje pateikiama išsami informacija apie datų lenteles ir kaip jas sukurti naudojant "Power Pivot". Visų pirma šiame straipsnyje aprašoma:
-
Kodėl datų lentelė yra svarbi naršant ir apskaičiuojant duomenis pagal datas ir laiką.
-
Kaip naudojant "Power Pivot" įtraukti datų lentelę į duomenų modelį.
-
Kaip sukurti naujus datos stulpelius, pvz., Metai, Mėnuo ir Laikotarpis datų lentelėje.
-
Kaip sukurti ryšius tarp datos ir faktų lentelių.
-
Kaip dirbti su laiku.
Šis straipsnis skirtas vartotojams, kurie naudoja "Power Pivot". Tačiau svarbu gerai suprasti duomenų importavimą, ryšių kūrimą ir apskaičiuotų stulpelių bei matų kūrimą.
Šiame straipsnyje aprašoma , kaip naudoti DAX Time-Intelligence funkcijas matų formulėse. Daugiau informacijos apie tai, kaip kurti matavimus naudojant DAX laiko informacijos funkcijas, žr. Laiko informacija "Power Pivot" programoje "Excel".
Pastaba: "Power Pivot" pavadinimai "matas" ir "apskaičiuotasis laukas" yra sinonimai. Šiame straipsnyje naudojame pavadinimo matą. Daugiau informacijos žr. "Power Pivot" matai.
Turinys
Datų lentelių supratimas
Beveik visa duomenų analizė apima naršymą ir duomenų palyginimą pagal datas ir laiką. Pavyzdžiui, galbūt norėsite sumuoti praėjusio finansinio ketvirčio pardavimo sumas, tada palyginti šias sumas su kitais ketvirčiais arba galite norėti apskaičiuoti mėnesio pabaigos balansą sąskaitoje. Kiekvienu iš šių atvejų datas naudojate kaip būdą sugrupuoti ir agreguoti konkretaus laikotarpio pardavimo operacijas arba balansus.
"Power View" ataskaita
Datų lentelėje gali būti daug skirtingų datų ir laiko reprezentacijos. Pvz., datų lentelėje dažnai būna tokie stulpeliai kaip Finansiniai metai, Mėnuo, Ketvirtis arba Laikotarpis, kuriuos galite pasirinkti kaip laukus iš laukų sąrašo, kai duomenys rikiuojami ir filtruojami "PivotTable" arba "Power View" ataskaitose.
"Power View" laukų sąrašas
Datos stulpeliuose, pvz., Year, Month ir Quarter, norint įtraukti visas datas į atitinkamą diapazoną, datų lentelėje turi būti bent vienas stulpelis su nuosekliu datų rinkiniu. T. y. tas stulpelis turi turėti po vieną eilutę kiekvienai dienai kiekvieniems metams, įtrauktiems į datos lentelę.
Pavyzdžiui, jei duomenys, kuriuos norite naršyti, turi datas nuo 2010 m. vasario 1 d. iki 2012 m. lapkričio 30 d., o jūs pateikiate ataskaitą apie kalendorinius metus, jums reikės datų lentelės su bent datų diapazonu nuo 2010 m. sausio 1 d. iki 2012 m. gruodžio 31 d. Kiekvienais metais jūsų datų lentelėje turi būti visos kiekvienų metų dienos. Jei reguliariai atnaujinsite duomenis naujesniais duomenimis, galbūt norėsite pabaigos datą paleisti metams ar dviems, kad nereikėtų atnaujinti datų lentelės, kol baigsis laikas.
Datų lentelė su nuosekliu datų rinkiniu
Jei teikiate ataskaitą apie finansinius metus, galite sukurti datų lentelę su nuosekliu kiekvienų finansinių metų datų rinkiniu. Pavyzdžiui, jei jūsų finansiniai metai prasideda kovo 1 d., o jūs turite 2010 finansinių metų duomenis iki dabartinės datos (pvz., 2013 finansiniais metais), galite sukurti datų lentelę, kuri prasideda 2009-03-01 ir į ją įtraukti bent kas dieną kiekvienais finansiniais metais iki paskutinės 2013 finansinių metų datos.
Jei pateiksite ataskaitą apie kalendorinius metus ir finansinius metus, jums nereikės kurti atskirų datų lentelių. Vienoje datų lentelėje gali būti stulpelių kalendoriniams metams, finansiniams metams ir net trylikai keturių savaičių laikotarpio kalendoriaus. Svarbu, kad jūsų datų lentelėje būtų nuoseklių visų įtrauktų metų datų rinkinys.
Datos lentelės įtraukimas į duomenų modelį
Datos lentelę į duomenų modelį galite įtraukti keliais būdais:
-
Importuoti iš sąryšinės duomenų bazės arba kito duomenų šaltinio.
-
Sukurkite datos lentelę programoje "Excel", tada nukopijuokite arba susiekite su nauja lentele "Power Pivot".
-
Importuoti iš " Microsoft Azure Marketplace".
Pažvelkime į kiekvieną iš jų atidžiau.
Importavimas iš sąryšinės duomenų bazės
Jei importuojate dalį arba visus duomenis iš duomenų sandėlio ar kito tipo sąryšinės duomenų bazės, gali būti, kad jau yra datų lentelė ir ryšys tarp jos ir kitų importuojamų duomenų. Datos ir formatas tikriausiai atitiks faktų duomenų datas, o datos tikriausiai praeityje prasidės ir nueis į ateitį. Norima importuoti datų lentelė gali būti labai didelė ir joje gali būti datų diapazonas, viršijantis tai, ką reikės įtraukti į duomenų modelį. Galite naudoti "Power Pivot" lentelių importavimo vediklio išplėstines filtro funkcijas, kad pasirinktinai pasirinktumėte tik datas ir konkrečius stulpelius, kurių jums tikrai reikia. Tai gali gerokai sumažinti darbaknygės dydį ir pagerinti našumą.
Lentelės importavimo vediklis
Daugeliu atvejų nereikės kurti jokių papildomų stulpelių, pvz., Finansiniai metai, Savaitė, Mėnesio pavadinimas ir t. t., nes jie jau bus importuotoje lentelėje. Tačiau kai kuriais atvejais, importavus datos lentelę į duomenų modelį, gali reikėti sukurti papildomų datos stulpelių, atsižvelgiant į konkretų ataskaitų poreikį. Laimei, tai lengva padaryti naudojant DAX. Daugiau apie datų lentelės laukų kūrimą sužinosite vėliau. Kiekviena aplinka yra skirtinga. Jei nesate tikri, ar jūsų duomenų šaltiniuose yra susijusi data arba kalendoriaus lentelė, kreipkitės į duomenų bazės administratorių.
Datos lentelės kūrimas programoje "Excel"
Programoje "Excel" galite sukurti datos lentelę ir nukopijuoti ją į naują duomenų modelio lentelę. Tai tikrai gana lengva padaryti ir suteikia jums daug lankstumo.
Kai programoje "Excel" sukuriate datos lentelę, pradedate nuo vieno stulpelio su nuosekliu datų diapazonu. Tada "Excel" darbalapyje galite sukurti papildomų stulpelių, pvz., Metai, Ketvirtis, Mėnuo, Finansiniai metai, Laikotarpis ir t. t., naudodami "Excel" formules, arba, nukopijavę lentelę į duomenų modelį, galite juos sukurti kaip apskaičiuojamuosius stulpelius. Papildomų datos stulpelių kūrimas "Power Pivot" aprašytas skyriuje Naujos datos stulpelių įtraukimas į datos lentelę toliau šiame straipsnyje.
Kaip: Datos lentelės kūrimas programoje "Excel" ir jos kopijavimas į duomenų modelį
-
Programos "Excel" tuščio darbalapio langelyje A1 įveskite stulpelio antraštės pavadinimą, kad nustatytumėte datų diapazoną. Paprastai tai buspanašus į datą, datą ir laiką arba datos raktą.
-
Langelyje A2 įveskite pradžios datą. Pavyzdžiui, 2010-01-01.
-
Spustelėkite užpildo rankenėlę ir vilkite ją žemyn iki eilutės numerio, kuriame yra pabaigos data. Pavyzdžiui, 2016-12-31.
-
Pažymėkite visas stulpelio Data eilutes (įskaitant antraštės pavadinimą langelyje A1).
-
Grupėje Stiliai spustelėkite Formatuoti kaip lentelę, tada pasirinkite stilių.
-
Dialogo lange Formatuoti kaip lentelę spustelėkite Gerai.
-
Nukopijuokite visas eilutes, įskaitant antraštę.
-
"Power Pivot" skirtuke Pagrindinis spustelėkite Įklijuoti.
-
Lauke Įklijuoti peržiūrą > Lentelės pavadinimas įveskite pavadinimą, pvz., Data arba Kalendorius. Palikite parinktį Naudoti pirmą eilutę kaip stulpelių antraštespažymėtą, tada spustelėkite Gerai.
Nauja datos lentelė (šiame pavyzdyje pavadinta Kalendorius) "Power Pivot" atrodo taip:
Pastaba: Taip pat galite sukurti susietą lentelę naudodami Įtraukti į duomenų modelį. Tačiau dėl to jūsų darbaknygė tampa pernelyg didelė, nes darbaknygėje yra dvi datų lentelės versijos; vieną programoje "Excel", o kitą "Power Pivot".
Pastaba: Pavadinimo data yra raktažodis "Power Pivot". Jei pavadinsite lentelę, kurią sukuriate naudodami "Power Pivot" datą, tada lentelės pavadinimą turėsite apskliausti viengubomis kabutėmis bet kurioje DAX formulėje, kuri nurodo ją argumente. Visi šiame straipsnyje pateikti vaizdų ir formulių pavyzdžiai nurodo datos lentelę, sukurtą naudojant "Power Pivot", pavadintą Kalendorius.
Dabar savo duomenų modelyje turite datų lentelę. Galite įtraukti naujų datos stulpelių, pvz., Metai, Mėnuo ir t. t., naudodami DAX.
Naujų datos stulpelių įtraukimas į datos lentelę
Datų lentelė su vienu datų stulpeliu, kuriame yra po vieną eilutę kiekvienai dienai kiekvienais metais, yra svarbi norint apibrėžti visas datų diapazono datas. Taip pat būtina sukurti ryšį tarp faktų lentelės ir datų lentelės. Tačiau tas atskiras datos stulpelis su viena eilute kiekvienai dienai nėra naudingas analizuojant pagal datas "PivotTable" arba "Power View" ataskaitoje. Norite, kad datų lentelėje būtų stulpelių, padedančių agreguoti diapazono ar datų grupės duomenis. Pavyzdžiui, galite norėti sumuoti pardavimo sumas pagal mėnesį ar ketvirtį arba galite sukurti matą, kuris apskaičiuoja metų augimą. Kiekvienu iš šių atvejų datos lentelei reikia metų, mėnesio ar ketvirčio stulpelių, leidžiančių agreguoti to laikotarpio duomenis.
Jei importavote datų lentelę iš sąryšinių duomenų šaltinio, joje jau gali būti skirtingų tipų datų stulpelių, kuriuos norite naudoti. Kai kuriais atvejais galite norėti modifikuoti kai kuriuos iš šių stulpelių arba sukurti papildomų datos stulpelių. Tai ypač aktualu, jei programoje "Excel" sukuriate savo datų lentelę ir nukopijuojate ją į duomenų modelį. Laimei, kurti naujus datos stulpelius "Power Pivot" yra gana paprasta naudojant DAX datos ir laiko funkcijas .
Patarimas: Jei dar nedirbote su DAX, puiki vieta pradėti mokytis – naudoti greito pasirengimo darbui funkciją: DAX pagrindus sužinokite per 30 minučių Office.com.
DAX datos ir laiko funkcijos
Jei kada nors dirbote su datos ir laiko funkcijomis "Excel" formulėse, tikriausiai būsite susipažinę su datos ir laiko funkcijomis. Nors šios funkcijos yra panašios į jų atitikmenis programoje "Excel", yra keletas svarbių skirtumų:
-
DAX datos ir laiko funkcijos naudoja datos ir laiko duomenų tipą.
-
Reikšmės iš stulpelio gali būti laikomos argumentu.
-
Jie gali būti naudojami datos reikšmėms grąžinti ir (arba) valdyti.
Šios funkcijos dažnai naudojamos kuriant pasirinktinius datos stulpelius datų lentelėje, todėl jas svarbu suprasti. Naudosime kelias šias funkcijas stulpeliams Year, Quarter, FiscalMonth ir t. t. kurti.
Pastaba: DAX datos ir laiko funkcijos skiriasi nuo laiko informacijos funkcijų. Sužinokite daugiau apie laiko informaciją "Power Pivot" programoje "Excel".
DAX apima šias datos ir laiko funkcijas:
Formulėse galite naudoti ir daug kitų DAX funkcijų. Pvz., daugelis čia aprašytų formulių naudoja matematines ir trigonometrines funkcijas , pvz., MOD ir TRUNC, logines funkcijas , pvz., IF, ir teksto funkcijas , pvz ., FORMAT . Daugiau informacijos apie kitas DAX funkcijas žr. šio straipsnio skyriuje Papildomi ištekliai .
Kalendorinių metų formulių pavyzdžiai
Toliau pateiktuose pavyzdžiuose aprašomos formulės, naudojamos kuriant papildomus stulpelius datų lentelėje Kalendorius. Vienas stulpelis, pavadintas Data, jau yra ir jame yra nuoseklių datų diapazonas nuo 2010-01-01 iki 2016-12-31.
Metai
=YEAR([data])
Šioje formulėje funkcija YEAR grąžina metus iš reikšmės, esančios stulpelyje Data. Kadangi reikšmė stulpelyje Data yra datos ir laiko duomenų tipo, funkcija YEAR žino, kaip iš jos grąžinti metus.
Mėnuo
=MONTH([data])
Šioje formulėje, panašiai kaip naudojant funkciją YEAR, galime tiesiog naudoti funkciją MONTH , kad grąžintumėte mėnesio reikšmę iš stulpelio Data.
Ketvirtis
=INT(([Mėnuo]+2)/3)
Šioje formulėje naudojame funkciją INT , kad grąžintumėme datos reikšmę kaip sveikasis skaičius. Funkcijos INT argumentas yra reikšmė iš stulpelio Mėnuo, pridėti 2, tada padalinti iš 3, kad gautumėte mūsų ketvirtį, 1–4.
Mėnesio pavadinimas
=FORMAT([data],"mmmm")
Šioje formulėje, norėdami gauti mėnesio pavadinimą, naudojame funkciją FORMAT , kad konvertuotume skaitinę reikšmę iš stulpelio Data į tekstą. Mes nurodome stulpelį Data kaip pirmąjį argumentą, tada formatą; mes norime, kad mūsų mėnesio pavadinimas rodų visus simbolius, todėl mes naudojame "mmmm". Mūsų rezultatas atrodo taip:
Jei norime grąžinti mėnesio pavadinimą, sutrumpintą trimis raidėmis, formato argumente naudosime "mmm".
Savaitės diena
=FORMAT([data],"ddd")
Šioje formulėje naudojame funkciją FORMAT, kad gautumėte dienos pavadinimą. Kadangi tiesiog norime sutrumpintos dienos pavadinimo, formato argumente nurodome "ddd".
„PivotTable“ pavyzdys
Jei turite datų laukų, pvz., Metai, Ketvirtis, Mėnuo ir t. t., galite juos naudoti "PivotTable" arba ataskaitoje. Pvz., toliau pateiktame paveikslėlyje rodomas laukas SalesAmount iš lentelės Pardavimo faktų reikšmės ir Metai ir Ketvirtis iš lentelės Kalendoriaus dimensija eilutės. SalesAmount yra agreguotas metų ir ketvirčio kontekstui.
Finansinių metų formulių pavyzdžiai
Finansiniai metai
=IF([Mėnuo]<= 6,[Metai],[Metai]+1)
Šiame pavyzdyje finansiniai metai prasideda liepos 1 d.
Nėra funkcijos, kuri išskleidžia finansinius metus iš datos reikšmės, nes finansinių metų pradžios ir pabaigos datos dažnai skiriasi nuo kalendorinių metų datų. Norėdami gauti finansinius metus, pirmiausia naudojame funkciją IF , kad patikrintumėte, ar Mėnesio reikšmė yra mažesnė arba lygi 6. Antrajame argumente, jei Mėnesio reikšmė yra mažesnė arba lygi 6, grąžinama reikšmė iš stulpelio Metai. Jei ne, tada grąžinkite reikšmę iš Year ir pridėkite 1.
Kitas finansinių metų pabaigos mėnesio reikšmės nurodymo būdas yra sukurti matą, kuris tiesiog nurodo mėnesį. Pvz., FYE:=6. Tada vietoj mėnesio numerio galite nurodyti mato pavadinimą. Pavyzdžiui, =IF([Mėnuo]<=[FYE],[Year],[Year]+1). Tai suteikia daugiau lankstumo nurodant finansinių metų pabaigos mėnesį keliose skirtingose formulėse.
Finansinis mėnuo
=IF([Mėnuo]<= 6, 6+[Mėnuo], [Mėnuo]- 6)
Šioje formulėje nurodome, ar [Month] reikšmė yra mažesnė arba lygi 6, tada paimkite 6 ir pridėkite reikšmę iš Mėnuo, kitu atveju atimkite 6 iš reikšmės iš [Month].
Finansinis ketvirtis
=INT(([FiscalMonth]+2)/3)
Formulė, kurią naudojame "FiscalQuarter", yra beveik tokia pati kaip ketvirtis mūsų kalendoriniais metais. Vienintelis skirtumas yra nurodyti [FiscalMonth], o ne [Month].
Šventės arba specialios datos
Galite įtraukti datos stulpelį, nurodantį, kad tam tikros datos yra šventės arba kita speciali data. Pavyzdžiui, galite norėti sumuoti Naujų metų dienos pardavimo sumas įtraukdami atostogų lauką į "PivotTable", kaip duomenų filtrą arba filtrą. Kitais atvejais galite neįtraukti šių datų į kitus datos stulpelius arba matą.
Įskaitant šventes ar specialias dienas yra gana paprasta. Programoje "Excel" galite sukurti lentelę, kurioje yra norimos įtraukti datos. Tada galite nukopijuoti arba naudoti įtraukti į duomenų modelį, kad įtrauktumėte jį į duomenų modelį kaip susietąją lentelę. Daugeliu atvejų nėra būtina sukurti ryšį tarp lentelės ir kalendoriaus lentelės. Bet kurios jį nurodančios formulės gali naudoti funkciją LOOKUPVALUE reikšmėms pateikti.
Toliau pateikiamas "Excel" sukurtos lentelės, kurioje yra į datos lentelę įtrauktinų laisvadienių, pavyzdys:
|
Data |
Šventės |
|---|---|
|
1/1/2010 |
Naujieji metai |
|
11/25/2010 |
Padėkos |
|
12/25/2010 |
Kalėdos |
|
2011 01 01 |
Naujieji metai |
|
11/24/2011 |
Padėkos |
|
12/25/2011 |
Kalėdos |
|
2012/1/1 |
Naujieji metai |
|
11/22/2012 |
Padėkos |
|
12/25/2012 |
Kalėdos |
|
1/1/2013 |
Naujieji metai |
|
11/28/2013 |
Padėkos |
|
12/25/2013 |
Kalėdos |
|
11/27/2014 |
Padėkos |
|
12/25/2014 |
Kalėdos |
|
1/1/2014 |
Naujieji metai |
|
11/27/2014 |
Padėkos |
|
12/25/2014 |
Kalėdos |
|
1/1/2015 |
Naujieji metai |
|
11/26/2014 |
Padėkos |
|
12/25/2015 |
Kalėdos |
|
1/1/2016 |
Naujieji metai |
|
11/24/2016 |
Padėkos |
|
12/25/2016 |
Kalėdos |
Datos lentelėje sukuriame stulpelį, pavadintą Šventės , ir naudojame tokią formulę:
=LOOKUPVALUE(Šventės[Šventės],Šventės[data],Kalendorius[data])
Apžvelkime šią formulę atidžiau.
Naudojame funkciją LOOKUPVALUE, kad gautumėte reikšmes iš stulpelio Šventės lentelėje Šventės. Pirmajame argumente nurodome stulpelį, kuriame bus mūsų rezultato reikšmė. Švenčių lentelės stulpelį Šventės nurodome, nes tai yra reikšmė, kurią norime grąžinti.
=LOOKUPVALUE(Šventės[Šventės],Šventės[data],Kalendorius[data])
Tada nurodome antrąjį argumentą, ieškos stulpelį, kuriame yra datos, kurių norime ieškoti. Lentelės Šventės stulpelį Data nurodome taip:
=LOOKUPVALUE(Šventės[Šventės],Šventės[data],Kalendorius[data])
Galiausiai, nurodome stulpelį, esantį lentelėje Kalendorius , kuriame yra datos, kurių norime ieškoti lentelėje Šventės . Tai, žinoma, yra lentelės Kalendorius stulpelis Data.
=LOOKUPVALUE(Šventės[Šventės],Šventės[data],Kalendorius[data])
Stulpelis Šventės pateiks kiekvienos eilutės, kurios datos reikšmė atitinka datą lentelėje Šventės, švenčių pavadinimą.
Pasirinktinis kalendorius – trylika keturių savaičių laikotarpių
Kai kurios organizacijos, pvz., mažmeninės prekybos ar maisto paslaugos, dažnai praneša apie skirtingus laikotarpius, pvz., trylika keturių savaičių laikotarpių. Su trylika keturių savaičių laikotarpio kalendorių, kiekvienas laikotarpis yra 28 dienos; Todėl kiekvieną laikotarpį sudaro keturi pirmadieniai, keturi antradieniai, keturi trečiadieniai ir t. t. Kiekvienas laikotarpis apima tą patį dienų skaičių ir paprastai šventės bus to paties laikotarpio kiekvienais metais. Galite pasirinkti pradėti laikotarpį bet kurią savaitės dieną. Taip pat, kaip naudojant kalendoriaus ar finansinių metų datas, galite naudoti DAX, kad sukurtumėte papildomų stulpelių su pasirinktomis datomis.
Toliau pateiktuose pavyzdžiuose pirmasis visas laikotarpis prasideda pirmąjį finansinių metų sekmadienį. Šiuo atveju finansiniai metai prasideda 2010-07-01.
Savaitė
Ši reikšmė nurodo savaitės numerį, prasidedančius pirmąja visa finansinių metų savaite. Šiame pavyzdyje pirmoji visa savaitė prasideda sekmadienį, todėl pirmoji visa savaitė pirmaisiais finansiniais metais lentelėje Kalendorius iš tikrųjų prasideda 2010-07-04 ir tęsiasi paskutinę visą savaitę lentelėje Kalendorius. Nors ši reikšmė nėra visa, kas naudinga atliekant analizę, būtina apskaičiuoti, kad ją būtų galima naudoti kitose 28 dienų laikotarpio formulėse.
=INT([data]-40356)/7)
Apžvelkime šią formulę atidžiau.
Pirmiausia, mes sukuriame formulę, kuri pateikia reikšmes iš stulpelio Data kaip sveikasis skaičius, pvz.:
=INT([data])
Tada norime ieškoti pirmojo pirmųjų finansinių metų sekmadienio. Matome, kad tai 2010-07-04.
Dabar atimkite 40356 (kuris yra sveikasis skaičius 2010-06-27, paskutinis sekmadienis iš ankstesnių finansinių metų) iš tos reikšmės, kad gautumėte dienų skaičių nuo dienų pradžios lentelėje Kalendorius, pvz.:
=INT([data]-40356)
Tada padalinkite rezultatą iš 7 (dienos per savaitę), kaip čia:
=INT(([data]-40356)/7)
Rezultatas atrodo taip:
Taškas
Šio pasirinktinio kalendoriaus laikotarpį sudaro 28 dienos ir jis visada prasidės sekmadienį. Šiame stulpelyje bus pateiktas laikotarpio, prasidedančio pirmųjų finansinių metų pirmąjį sekmadienį, skaičius.
=INT(([Savaitė]+3)/4)
Apžvelkime šią formulę atidžiau.
Pirmiausia, mes sukuriame formulę, kuri pateikia reikšmę iš stulpelio Savaitė kaip sveikasis skaičius, pvz.:
=INT([Savaitė])
Tada į šią reikšmę įtraukite 3, pvz.:
=INT([Savaitė]+3)
Tada padalinkite rezultatą iš 4, kaip čia:
=INT(([Savaitė]+3)/4)
Rezultatas atrodo taip:
Laikotarpis finansiniai metai
Ši reikšmė grąžina laikotarpio finansinius metus.
=INT(([Laikotarpis]+12)/13)+2008
Apžvelkime šią formulę atidžiau.
Pirmiausia, mes sukuriame formulę, kuri pateikia reikšmę iš periodo ir prideda 12:
= ([Laikotarpis]+12)
Rezultatą padaliname iš 13, nes finansiniais metais yra trylika 28 dienų laikotarpių:
=(([Laikotarpis]+12)/13)
Įtraukiame 2010 m., nes tai pirmieji metai lentelėje:
=(([Laikotarpis]+12)/13)+2010
Galiausiai naudojame funkciją INT, kad pašalintume bet kokią rezultato dalį ir grąžintumėte sveikąjį skaičių, padalintą iš 13, kaip čia:
=INT(([Laikotarpis]+12)/13)+2010
Rezultatas atrodo taip:
Laikotarpis finansiniais metais
Ši reikšmė grąžina laikotarpio numerį nuo 1 iki 13, pradedant pirmuoju visu laikotarpiu (pradedant nuo sekmadienio) kiekvienais finansiniais metais.
=IF(MOD([Taškas],13), MOD([Taškas],13),13)
Ši formulė yra šiek tiek sudėtingesnė, todėl pirmiausia aprašysime ją kalba, kuria geriau suprantame. Ši formulė nurodo, padalinkite reikšmę iš [Taškas] iš 13, kad gautumėte metų laikotarpio skaičių (1–13). Jei tas skaičius yra 0, grąžinkite 13.
Pirmiausia sukuriame formulę, kuri grąžina liekaną nuo Laikotarpio iki 13. Mod ( matematines ir trigonometrines funkcijas) galime naudoti taip:
=MOD([Taškas],13)
Tai daugeliu atvejų pateikia mums pageidaujamą rezultatą, išskyrus atvejus, kai laikotarpio reikšmė yra 0, nes tos datos nepatenka į pirmuosius finansinius metus, pvz., pirmose penkiose mūsų kalendoriaus datų lentelės pavyzdžio dienose. Galime tuo pasirūpinti naudodami FUNKCIJĄ IF. Jei rezultatas bus 0, grąžinsime 13, pvz.:
=IF(MOD([Taškas],13),MOD([Taškas],13),13)
Rezultatas atrodo taip:
„PivotTable“ pavyzdys
Toliau pateiktame paveikslėlyje rodoma "PivotTable" su lauku "SalesAmount" iš lentelės Pardavimo faktų reikšmės ir PeriodFiscalYear ir PeriodInFiscalYear laukų kalendoriaus datos dimensijų lentelėje EILUTĖS. SalesAmount yra agreguotas pagal finansinius metus ir 28 dienų laikotarpį finansiniais metais.
Ryšiai
Sukūrę datų lentelę duomenų modelyje, norėdami pradėti naršyti duomenis "PivotTable" ir ataskaitose ir agreguoti duomenis pagal datos dimensijų lentelės stulpelius, turite sukurti ryšį tarp faktų lentelės su operacijų duomenimis ir datos lentelės.
Kadangi jums reikia sukurti ryšį pagal datas, norėsite įsitikinti, kad sukūrėte šį ryšį tarp stulpelių, kurių reikšmės yra datos ir laiko (datos) duomenų tipo.
Kiekvienos faktų lentelės datos reikšmės susijęs peržvalgos stulpelis datų lentelėje turi turėti atitinkančias reikšmes. Pavyzdžiui, lentelės Pardavimo faktas eilutė (operacijos įrašas), kurios reikšmė stulpelyje DateKey yra 2012-08-15 12:00, turi turėti atitinkamą reikšmę stulpelyje Susijusi data lentelėje data (pavadinta Kalendorius). Tai yra viena iš svarbiausių priežasčių, kodėl norite, kad datų lentelės datų stulpelis apimtų vientisą datų diapazoną, kuris apima bet kokią galimą datą jūsų faktų lentelėje.
Pastaba: Nors kiekvienoje lentelėje datos stulpelis turi būti to paties duomenų tipo (Data), kiekvieno stulpelio formatas nesvarbus.
Pastaba: Jei "Power Pivot" neleis sukurti ryšių tarp šių dviejų lentelių, datos laukuose data ir laikas gali būti netikslūs. Atsižvelgiant į stulpelio formatavimą, reikšmės gali atrodyti taip pat, tačiau gali būti saugomos skirtingai. Skaitykite daugiau apie darbą su laiku.
Pastaba: Stenkitės nenaudoti sveikųjų skaičių pakaitalų raktų ryšiuose. Kai importuojate duomenis iš sąryšinio duomenų šaltinio, dažnai datos ir laiko stulpeliai vaizduojami pakaitiniu raktu, kuris yra sveikasis skaičius, naudojamas unikaliai datai nurodyti. Naudodami "Power Pivot" turėtumėte vengti ryšių kurdami ryšius naudodami sveikųjų skaičių datos / laiko klavišus, o vietoj to naudokite stulpelius, kuriuose yra unikalių reikšmių su datos duomenų tipu. Nors pakaitinių raktų naudojimas laikomas geriausia praktika tradiciniuose duomenų sandėliuose, "Power Pivot" sveikųjų skaičių raktai nebūtini ir gali būti sunku grupuoti reikšmes "PivotTable" pagal skirtingus datos laikotarpius.
Jei bandydami sukurti ryšį gaunate tipo neatitikimo klaidą, tikėtina, kad faktų lentelės stulpelis nėra datos duomenų tipo. Taip gali nutikti, kai "Power Pivot" negali automatiškai konvertuoti ne datos (paprastai teksto duomenų tipo) į datos duomenų tipą. Vis tiek galite naudoti stulpelį faktų lentelėje, bet turėsite konvertuoti duomenis naudodami DAX formulę naujame apskaičiuotame stulpelyje. Žr . Teksto duomenų tipo datų konvertavimas į datos duomenų tipą toliau priede.
Keli ryšiai
Kai kuriais atvejais gali reikėti sukurti kelis ryšius arba sukurti kelias datos lenteles. Pavyzdžiui, jei pardavimo faktų lentelėje yra keli datos laukai, pvz., DateKey, ShipDate ir ReturnDate, jie visi gali turėti ryšius su datos lauku kalendoriaus datos lentelėje, bet tik vienas iš jų gali būti aktyvus ryšys. Šiuo atveju DateKey nurodo operacijos datą, todėl ir svarbiausią datą, todėl geriausia būtų būti aktyviu ryšiu. Kiti turi neaktyvius ryšius.
Ši "PivotTable" apskaičiuoja bendrą pardavimą pagal finansinius metus ir finansinį ketvirtį. Matas, pavadintas Total Sales, su formule Total Sales:=SUM([SalesAmount]), įdedamas į VALUES, o laukai FiscalYear ir FiscalQuarter iš lentelės Kalendorius data įdedami į ROWS.
Ši paprasta "PivotTable" veikia tinkamai, nes mes norime sumuoti bendrą pardavimą pagal operacijosdatą DateKey. Mūsų matas Total Sales naudoja datas, nurodytas DateKey, ir yra sumuojamas iš finansinių metų ir finansinio ketvirčio, nes lentelėje Pardavimas yra ryšys tarp DateKey ir stulpelio Data lentelėje Kalendoriaus data.
Neaktyvūs ryšiai
Tačiau ką daryti, jei norėjome sumuoti savo bendrą pardavimą ne pagal operacijos datą, bet pagal siuntimo datą? Reikia ryšio tarp lentelės Pardavimas stulpelio Išsiuntimo data ir stulpelio Data lentelėje Kalendorius. Jei šio ryšio nesukursime, agregavimų rezultatai visada bus pagrįsti operacijos data. Tačiau mes galime turėti kelis ryšius, nors tik vienas gali būti aktyvus, o operacijos data yra svarbiausia, ji gauna aktyvų ryšį su lentele Kalendorius.
Šiuo atveju ShipDate yra neaktyvus ryšys, todėl bet kuri matų formulė, sukurta agreguoti duomenis pagal siuntimo datas, turi nurodyti neaktyvų ryšį naudojant funkciją USERELATIONSHIP .
Pavyzdžiui, kadangi lentelės Pardavimas stulpelis Išsiuntimo_data ir lentelės Kalendorius stulpelis Data yra neaktyvus, galime sukurti matą, kuris sumuoja pardavimo sumą pagal išsiuntimo datą. Naudojame tokią formulę, kad nurodytumėte naudoti norimą ryšį:
Bendras pardavimas pagal siuntimo datą:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))
Šioje formulėje tiesiog nurodoma: "SalesAmount" sumos skaičiavimas, bet filtravimas naudojant ryšį tarp lentelės Pardavimas stulpelio Išsiuntimo data ir stulpelio Data lentelėje Kalendorius.
Dabar, jei sukursime "PivotTable" ir matą Total Sales by Ship Date į reikšmes, o finansinius metus ir finansinį ketvirtį į eilutes matysime tą pačią bendrąją sumą, tačiau visos kitos finansinių metų ir finansinio ketvirčio sumos yra skirtingos, nes jos pagrįstos siuntimo data, o ne operacijos data.
Naudodami neaktyvius ryšius galite naudoti tik vieną datos lentelę, bet tam reikia, kad visi matai (pvz., Total Sales by Ship Date) nurodytų neaktyvų ryšį formulėje. Yra dar viena alternatyva, t. y. naudoti kelias datos lenteles.
Kelios datų lentelės
Kitas būdas dirbti su keliais datų stulpeliais faktų lentelėje yra sukurti kelias datų lenteles ir sukurti atskirus aktyvius ryšius tarp jų. Pažvelkime į lentelės Pardavimas pavyzdį dar kartą. Turime tris stulpelius su datomis, pagal kurias galbūt norėsite agreguoti duomenis:
-
A DateKey su kiekvienos operacijos pardavimo data.
-
Siuntimo data su data ir laiku, kai parduotos prekės buvo išsiųstos klientui.
-
Grąžinimo data su data ir laiku, kai buvo gautas vienas ar daugiau elementų.
Atminkite, kad svarbiausia yra laukas DateKey su operacijos data. Mes atliksime daugumą savo agregavimų pagal šias datas, todėl tikrai norime ryšio tarp jo ir stulpelio Data lentelėje Kalendorius. Jei nenorime sukurti neaktyvių ryšių tarp ShipDate ir ReturnDate bei datos lauko lentelėje Kalendorius, todėl reikia specialių matų formulių, galime sukurti papildomas datų lenteles siuntimo datai ir grąžinimo datai. Tada galime sukurti aktyvius jų ryšius.
Šiame pavyzdyje sukūrėme kitą datų lentelę pavadinimu ShipCalendar. Tai, žinoma, taip pat reiškia papildomų datos stulpelių kūrimą, o šie datos stulpeliai yra kitoje datų lentelėje, todėl norime juos pavadinti taip, kad jie būtų atskirti nuo tų pačių stulpelių lentelėje Kalendorius. Pavyzdžiui, sukūrėme stulpelius, pavadintus Išsiuntimo metai, Išsiuntimo mėnesis, Išsiuntimoquarter ir t. t.
Jei sukursime "PivotTable" ir matą Total Sales įtrauksime į VALUES, o ShipFiscalYear ir ShipFiscalQuarter – eilutėse, matysime tuos pačius rezultatus, kuriuos pamatėme kurdami neaktyvų ryšį ir specialų apskaičiuotąjį lauką Total Sales by ShipFiscalQuarter.
Kiekvieną iš šių metodų reikia atidžiai apsvarstyti. Naudojant kelis ryšius su viena datų lentele, gali tekti sukurti specialias priemones, kurios perduoda neaktyvius ryšius, naudodamos funkciją USERELATIONSHIP. Kita vertus, sukurti kelias datos lenteles gali būti painu laukų sąraše, o duomenų modelyje yra daugiau lentelių, todėl reikės daugiau atminties. Išbandykite tai, kas jums geriausiai tinka.
Date Table property
Ypatybė Date Table nustato metaduomenis, būtinus, kad Time-Intelligence funkcijos, pvz., TOTALYTD, PREVIOUSMONTH ir DATESBETWEEN, veiktų tinkamai. Kai skaičiavimas vykdomas naudojant vieną iš šių funkcijų, "Power Pivot" formulių modulis žino, kur eiti ir gauti reikalingas datas.
Įspėjimas: Jei ši ypatybė nenustatyta, matai, naudojantis DAX Time-Intelligence funkcijos gali pateikti neteisingus rezultatus.
Kai nustatote ypatybę Datos lentelė, joje nurodote datos lentelę ir datos stulpelį, kurio duomenų tipas yra Data (data ir laikas).
Kaip: Ypatybės Datų lentelė nustatymas
-
"PowerPivot" lange pasirinkite lentelę Kalendorius .
-
Skirtuke Dizainas spustelėkite Žymėti kaip datos lentelę.
-
Dialogo lange Žymėti kaip datos lentelę pasirinkite stulpelį su unikaliomis reikšmėmis ir datos duomenų tipą.
Darbas su laiku
Visos datos reikšmės su datos duomenų tipu programose "Excel" arba "SQL Server" iš tikrųjų yra skaičius. Į tą skaičių įtraukti skaitmenys, nurodantys laiką. Daugeliu atvejų, kad laikas kiekvienai eilutei yra vidurnaktis. Pavyzdžiui, jei pardavimo faktų lentelės laukas DateTimeKey turi reikšmes, pvz., 10/19/2010 12:00:00 AM, tai reiškia, kad reikšmės yra iki dienos tikslumo lygio. Jei lauko DateTimeKey reikšmės apima laiką, pvz., 10/19/2010 8:44:00 AM, tai reiškia, kad reikšmės yra minučio tikslumo lygio. Reikšmės taip pat gali būti iki valandos lygio tikslumo arba net sekundžių tikslumo lygio. Laiko reikšmės tikslumo lygis turės didelės įtakos datų lentelės kūrimui ir jos bei faktų lentelės ryšiams.
Turite nustatyti, ar duomenis agreguosite iki dienos tikslumo lygio, ar iki laiko tikslumo lygio. Kitaip tariant, galite naudoti datos lentelės stulpelius, pvz., Rytas, Popietė arba Valanda kaip laiko datos laukus "PivotTable" srityse Eilutė, Stulpelis arba Filtruoti.
Pastaba: Dienos yra mažiausias laiko vienetas, su kuriuo gali veikti DAX laiko informacijos funkcijos. Jei jums nereikia dirbti su laiko reikšmėmis, turėtumėte sumažinti duomenų tikslumą, kad dienų skaičius būtų naudojamas kaip minimalus vienetas.
Jei ketinate agreguoti duomenis iki laiko lygio, datos lentelei reikės datos stulpelio su įtrauktuoju laiku. Tiesą sakant, reikės datos stulpelio su viena eilute kas valandą, o gal net kas minutę, kiekvieną dieną, už kiekvienais metais datų diapazone. Taip yra todėl, kad norėdami sukurti ryšį tarp faktų lentelės stulpelio DateTimeKey ir datos stulpelio datų lentelėje, turite turėti atitinkančias reikšmes. Kaip jūs galite įsivaizduoti, jei įtrauksite daug metų, tai gali padaryti labai didelis datos stalo.
Tačiau daugeliu atvejų duomenis norite agreguoti tik iki dienos. Kitaip tariant, kaip "PivotTable" eilučių, stulpelių arba filtravimo sričių laukus naudosite tokius stulpelius kaip Metai, Mėnuo, Savaitė arba Savaitės diena. Šiuo atveju datos lentelės datų stulpelyje turi būti tik viena eilutė kiekvienai metų dienai, kaip aprašyta anksčiau.
Jei datos stulpelyje nurodytas tikslumo lygis, bet agreguosite tik iki dienos lygio, norėdami sukurti ryšį tarp faktų lentelės ir datos lentelės, gali tekti modifikuoti faktų lentelę sukuriant naują stulpelį, kuris sutrumpina datos stulpelio reikšmes iki dienos reikšmės. Kitaip tariant, konvertuokite reikšmę, pvz., 2010-10-19 08:44:00į2010-10-19 12:00:00. Tada galite sukurti ryšį tarp šio naujo stulpelio ir datos stulpelio datų lentelėje, nes reikšmės sutampa.
Pažvelkime į pavyzdį. Šiame paveikslėlyje pavaizduotas stulpelis DateTimeKey lentelėje Pardavimo faktas. Visi šios lentelės duomenų agregavimų duomenys turi būti tik iki dienos lygio, naudojant lentelės Kalendorius data stulpelius, pvz., Metai, Mėnuo, Ketvirtis ir t. t. Į reikšmę įtrauktas laikas nėra susijęs, tik faktinė data.
Mums nereikia analizuoti šių duomenų pagal laiko lygį, todėl mums nereikia kalendoriaus datos lentelės stulpelio Data, kad įtrauktume po vieną eilutę kas valandą ir kiekvieną kiekvienos dienos minutę kiekvienais metais. Taigi, mūsų datų lentelės stulpelis Data atrodo taip:
Norėdami sukurti ryšį tarp stulpelio DateTimeKey lentelėje Pardavimas ir stulpelio Data lentelėje Kalendorius, galime sukurti naują apskaičiuojamąjį stulpelį lentelėje Pardavimo faktas ir naudoti funkciją TRUNC datos ir laiko reikšmei stulpelyje DateTimeKey sutrumpinti į datos reikšmę, atitinkančią reikšmes stulpelyje Data lentelėje Kalendorius. Mūsų formulė atrodo taip:
=TRUNC([DateTimeKey],0)
Tai suteikia mums naują stulpelį (mes pavadinome DateKey) su data iš stulpelio DateTimeKey, o kiekvienos eilutės laikas – 12:00:00:
Dabar galime sukurti ryšį tarp šio naujo stulpelio (DateKey) ir stulpelio Data lentelėje Kalendorius.
Taip pat galime sukurti apskaičiuotąjį stulpelį lentelėje Pardavimas, kuris sumažina laiko tikslumą stulpelyje DateTimeKey iki valandos tikslumo lygio. Šiuo atveju funkcija TRUNC neveiks, tačiau vis tiek galime naudoti kitas DAX datos ir laiko funkcijas, kad išskleistume ir iš naujo sujungtumėte naują reikšmę iki valandos tikslumo lygio. Galime naudoti tokią formulę:
= DATE (YEAR([DateTimeKey]), MONTH([DateTimeKey]), DAY([DateTimeKey]) ) + TIME (HOUR([DateTimeKey]), 0, 0)
Mūsų naujas stulpelis atrodo taip:
Jei mūsų datų lentelės stulpelis Data turi reikšmes iki valandos tikslumo lygio, galime sukurti ryšį tarp jų.
Datos padarydamos labiau naudojamas
Daugelis datos stulpelių, kuriuos sukuriate datų lentelėje, yra būtini kitiems laukams, bet iš tikrųjų nėra visi naudingi atliekant analizę. Pavyzdžiui, lentelės Pardavimas laukas DateKey, kurį nurodėme ir rodome šiame straipsnyje, yra svarbus, nes kiekvienai operacijai ta operacija įrašoma kaip pasitaikanti konkrečią datą ir laiką. Tačiau analizės ir ataskaitų požiūriu ne viskas naudinga, nes negalime jo naudoti kaip eilutės, stulpelio ar filtro lauko "Pivot" lentelėje arba ataskaitoje.
Taip pat mūsų pavyzdyje stulpelis Data lentelėje Kalendorius yra labai naudingas ir kritinis, tačiau negalite jo naudoti kaip "PivotTable" dimensijos.
Kad lentelės ir stulpeliai jose būtų kuo naudingesni, o "PivotTable" arba "Power View" ataskaitos laukų sąrašus būtų lengviau naršyti, svarbu paslėpti nereikalingus stulpelius kliento įrankiuose. Taip pat galite paslėpti tam tikras lenteles. Ankstesnėje lentelėje Šventės yra švenčių datos, svarbios tam tikriems kalendoriaus lentelės stulpeliams, tačiau negalite naudoti stulpelių Data ir Šventės lentelėje patys kaip "PivotTable" laukų. Čia dar kartą, kad laukų sąrašus būtų lengviau naršyti, galite paslėpti visą lentelę Šventės.
Kitas svarbus darbo su datomis aspektas yra vardų suteikimo konvencijos. "Power Pivot" galite pavadinti lenteles ir stulpelius taip, kaip norite. Tačiau turėkite omenyje, ypač jei darbaknygę bendrinsite su kitais vartotojais, gera pavadinimų suteikimo konvencija leis lengviau identifikuoti lenteles ir datas ne tik laukų sąrašuose, bet ir "Power Pivot" ir DAX formulėse.
Duomenų modelyje sukūrę datų lentelę, galite pradėti kurti priemones, kurios padės išnaudoti visas duomenų naudojimo rezultatus. Kai kurie iš jų gali būti tokie paprasti, kaip dabartinių metų pardavimo sumų sumavimas, o kiti gali būti sudėtingesni, kai reikia filtruoti pagal konkretų unikalių datų diapazoną. Sužinokite daugiau "Power Pivot" matuose ir laiko informacijos funkcijose.
Priedas
Teksto duomenų tipo datų konvertavimas į datos duomenų tipą
Kai kuriais atvejais faktų lentelėje su operacijų duomenimis gali būti teksto duomenų tipo datų. Tai yra data, kuri rodoma kaip 2012-12-04T11:47:09, iš tikrųjų nėra data arba bent jau nėra "Power Pivot" duomenų tipas, kurį gali suprasti. Tai tikrai tik tekstas, kuris skaito kaip datą. Norint sukurti ryšį tarp faktų lentelės datos stulpelio ir datos stulpelio datų lentelėje, abu stulpeliai turi būti datos duomenų tipo.
Paprastai, kai bandote pakeisti datų stulpelio, kuris yra teksto duomenų tipas, duomenų tipą į datos duomenų tipą, "Power Pivot" gali interpretuoti datas ir automatiškai konvertuoti jas į tikros datos duomenų tipą. Jei "Power Pivot" negali atlikti duomenų tipo konvertavimo, gausite tipo neatitikimo klaidą.
Tačiau vis tiek galite konvertuoti datas į tikrą datos duomenų tipą. Galite sukurti naują apskaičiuojamąjį stulpelį ir naudoti DAX formulę, kad iš teksto eilučių išanalizuotumėte metus, mėnesį, dieną, laiką ir t. t., tada vėl jį sujungti taip, kaip "Power Pivot" gali skaityti kaip teisingą datą.
Šiame pavyzdyje importavome faktų lentelę, pavadintą Pardavimas į "Power Pivot". Jame yra stulpelis, pavadintas DateTime. Reikšmės rodomos taip:
Jei pažvelgsime į Duomenų tipas formatavimo grupėje "Power Pivot" skirtukas Pagrindinis, matome, kad tai yra teksto duomenų tipas.
Negalime sukurti ryšio tarp stulpelio DateTime ir stulpelio Date, esančio mūsų datų lentelėje, nes nesutampa duomenų tipai. Jei bandysime pakeisti duomenų tipą į datą, gauname tipo neatitikimo klaidą:
Šiuo atveju "Power Pivot" negalėjo konvertuoti duomenų tipo iš teksto į datą. Vis dar galime naudoti šį stulpelį, tačiau, kad paverstume jį tikruoju datos duomenų tipu, turime sukurti naują stulpelį, kuris analizuoja tekstą ir iš naujo sukuria jį į reikšmę "Power Pivot", kad būtų duomenų tipas Data.
Atsiminkite anksčiau šiame straipsnyje esančią sekciją Darbas su laiku; išskyrus atvejus, kai būtina atlikti jūsų analizę, kad būtų nustatytas dienos laiko tikslumas, turite konvertuoti faktų lentelės datas į dienos tikslumą. Turėdami tai omenyje, norime, kad reikšmės mūsų naujame stulpelyje būtų dienos tikslumo lygyje (išskyrus laiką). Mes abu galime konvertuoti stulpelio DateTime reikšmes į datos duomenų tipą ir pašalinti laiko lygį pagal šią formulę:
=DATE(LEFT([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2))
Tai suteikia mums naują stulpelį (šiuo atveju – Data). "Power Pivot" netgi aptinka reikšmes kaip datas ir automatiškai nustato duomenų tipą į Data.
Jei norime išlaikyti laiko tikslumą, paprasčiausiai išplečiame formulę, kad įtrauktume valandas, minutes ir sekundes.
=DATE(LEFT([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2)) +
TIME(MID([DateTime],12,2), MID([DateTime],15,2), MID([DateTime],18,2))
Dabar, kai turime datos duomenų tipo stulpelį, galime sukurti ryšį tarp jo ir datos stulpelio datos.
Papildomi ištekliai
Greitasis pasirengimas darbui: DAX pagrindai per 30 minučių
