Razumevanje in ustvarjanje datumskih tabel v dodatku Power Pivot v programu Excel

Ustvarjanje datumski tabeli v Excel

V tabeli z datumi lahko Excel in jo nato kopirate v novo tabelo v podatkovnem modelu. To je res preprosto in vam omogoča veliko prilagodljivosti.

Ko v tabeli z datumi Excel, začnete z enim stolpcem z neprekinjenim obsegom datumov. Nato lahko ustvarite dodatne stolpce, kot so Leto, Četrtletje, Mesec, Poslovno leto, Obdobje itd. na delovnem listu Excel s formulami Excel ali, ko kopirate tabelo v podatkovni model, jih lahko ustvarite kot izračunane stolpce. Ustvarjanje dodatnih stolpcev z datumi v dodatku Power Pivot je opisano v razdelku Dodajanje novih stolpcev z datumom v tabelo v nadaljevanju tega članka.

Adding new date columns to the date table

Datumska tabela z enim stolpcem z datumom, ki ima eno vrstico za vsak dan za vsako leto, je pomembna za določanje vseh datumov v datumnem obsegu. Ustvariti je treba tudi relacijo med tabelo dejstev in tabelo z datumi. Toda ta stolpec z enim datumom z eno vrstico za vsak dan ni uporaben pri analizi po datumih v vrtilni tabeli ali poročilu Power View. Želite, da vaša datumska tabela vključuje stolpce, s pomočjo katerega lahko združite podatke za obseg ali skupino datumov. Morda želite na primer sešteti zneske prodaje po mesecu ali četrtletju ali pa ustvarite mero za izračun večletnega rasti. V vsakem od teh primerov so v vaši datumski tabeli stolpci za leto, mesec ali četrtletje, ki omogočajo združevanje podatkov za to obdobje.

Če ste uvozili datumsko tabelo iz relacijskega vira podatkov, morda že vključuje različne vrste stolpcev z datumi, ki jih želite. V nekaterih primerih boste morda želeli spremeniti nekatere od teh stolpcev ali ustvariti dodatne stolpce z datumi. To še posebej velja, če ustvarite svojo datumsko tabelo v Excel in jo kopirate v podatkovni model. Ustvarjanje novih stolpcev z datumi v dodatku Power Pivot je na srečo preprosto s funkcijami za datum in čas v dodatku DAX.

Funkcije datuma in časa v funkciji DAX

Če ste že kdaj delali s funkcijami za datum in čas v Excel formulah, boste verjetno že kdaj delali s funkcijami za datum in čas. Čeprav so te funkcije podobne funkcijam v drugih Excel, obstajajo nekatere pomembne razlike:

• Funkcije datuma in časa DAX uporabljajo vrsto podatkov datetime.

• Vrednosti iz stolpca lahko vzeli kot argument.

• Uporabite jih lahko za vračanje in/ali spreminjanje datumskih vrednosti.

Te funkcije se pogosto uporabljajo pri ustvarjanju stolpcev z datumi po meri v datumski tabeli, zato jih morate razumeti. S temi funkcijami bomo ustvarili stolpce za leto, četrtletje, fiskalnomesece in tako naprej.

DAX vključuje te funkcije za datum in čas:

• DATE

• DATEVALUE

• NASLEDNJI DAN

• EDATE

• EOMONTH

• URA

• MINUTA

• MESEC

• NOW

• SEKUNDA

• TIME

• TIMEVALUE

• DANES

• WEEKDAY

• WEEKNUM

• YEAR

• YEARFRAC

V formulah lahko uporabite tudi številne druge funkcije DAX. Veliko formul, ki so opisane tukaj, na primer uporablja matematične in trigonometrične funkcije, kot sta MOD in TRUNC, logične funkcije,kot sta IF in Besedilne funkcije, kot sta FORMAT. Če želite več informacij o drugih funkcijah dax, glejte razdelek »Dodatni viri« v nadaljevanju tega članka.

Primeri formul za koledarsko leto

Ti primeri opisujejo formule, ki se uporabljajo za ustvarjanje dodatnih stolpcev v datumski tabeli z imenom »Koledar«. En stolpec z imenom Datum že obstaja in vsebuje neprekinjen obseg datumov od 1. 1. 2010 do 31. 12. 2016.

Ime meseca

=FORMAT([datum],"mmmm")

Če želite dobiti ime meseca v tej formuli, s funkcijo FORMAT pretvorite številsko vrednost iz stolpca »Datum« v besedilo. Stolpec Datum smo določili kot prvi argument in nato za obliko zapisa; želimo, da ime meseca prikaže vse znake, zato smo uporabili »mmmm«. Naš rezultat je videti tako:

Če želimo, da je ime meseca okrajšano s tremi črkami, v argumentu oblike uporabite »mmm«.

Dan v tednu

=FORMAT([datum],"ddd")

V tej formuli smo uporabili funkcijo FORMAT, da bi dobili ime dneva. Ker želimo le okrajšano ime dneva, v argumentu oblike določimo »ddd«.

Vzorčna vrtilna tabela

Ko imate polja za datume, kot so Leto, Četrtletje, Mesec itd., jih lahko uporabite v vrtilni tabeli ali poročilu. Na spodnji sliki je na primer prikazano polje »ZnesekProdaje« iz tabele »Dejstvo prodaje« v VREDNOSTIh ter »Leto« in »Četrtletje« iz tabele dimenzije »Koledar« v vrsticah. Znesekprodaje je združen za kontekst leta in četrtletja.

Primeri formul za poslovno leto

Prazniki ali posebni datumi

Morda boste želeli vključiti stolpec z datumom, ki označuje, da so nekateri datumi prazniki ali drugi posebni datumi. Morda boste želeli sešteti skupne vsote prodaje za dan nova leta tako, da dodate polje »Praznik« v vrtilno tabelo kot razčlenjevalnik ali filter. V drugih primerih boste morda želeli izključiti te datume iz drugih stolpcev z datumi ali v meri.

Vse to je preprosto, vključno s prazniki ali posebnimi dnevi. V tabeli z datumi Excel jih želite vključiti, lahko ustvarite tabelo. Nato lahko kopirate ali uporabite funkcijo Dodaj v podatkovni model, da jo dodate v podatkovni model kot povezano tabelo. V večini primerov vam ni treba ustvariti relacije med tabelo in tabelo Koledar. Vse formule, ki se sklicujejo nanje, lahko za vrnitev vrednosti uporabijo funkcijo LOOKUPVALUE.

Spodaj je primer tabele, ustvarjene v Excel ki vključuje praznike, ki jih želite dodati v tabelo z datumi:

V datumski tabeli ustvarimo stolpec z imenom Praznik in uporabimo formulo, podobno tej:

=LOOKUPVALUE(prazniki[prazniki],prazniki[datum],koledar[datum])

To formulo si o bomo še natančneje ošteli.

S funkcijo LOOKUPVALUE pridobite vrednosti iz stolpca Prazniki v tabeli Prazniki. V prvem argumentu določimo stolpec, v katerem bo vrednost rezultata. V tabeli Prazniki smo določili stolpec Prazniki, ker je to vrednost, ki jo želimo vrnejo.

=LOOKUPVALUE(prazniki[prazniki],prazniki[datum],koledar[datum])

Nato bomo določili drugi argument, stolpec za iskanje z datumi, ki jih želite poiskati. Stolpec Datum smo določili v tabeli Prazniki, kot je prikazano tukaj:

=LOOKUPVALUE(prazniki[prazniki],prazniki[datum],Koledar[datum])

Na koncu v tabeli »Koledar« določimo stolpec z datumi, ki jih želite poiskati, v tabeli »Praznik«. To je seveda stolpec »Datum« v tabeli »Koledar«.

=LOOKUPVALUE(prazniki[prazniki],prazniki[datum],koledar[datum])

Stolpec Praznik vrne ime praznika za vsako vrstico z vrednostjo datuma, ki se ujema z datumom v tabeli Prazniki.

Koledar po meri – trinajst štiri-tedenska obdobja

Nekatere organizacije, na primer maloprodajne ali hrani, pogosto poročajo o različnih obdobjih, na primer trinajst štiri-tedenska obdobja. S trinajstim štiritedenim koledarjem ob vsakem obdobju je vsako obdobje 28 dni; Zato vsako obdobje vsebuje štiri ponedeljke, štiri torek, štiri srede in tako naprej. Vsako obdobje vsebuje enako število dni in ponavadi prazniki vsako leto preteč v enako obdobje. Izberete lahko, da želite začeti obdobje na kateri koli dan v tednu. Podobno kot pri datumih v koledarju ali poslovnem letu lahko z dax ustvarite dodatne stolpce z datumi po meri.

V spodnjih primerih se prvo polno obdobje začne na prvo nedeljo poslovnega leta. V tem primeru se poslovno leto začne 7/1.

Pika

Obdobje v tem koledarju po meri vsebuje 28 dni in se vedno začne z nedeljo. Ta stolpec vrne število obdobja, ki se začne s prvo nedeljo v prvem poslovnem letu.

=INT(([Teden]+3)/4)

To formulo si o bomo še natančneje ošteli.

Najprej ustvarimo formulo, ki vrne vrednost iz stolpca Teden kot celo število, na primer:

=INT([Teden])

Nato tej vrednosti prištejete 3, in sicer tako:

=INT([Teden]+3)

Nato rezultat razdelite s 4, tako:

=INT(([Teden]+3)/4)

Rezultat je videti tako:

Obdobje poslovnega leta

Ta vrednost vrne poslovno leto za obdobje.

=INT(([Obdobje]+12)/13)+2008

To formulo si o bomo še natančneje ošteli.

Najprej ustvarimo formulo, ki vrne vrednost iz obdobja in doda 12:

= ([Obdobje]+12)

Rezultat delimo s 13, ker je trinajst 28-dnevnih obdobij v proračunskem letu:

=(([Obdobje]+12)/13)

Leto 2010 smo dodali, ker je to prvo leto v tabeli:

=(([Obdobje]+12)/13)+2010

S funkcijo INT lahko odstranimo del rezultata in vrnemo celo število, če ga delimo s 13, in sicer tako:

=INT(([Obdobje]+12)/13)+2010

Rezultat je videti tako:

Obdobje v poslovnem letu

Ta vrednost vrne številko obdobja od 1 do 13 in začne s prvim polnim obdobjem (z začetkom v nedeljo) v posameznem poslovnem letu.

=IF(MOD([obdobje],13), MOD([obdobje],13),13)

Ta formula je nekoliko bolj zapletena, zato jo bomo najprej opisali v jeziku, ki ga bolje razumemo. Ta formula navaja, vrednost ločite od [Obdobje] s 13, da dobite številko obdobja (1–13) v letu. Če je to število 0, vrni 13.

Najprej ustvarimo formulo, ki vrne preostanek vrednosti iz obdobja za 13. Funkcijo MOD (matematične in trigonometrične funkcije) lahko uporabimo tako:

=MOD([Obdobje],13)

V tem primeru bo prikazan rezultat, ki ga želimo, razen če je vrednost za obdobje 0, ker ti datumi niso znotraj prvega poslovnega leta, kot v prvih petih dneh vzorčne tabele z datumom v koledarju. To lahko poskrbimo s funkcijo IF. Če je naš rezultat 0, vrnemo 13, tako:

=IF(MOD([obdobje],13),MOD([obdobje],13),13)

Rezultat je videti tako:

Več relacij

V nekaterih primerih boste morda morali ustvariti več relacij ali ustvariti več datumskih tabel. Če je na primer v tabeli dejstev »Prodaja« več polj z datumi, na primer DateKey, ShipDate in ReturnDate, imajo lahko vsa polja relacije do polja »Datum« v tabeli »Datum koledarja«, vendar je lahko le ena od teh aktivnih relacij aktivna. Ker DateKey predstavlja datum transakcije in zato najpomembnejši datum, bi bilo to najbolje deluje kot aktivni odnos. Drugi imajo nedejavne relacije.

Ta vrtilna tabela izračuna skupno prodajo po poslovnem letu in poslovnem četrtletju. Mera z imenom »Skupna prodaja« s formulo »Total Sales:=SUM([ZnesekProdaje])« se nahaja v vrednostih, polji »Poslovno leto« in »Fiskalni kvartil« iz tabele »Datum koledarja« pa sta postavljeni v VRSTICE.

Ta vrtilna tabela za neposredno posredovanje deluje pravilno, ker želimo vsoto prodaje sešteti glede na datumtransakcijev ključu DateKey. Mera Total Sales uporablja datume v ključu DateKey in je povzeta po poslovnem letu in poslovnem četrtletju, ker med »DateKey« v tabeli »Prodaja« in stolpcem »Datum« v tabeli »Datum koledarja« obstaja odnos.

Neaktivni odnosi

Kaj pa, če bi želeli sešteti skupno prodajo ne po datumu transakcije, ampak po datumu izdaje? Med stolpcem »DatumProdaje« v tabeli »Prodaja« in stolpcem »Datum« v tabeli »Koledar« potrebujemo relacijo. Če tega odnosa ne ustvarimo, naša združevanja vedno temeljijo na datumu transakcije. Vendar pa je lahko več relacij, čeprav je lahko aktivna le ena, in ker je datum transakcije najpomembnejši, dobi aktivni odnos s tabelo »Koledar«.

V tem primeru ima »DatumElažja« neaktivno relacijo, zato mora vsaka formula mera, ustvarjena za združevanje podatkov na podlagi datumov pošiljatelja, določiti neaktivni odnos s funkcijo USERELATIONSHIP.

Ker je na primer med stolpcem »DatumProdaje« v tabeli »Prodaja« in stolpcem »Datum« v tabeli »Koledar« nedejaven, lahko ustvarimo mero, ki sešteje skupno prodajo po datumu odprem. Če želite določiti razmerje, ki ga boste uporabili, uporabite tako formulo:

Skupna prodaja po datumu odprem:=CALCULATE(SUM(Prodaja[KoličinaProdaje]), USERELATIONSHIP(Prodaja[DatumProdaje], Koledar[Datum]))

Ta formula preprosto navaja: Izračunaj vsoto za ZnesekProdaje, vendar filtriraj glede na razmerje med stolpcem »DatumProdaje« v tabeli »Prodaja« in stolpcem »Datum« v tabeli »Koledar«.

Če zdaj ustvarimo vrtilno tabelo in v vrstice postavimo mero »Skupna prodaja po datumu odprem« v vrednostih VREDNOSTI, poslovno leto in poslovno četrtletje pa v vrstice, je enaka skupna vsota, vendar so vsi drugi zneski vsot za poslovno leto in fiskalno četrtletje drugačni, ker temeljijo na datumu odprema in ne na datumu transakcije.

Če uporabljate neaktivne relacije, lahko uporabite le eno datumsko tabelo, vendar pa morate, da se vse ukrepe (na primer Skupna prodaja po datumu odpreve) sklicevajo na neaktivni odnos v formuli. Obstaja druga možnost, to pomeni, da uporabite več datumske tabele.

Več datumnih tabel

Več datumskih stolpcev lahko v tabeli dejstev delate tudi tako, da ustvarite več datumskih tabel in med njimi ustvarite ločene aktivne relacije. Ogledamo primer tabele »Prodaja«. Imamo tri stolpce z datumi, za katere bomo morda želeli združiti podatke:

• Ključ datuma z datumom prodaje za vsako transakcijo.

• DatumPošteta – z datumom in časom, ko so bili prodani artikli poslani stranki.

• Datum Vrnitve – z datumom in časom, ko je bil vrnjen en element ali več vrnjenih elementov.

Ne pozabite, da je polje DateKey z datumom transakcije najpomembnejše. Večino združevanj bomo naredili na podlagi teh datumov, zato bomo zagotovo želeli imeti relacijo med njim in stolpcem »Datum« v tabeli »Koledar«. Če ne želimo ustvariti neaktivnih odnosov med poljema »Datum Najemla« in »Datum« v tabeli »Koledar« in tako zahtevate posebne formule merske enote, lahko za datum in datum vrnitve ustvarimo dodatne tabele z datumi. Nato lahko ustvarimo aktivne odnose med njimi.

V tem primeru smo ustvarili drugo datumsko tabelo, imenovano ShipCalendar. To seveda pomeni tudi ustvarjanje dodatnih stolpcev z datumi in ker so ti stolpci z datumi v drugi datumski tabeli, jih želimo poimenvati tako, da jih ločimo od istih stolpcev v tabeli »Koledar«. Ustvarili smo na primer stolpce z imenom »ShipYear, ShipMonth, ShipQuarter« in tako naprej.

Če ustvarimo vrtilno tabelo in v vrstice postavimo mero »Skupna prodaja« v VREDNOSTI ter »ShipFiscalYear« in »ShipFiscalQuarter« v vrstice, vidimo enake rezultate, kot smo videli, ko smo ustvarili neaktivni odnos, in posebno izračunano polje »Skupna prodaja po datumu odpreje«.

Vsak od teh pristopov je treba skrbno upoštevati. Če uporabljate več relacij z eno datumsko tabelo, boste morda morali ustvariti posebne ukrepe, ki so aktivni med aktivnimi odnosi z uporabo funkcije USERELATIONSHIP. Ustvarjanje več datumnih tabel pa je lahko zapletenejše v seznamu polj, ker pa imate v podatkovnem modelu več tabel, boste potrebovali več pomnilnika. Preskusite tisto, kar vam najbolj pomaga.

Navodila: Nastavitev lastnosti »Datumna tabela«

1. V PowerPivot izberite tabelo Koledar.

2. Na zavihku Načrt kliknite Označi kot datumska tabela.

3. V pogovornem oknu Označi kot datumske tabele izberite stolpec z enoličnimi vrednostmi in podatkovni tip Datum.

Pretvorba datumov vrste besedilnih podatkov v podatkovni tip »datum«

V nekaterih primerih lahko tabela dejstev s podatki o transakciji vsebuje datume besedilnega podatkovnega tipa. To pomeni, da datum, ki je prikazan kot datum 2012-12-04T11:47:09, pravzaprav sploh ni datum ali pa vsaj ni mogoče razumeti vrste datuma, ki ga Power Pivot lahko razume. Gre v resnici samo za besedilo, ki bere kot datum. Če želite ustvariti relacijo med stolpcem z datumom v tabeli dejstev in stolpcem z datumom v datumski tabeli, morata biti oba stolpca podatkovnega tipa »Datum«.

Ko želite podatkovni tip za stolpec datumov, ki so besedilni podatkovni tip, spremeniti v podatkovni tip datuma, lahko Power Pivot po navadi interpretira datume in jih samodejno pretvori v pravi podatkovni tip. Če Power Pivot ne more narediti pretvorbe podatkovnega tipa, se prikaže sporočilo o napaki zaradi neveljavosti tipa.

Vendar pa lahko datume še vedno pretvorite v podatkovni tip »true«. Ustvarite lahko nov izračunan stolpec in s formulo DAX razčlenite leto, mesec, dan, čas itd. iz besedilnih nizov in ga nato znova združite, tako da lahko Power Pivot prebere kot pravi datum.

V tem primeru smo v Power Pivot uvozili tabelo dejstev z imenom »Prodaja«. Vsebuje stolpec z imenom DateTime. Vrednosti so videti tako:

Če si ogledate zavihek »Osnovno« skupine »Oblikovanje« skupine »Oblikovanje«, vidimo, da gre za podatkovni tip »Besedilo«.

V tabeli datuma ni mogoče ustvariti relacije med stolpcema DateTime in Date, ker se podatkovni tipi ne ujemajo. Če poskušamo podatkovni tip spremeniti v »Datum«, pride do napake zaradi neveljhanega tipa:

V tem primeru Power Pivot ni mogel pretvoriti podatkovnega tipa iz besedila v datum. Ta stolpec lahko še vedno uporabimo, vendar če ga želite prešteti v podatkovni tip »pravi datum« , moramo ustvariti nov stolpec, ki razčleni besedilo in ga znova ustvari v vrednost, ki jo Power Pivot lahko spremeni v podatkovni tip »Datum«.

Ne pozabite: v razdelku Delo s časom v tem članku. Razen če je potrebna natančnost vaše analize na časovno raven dneva, pretvorite datume v tabeli dejstev na raven natančnosti dneva. Zato želimo, da so vrednosti v novem stolpcu natančno določene (brez časa). Vrednosti v stolpcu »DateTime« lahko pretvorimo v podatkovni tip »Datum« in s to formulo odstranimo časovno raven natančnosti:

=DATE(LEFT([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2))

S tem ustvarite nov stolpec (v tem primeru »Datum«). Power Pivot celo zazna vrednosti kot datume in samodejno nastavi podatkovni tip na datum.

Če želimo ohraniti časovno raven natančnosti, formulo preprosto razširimo tako, da vključuje ure, minute in sekunde.

=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))

Zdaj, ko imamo stolpec »Datum« podatkovnega tipa Datum, lahko v datumu ustvarimo relacijo med njim in stolpcem z datumom.

Dodatni viri

Datumi v dodatku Power Pivot

Izračuni v dodatku Power Pivot

Vodnik za hitri začetek: naučite se osnov jezika DAX v 30 minutah

Sklic na izraze za analizo podatkov

Središče za vire za DAX