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

Ustvarjanje datumsko tabelo v Excelu

V Excelu lahko ustvarite datumsko tabelo in jo nato kopirate v novo tabelo v podatkovnem modelu. To je res zelo enostavno in vam daje veliko prilagodljivosti.

Ko v Excelu ustvarite datumsko tabelo, začnete z enim stolpcem z neprekinjenim obsegom datumov. Nato lahko z Excelovimi formulami ustvarite dodatne stolpce, kot so Leto, Četrtletje, Mesec, Poslovno leto, Obdobje itd., z Excelovimi formulami 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 z datumom v nadaljevanju tega članka.

Adding new date columns to the date table

Datumsko tabelo z enim stolpcem z datumom, ki ima eno vrstico za vsak dan za vsako leto, je pomembno za določanje vseh datumov v datumskega obsega. Prav tako je nujno, da ustvarite relacijo med tabelo de factov in tabelo z datumi. Vendar pa en stolpec z eno vrstico za vsak dan ni uporaben pri analizi po datumih v vrtilni tabeli ali poročilu Power View. V datumsko tabelo želite vključiti stolpce, s katerimi 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, s katero izračunate letno rast. V vsakem od teh primerov vaša datumska tabela potrebuje stolpce za leto, mesec ali četrtletje, s katerih lahko združite podatke za to obdobje.

Če ste uvozili datumsko tabelo iz relacijskih virov podatkov, morda že vključujejo različne vrste želenih stolpcev z datumi. 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 Excelu 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 orodju DAX.

Funkcije za datum in čas jezika DAX

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

• Funkcije za datum in čas JEZIKA DAX uporabljajo podatkovni tip »datetime«.

• Vrednosti iz stolpca lahko vnesete 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 je treba razumeti. S temi funkcijami bomo ustvarili stolpce za »Leto«, »Četrtletje«, »FiscalMonth« in tako naprej.

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

• DATUM

• DATEVALUE

• NASLEDNJI DAN

• EDATE

• EOMONTH

• URO

• MINUTO

• MESEC

• NWO

• DRUGI

• ČAS

• TIMEVALUE

• DANES

• DAN

• WEEKNUM (ŠTEVILO TEDNOV)

• LETO

• YEARFRAC (LETOFRAC)

V formulah lahko uporabite tudi številne druge funkcije DAX. Številne formule, opisane tukaj, na primer uporabljajo matematične in trigonometričnefunkcije, kot so MOD in TRUNC, logične funkcije, kot so IF in funkcije besedila, kot je FORMAT. Če želite več informacij o drugih funkcijah jezika 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 v tej formuli dobiti ime meseca, s funkcijo FORMAT pretvorite številsko vrednost iz stolpca »Datum« v besedilo. Stolpec Datum določimo kot prvi argument in nato za obliko; želimo, da ime meseca prikazuje vse znake, zato uporabljamo »mmmm«. Naš rezultat je videti tako:

Če želimo dobiti ime meseca, okrajšano na tri črke, bi v argumentu oblike uporabili »mmm«.

Dan v tednu

=FORMAT([datum],"ddd")

V tej formuli smo uporabili funkcijo FORMAT, da pridobimo ime dneva. Ker želimo le okrajšano ime dneva, v argumentu oblike zapisa navedemo »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 »ZnesekProprojekta« iz tabele »Podatki o prodaji« v vrednostih in »Leto« in »Četrtletje« iz tabele dimenzij koledarja v razdelku VRSTICE. Znesek »SalesAmount« je združen za leto in četrtletje.

Primeri formul za poslovno leto

Prazniki ali posebni datumi

Morda boste želeli vključiti stolpec z datumom, ki označuje, da so določeni datumi prazniki ali drugi posebni datumi. Morda boste želeli sešteti skupne vsote prodaje za nov dan let 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 mere.

Preprosto je tudi vnesti praznike ali posebne dni. V Excelu lahko ustvarite tabelo z datumi, ki jih želite vključiti. Nato ga lahko kopirate ali uporabite za dodajanje podatkovnega modela v podatkovni model kot povezano tabelo. V večini primerov ni treba ustvariti relacije med tabelo in tabelo Koledar. Vse formule, na katere se sklicuje, lahko s funkcijo LOOKUPVALUE vrnejo vrednosti.

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

V tabeli z datumi smo ustvarili stolpec z imenom Praznik in uporabili formulo, podobno tej:

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

Te formule si bomo še bolj natančno omislili.

Funkcijo LOOKUPVALUE uporabljamo za pridobivanje vrednosti iz stolpca Prazniki v tabeli Prazniki. V prvem argumentu smo določili stolpec, kjer bo naša vrednost rezultata. V tabeli Prazniki smo določili stolpec Prazniki , ker je to vrednost, ki jo želimo vrniti.

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

Nato določimo drugi argument, stolpec za iskanje z datumi, ki jih želite poiskati. V tabeli Prazniki smo določili stolpec Datum , in to tako:

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

Na koncu v tabeli Koledar določimo stolpec z datumi, ki jih želimo poiskati, v tabeli Prazniki . To je seveda stolpec Datum v tabeli Koledar.

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

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

Koledar po meri – trinajst štiritevnih obdobij

Nekatere organizacije, na primer maloprodajne ali prehranske storitve, pogosto poročajo o različnih obdobjih, na primer trinajst štirih tedenskih obdobjih. S trinajstimi štiritevni koledarji 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 običajno prazniki vsako leto padejo v enako obdobje. Izberete lahko, da želite začeti obdobje na kateri koli dan v tednu. Tako kot pri datumih v koledarju ali poslovnem letu lahko uporabite dax za ustvarjanje dodatnih stolpcev z datumi po meri.

V spodnjih primerih se prvo polno obdobje začne na prvo nedeljo v proračunskem letu. V tem primeru se poslovno leto začne 7/1.

Pika

Obdobje v tem koledarju po meri vsebuje 28 dni in vedno se bo začelo na nedeljo. V tem stolpcu je vrnjeno število obdobja, ki se začne s prvo nedeljo v prvem poslovnem letu.

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

Te formule si bomo še bolj natančno omislili.

Najprej ustvarimo formulo, ki vrne vrednost iz stolpca Teden kot celo število, kot je to:

=INT([Teden])

Nato tej vrednosti dodajte 3, tako:

=INT([Teden]+3)

Nato rezultat delite s 4, kot je prikazano tukaj:

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

Rezultat je videti tako:

Poslovno leto obdobja

Ta vrednost vrne poslovno leto za obdobje.

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

Te formule si bomo še bolj natančno omislili.

Najprej ustvarimo formulo, ki vrne vrednost iz obdobja in prišteje 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

Funkcijo INT na koncu uporabljamo za odstranitev katerega koli dela rezultata in za vrnitev celega števila, če ga delimo s 13, kot je prikazano tukaj:

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

Rezultat je videti tako:

Obdobje v poslovnem letu

Ta vrednost vrne število obdobij od 1 do 13, pri tem pa začne s prvim polnim obdobjem (z začetkom v nedeljo) v vsakem 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 določa, delite vrednost iz [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 uporabite tako:

=MOD([Obdobje],13)

Ta rezultat je v večini primerov rezultat, ki ga želimo, razen če je vrednost za obdobje 0, ker ti datumi niso znotraj prvega poslovnega leta, kot v prvih petih dneh naše vzorčne tabele z datumom v koledarju. Za to lahko poskrbimo s funkcijo IF. Če je naš rezultat 0, vrnemo 13, in sicer tako:

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

Rezultat je videti tako:

Več relacij

V nekaterih primerih bo morda treba ustvariti več relacij ali ustvariti več datumovih tabel. Če je na primer v tabeli z vrednostjo »DatumProdaje« več polj z datumi, na primer »DatumPošiljanja«, »DatumPošiljanja« in »DatumPojavnegaDatuma«, imajo lahko vsi relacije do polja »Datum« v tabeli »Datum koledarja«, vendar je lahko le ena od teh aktivni odnos. Ker DateKey predstavlja datum transakcije in zato najpomembnejši datum, bi bilo to najbolje služiti kot aktivni odnos . Drugi imajo neaktivne odnose.

Ta vrtilna tabela izračuna skupno prodajo po poslovnem letu in poslovnem četrtletju. Mera z imenom »Skupna prodaja« s formulo Total Sales:=SUM([KoličinaPro prodaja])) je v vrednostih, polja »FiscalYear« in »FiscalQuarter« iz tabele »Datum koledarja« pa so postavljena v vrstice.

Ta vrtilna tabela za neposredno posredovanje deluje pravilno, ker želimo skupno prodajo sešteti po datumutransakcije v funkciji DateKey. Mera Total Sales uporablja datume v ključu DateKey in sešteje po poslovnem letu in poslovnem četrtletju, ker obstaja odnos med »Datumski ključ« v tabeli »Prodaja« in stolpcem »Datum« v tabeli »Datum koledarja«.

Neaktivni odnosi

Kaj pa, če želimo sešteti skupno prodajo ne po datumu transakcije, ampak po datumu odpreme? Potrebujemo relacijo med stolpcem »DatumPošiljanja« v tabeli »Prodaja« in stolpcem »Datum« v tabeli »Koledar«. Če tega odnosa ne ustvarimo, naša združevanja vedno temeljijo na datumu transakcije. Vendar pa lahko imamo 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 »DatumPošiljanja« nedejaven odnos, zato mora vsaka formula mere, ustvarjena za združevanje podatkov na podlagi datumov odpreme, s funkcijo USERELATIONSHIP določiti neaktivni odnos.

Ker je na primer med stolpcem »DatumPošiljanja« v tabeli »Prodaja« in stolpcem »Datum« v tabeli »Koledar« nedejaven odnos, lahko ustvarimo mero, ki sešteje skupno prodajo po datumu odpreme. Tako formulo uporabljamo za določanje relacije, ki jo želite uporabiti:

Skupna prodaja po datumu odpreme:=CALCULATE(SUM(Prodaja[KoličinaProdaje]), USERELATIONSHIP(Prodaja[DatumPošiljanja], Koledar[Datum]))

Ta formula preprosto določa: Izračunajte vsoto za »ZnesekProdaje«, vendar filtrirajte tako, da uporabite relacijo med stolpcem »DatumPošiljanja« v tabeli »Prodaja« in stolpcem »Datum« v tabeli »Koledar«.

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

Če uporabljate neaktivne relacije, lahko uporabite le eno datumsko tabelo, vendar pa potrebujete, da se vse mere (na primer Total Sales by Ship Date) sklicujejo na neaktivno relacijo v svoji formuli. Obstaja druga druga možnost, to je uporaba več datumovih tabel.

Več datumovih tabel

Z več datumski stolpci v tabeli dejavnih podatkov lahko delate tudi tako, da ustvarite več datumovih tabel in med njimi ustvarite ločene aktivne relacije. Opišite si primer tabele »Prodaja«. Imamo tri stolpce z datumi, za katere morda želimo združiti podatke:

• Datumski ključ z datumom prodaje za vsako transakcijo.

• DatumPošiljanja – z datumom in uro, ko so bili prodani artikli odpremljeni stranki.

• ReturnDate – z datumom in uro, ko je bil vrnjen en element ali več.

Ne pozabite, da je polje DateKey z datumom transakcije najpomembnejše. Večino svojih združevanj bomo odpravili na podlagi teh datumov, zato si bomo gotovo želeli relacijo med njim in stolpcem »Datum« v tabeli »Koledar«. Če ne želimo ustvariti neaktivnih odnosov med »DatumPošiljanja« in »DatumPošiljanja« in poljem »Datum« v tabeli »Koledar«, zato je potrebna posebna formula mere, lahko ustvarimo dodatne tabele datumov za datum dostave in datum vračila. Med njimi lahko nato ustvarimo aktivne odnose.

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

Če ustvarimo vrtilno tabelo in mero Total Sales vnesemo v VREDNOSTI, leto »ShipFiscalYear« in »ShipFiscalQuarter« pa v vrstice, bomo videli enake rezultate, kot smo jih videli, ko smo ustvarili neaktivni odnos, in posebno izračunano polje »Skupno prodajo po datumu dostave«.

Vsak od teh pristopov zahteva skrbno upoštevanje. Če uporabljate več relacij z eno datumsko tabelo, boste morda morali ustvariti posebne mere, ki s funkcijo USERELATIONSHIP prevajajo neaktivne relacije. Po drugi strani pa je ustvarjanje več datumovih tabel lahko nejasno na seznamu polj in ker imate v podatkovnem modelu več tabel, boste za to morali imeti več pomnilnika. Preizkusite tisto, kar vam najbolj ustreza.

Navodila: Nastavitev lastnosti »Tabela datuma«

1. V oknu dodatka PowerPivot izberite tabelo Koledar.

2. Na zavihku Načrt kliknite Označi kot datumsko tabelo.

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

Pretvarjanje datumov besedilnih podatkovnih tipov v podatkovni tip »datum«

V nekaterih primerih lahko tabela de factov s podatki o transakcijah vsebuje datume besedilnega podatkovnega tipa. To pomeni, da datum, ki je prikazan kot 2012-12-04T11:47:09, pravzaprav ni datum ali pa vsaj vrsta datuma, ki ga Power Pivot ne more razumeti. To je res samo besedilo, ki bere kot datum. Če želite ustvariti relacijo med stolpcem z datumom v tabeli z dejstvom in stolpcem z datumom v tabeli z datumom, morata biti oba stolpca vrste podatkov » Datum«.

Ko želite podatkovni tip za stolpec z datumi, 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 datuma. Če Power Pivot ne more pretvarjati podatkovnega tipa, se prikaže napaka zaradi neujemanja vrste.

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

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

Če si v skupini »Oblikovanje« ogledate zavihek »Osnovno« v skupini »Oblikovanje«, je prikazano, da gre za podatkovni tip »Besedilo«.

Relacije med stolpcema DateTime in Date v datumski tabeli ni mogoče ustvariti, ker se podatkovni tipi ne ujemajo. Če poskušamo podatkovni tip spremeniti v »Datum«, pride do napake zaradi neujemanja vrste:

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

Ne pozabite, v razdelku Delo s časom prej v tem članku; če ni nujno, da je vaša analiza natančno nastavljena na časovno raven dneva, pretvorite datume v tabeli de factov na raven natančnosti dneva. V mislih želimo, da so vrednosti v novem stolpcu na dnevni ravni natančnosti (brez časa). Vrednosti v stolpcu DateTime lahko pretvorimo v podatkovni tip datuma in s to formulo odstranimo časovno raven natančnosti:

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

Prikaže se nov stolpec (v tem primeru imenovan 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« v podatkovnem tipu »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 jezika DAX