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

Opomba: Najnovejšo vsebino pomoči v vašem jeziku vam želimo zagotoviti v najkrajšem možnem času. Ta stran je bila prevedena z avtomatizacijo in lahko vsebuje slovnične napake ali nepravilnosti. Naš namen je, da bi bila vsebina za vas uporabna. Ali nam lahko na dnu te strani sporočite, ali so bile informacije za vas uporabne? Tukaj je angleški članek za preprosto referenco.

Datumske tabele v Dodatku Power pivot so bistvenega pomena za brskanje in izračunavanje podatkov v daljšem časovnem obdobju. V tem članku je podrobno razumevanje datumskih tabel in kako jih lahko ustvarite v Dodatku Power pivot. V tem članku so opisani zlasti:

  • Zakaj je datumska tabela pomembna za brskanje in izračunavanje podatkov po datumih in času.

  • Uporaba dodatka Power pivot za dodajanje datumske tabele v podatkovni model.

  • Ustvarjanje novih datumskih stolpcev, kot so leto, mesec in obdobje v datumski tabeli.

  • Kako ustvariti relacije med datumskimi tabelami in tabelami dejstev.

  • Kako delati s časom.

Ta članek je namenjen uporabnikom, ki so novi v Dodatku Power pivot. Vendar pa je pomembno, da že dobro razumete uvoz podatkov, ustvarjanje relacij in ustvarjanje izračunanih stolpcev in mer.

V tem članku ni opisano, kako lahko uporabite funkcije časovnih obveščevalnIH storitev Dax v formulah za merjenje. Če želite več informacij o tem, kako ustvarite ukrepe s funkcijami časovnih obveščevalnih podatkov DAX, glejte časovnih podatkov v Dodatku Power pivot v Excelu.

Opomba: V Dodatku Power pivot so imena» mera «in» izračunano polje «sinonima. V tem članku uporabljamo merilo imena. Če želite več informacij, glejte mere v Dodatku Power pivot.

Vsebina

Razumevanje datumskih tabel

Dodajanje datumske tabele v podatkovni model

Uvoz iz relacijske zbirke podatkov

Ustvarjanje datumske tabele v Excelu

Kako: ustvarjanje datumske tabele v Excelu in kopiranje v podatkovni model

Dodajanje novih datumskih stolpcev v datumsko tabelo

Datumske in časovne funkcije DAX

Primeri formul za koledarsko leto

Leto

Mesec

Četrtletje

Ime meseca

Primeri formul za poslovno leto

Poslovno leto

Proračunski mesec

Poslovna četrt

Prazniki ali posebni datumi

Koledar po meri – 13 4 – tedenska obdobja

Teden

Obdobje

Obdobje poslovnega leta

Obdobje v FiscalYear

Relacije

Več relacij

NeDejavni relacije

Več datumskih tabel

Lastnost» datumska tabela «

Delo s časom

Ustvarjanje datumov, ki so bolj uporabni

Dodatek

Pretvorba datumov besedilnih podatkov v podatkovni tip» datum «

Dodatni viri

Razumevanje datumskih tabel

Skoraj vsa analiza podatkov vključuje brskanje in primerjavo podatkov v datumih in času. Na primer, morda želite sešteti zneske prodaje za preteklo davčno četrtletje in nato primerjati te vsote z drugimi četrtletji, ali pa boste morda želeli izračunati mesečno stanje zapiranja za račun. V vsakem od teh primerov uporabljate datume kot način združevanja in združevanja prodajnih transakcij ali saldov za določeno obdobje.

Poročilo Power View

Vrtilna tabela s skupno prodajo po poslovnem četrtletju

Datumska tabela lahko vsebuje več različnih predstavitev datumov in časa. Datumska tabela bo na primer pogosto imela stolpce, kot so poslovno leto, mesec, četrtletje ali obdobje, ki ga lahko izberete kot polja s seznama polj, ko razrežete in filtrirate podatke v vrtilnih tabelah ali poročilih Power View.

Seznam polj» Power View «

Seznam polj dodatka Power View

Če želite v datumskih stolpcih, kot so leto, mesec in četrtletje, vključiti vse datume v ustreznem obsegu, mora imeti datumska tabela vsaj en stolpec z neprekinjenim naborom datumov. To pomeni, da mora stolpec imeti eno vrstico za vsak dan za vsako leto, ki je vključena v datumsko tabelo.

Če na primer podatki, ki jih želite prebrskati, vsebujejo datume iz februarja 1, 2010 do novembra 30 2012 in poročate o koledarskem letu, potem boste želeli datumsko tabelo z vsaj datumskega obsega od januarja 1 2010 do 31 december 2012. Vsako leto v tabeli Date mora vsebovati vse dneve za vsako leto. Če boste redno osveževanje podatkov z novejšimi podatki, boste morda želeli zagnati končni datum za leto ali dve, tako da vam ni treba posodobiti datumske tabele, kot je čas mineval.

Datumska tabela z neprekinjenim naborom datumov

Datumska tabela z zaporednimi datumi

Če poročate o poslovnem letu, lahko ustvarite datumsko tabelo z neprekinjenim naborom datumov za vsako poslovno leto. Na primer, če se vaše poslovno leto začne marca 1st in imate podatke za poslovna leta 2010 navzgor s trenutnim datumom (na primer v programu FL 2013), lahko ustvarite datumsko tabelo, ki se začne z 3/1/2009 in vključuje vsaj vsak dan v vsakem poslovnem letu prek zadnjega datuma. v poslovnem letu 2013.

Če boste poročali o koledarskem letu in poslovnem letu, vam ni treba ustvariti ločenih datumskih tabel. V eni tabeli lahko vključite stolpce za koledarsko leto, poslovno leto in celo koledar obdobja 13 4. Pomembna stvar je v tem, da je v tabeli» datum «prikazan neprekinjen nabor datumov za vsa vključena leta.

Dodajanje datumske tabele v podatkovni model

Datumsko tabelo lahko dodate v podatkovni model na več načinov:

  • Uvozite iz relacijske zbirke podatkov ali drugega vira podatkov.

  • Ustvarjanje datumske tabele v Excelu in kopiranje ali povezovanje z novo tabelo v Dodatku Power pivot.

  • Uvozite iz tržnice Microsoft Azure.

Oglejmo si vse te tesneje.

Uvoz iz relacijske zbirke podatkov

Če uvozite nekatere ali vse podatke iz podatkovnega skladišča ali druge vrste relacijske zbirke podatkov, obstaja možnost, da je na voljo že datumska tabela in relacije med njim in ostalimi podatki, ki jih uvažate. Datumi in oblika se bodo verjetno ujemali z datumi v vaših podatkih o dejstvih in datumi se verjetno začnejo tudi v preteklosti in bodo v prihodnje še daleč. Datumska tabela, ki jo želite uvoziti, je lahko zelo velika in vsebuje obseg datumov, ki jih boste morali vključiti v podatkovni model. Z naprednimi funkcijami filtra čarovnika za uvoz v Dodatku Power pivot lahko selektivno izberete le datume in določene stolpce, ki jih res potrebujete. S tem lahko znatno zmanjšate velikost delovnega zvezka in izboljšate učinkovitost delovanja.

Čarovnik za uvoz tabele

Pogovorno okno čarovnika za uvoz tabele

V večini primerov vam ne bo treba ustvariti dodatnih stolpcev, kot so poslovno leto, teden, ime meseca itd., ker bodo že obstajale v uvoženi tabeli. Vendar pa lahko v nekaterih primerih po tem, ko imate datumsko tabelo, uvoženo v podatkovni model, ustvarite dodatne datumske stolpce, odvisno od določene potrebe po poročanju. Na srečo je to preprosto za uporabo storitve DAX. Izvedeli boste več o ustvarjanju polj z datumskimi tabelami pozneje. Vsako okolje je drugačno. Če niste prepričani, ali so vaši viri podatkov povezani s datumsko ali koledarsko tabelo, se pogovorite z skrbnikom zbirke podatkov.

Ustvarjanje datumske tabele v Excelu

V Excelu lahko ustvarite datumsko tabelo in jo nato kopirate v novo tabelo v podatkovnem modelu. To je zelo preprosto narediti in vam daje veliko fleksibilnosti.

Ko ustvarite datumsko tabelo v Excelu, začnete z enim stolpcem z neprekinjenim obsegom datumov. Nato lahko ustvarite dodatne stolpce, kot so leto, četrtletje, mesec, poslovno leto, obdobje itd., na Excelovem delovnem listu z uporabo Excelovih formul ali, ko kopirate tabelo v podatkovni model, jih lahko ustvarite kot izračunane stolpce. Ustvarjanje dodatnih datumskih stolpcev v Dodatku Power pivot je opisano v razdelku Dodajanje novih datumskih stolpcev v razdelek datumska tabela v nadaljevanju tega članka.

Kako: ustvarjanje datumske tabele v Excelu in kopiranje v podatkovni model

  1. V Excelu v praznem delovnem listu v celico a1vnesite ime glave stolpca, da prepoznate obseg datumov. Običajno bo tonekaj podobnega datumu, datetime ali poimenovali.

  2. V celico a2vnesite začetni datum. Na primer 1/1/2010.

  3. Kliknite zapolnitveno ročico in jo povlecite navzdol do številke vrstice, ki vključuje končni datum. Na primer 12/31/2016.

    Datumski stolpec v Excelu

  4. Izberite vse vrstice v stolpcu» datum «(vključno z imenom glave v celici a1).

  5. V skupini slogi kliknite Oblikuj kot tabeloin nato izberite slog.

  6. V pogovornem oknu Oblikuj kot tabelo kliknite v redu.

    Datumski stolpec v dodatku Power Pivot

  7. Kopirajte vse vrstice, vključno z glavo.

  8. V Dodatku Power pivot na zavihku osnovno kliknite Prilepi.

  9. V Prilepi preview _GT_ ime tabele vnesite ime, kot je datum ali koledar. Pusti prvo vrstico uporabi kot glavestolpcev in nato kliknite v redu.

    Predogled lepljenja

    Nova datumska tabela (imenovan koledar v tem primeru) v Dodatku Power pivot je videti tako:

    Datumska tabela v dodatku Power Pivot

    Opomba: Povezano tabelo lahko ustvarite tudi tako, da uporabite» Dodaj v podatkovni model«. Vendar je zaradi tega vaš delovni zvezek nepotrebno velik, ker ima delovni zvezek dve različici datumske tabele; eno v Excelu in eno v Dodatku Power pivot..

Opomba: Datum imena je ključna beseda v Dodatku Power pivot. Če poimenujete tabelo, ki jo ustvarite v datumu dodatka Power pivot, boste morali ime tabele priložiti z enojnimi narekovaji v poljubnih formulah DAX, ki se sklicujejo na argument. Vse vzorčne slike in formule v tem članku se nanašajo na datumsko tabelo, ustvarjeno v Dodatku Power pivot z imenom koledar.

Zdaj imate v podatkovnem modelu datumsko tabelo. Nove stolpce za datum, kot so leto, mesec itd., lahko dodate tako, da uporabite DAX.

Dodajanje novih datumskih stolpcev v datumsko tabelo

Datumska tabela z enim stolpcem datuma, ki ima eno vrstico za vsak dan za vsako leto, je pomembna za določanje vseh datumov v datumskem obsegu. Prav tako je treba ustvariti relacijo med tabelo» Fact «in datumsko tabelo. Toda en datumski stolpec z eno vrstico za vsak dan ni uporaben, če analizirate po datumih v poročilu vrtilne tabele ali Power View. Če želite, da bo datumska tabela vključena v stolpce, ki vam bodo pomagali združiti podatke za obseg ali skupino datumov. Morda želite na primer sešteti zneske prodaje po mesecih ali četrtletjih, lahko pa ustvarite merilo, ki izračuna letno rast. V vsakem od teh primerov mora vaša datumska tabela dati stolpcem 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 datumov, ki jih želite. V nekaterih primerih boste morda želeli spremeniti nekatere od teh stolpcev ali ustvariti dodatne datumske stolpce. To velja še posebej, če ustvarite svojo datumsko tabelo v Excelu in jo kopirate v podatkovni model. Na srečo je ustvarjanje novih datumskih stolpcev v Dodatku Power pivot dokaj preprosto z datumskimi in časovniMi funkcijami v Dax.

Namig: Če še niste delali z DAX, je odličen kraj za začetek učenja : Oglejte si osnove za Dax v 30 minutah na Office.com.

Datumske in časovne funkcije DAX

Če ste kdaj delali s funkcijami za datum in čas v Excelovih formulah, boste verjetno seznanjeni s funkcijami za datum in čas. Čeprav so te funkcije podobne njihovim kolegom v Excelu, obstaja nekaj pomembnih razlik:

  • Datumske in časovne funkcije DAX uporabljajo podatkovni tip» datetime «.

  • Vrednosti iz stolpca lahko prevzamejo kot argument.

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

Te funkcije se pogosto uporabljajo pri ustvarjanju datumskih stolpcev po meri v datumski tabeli, tako da jih je treba razumeti. Za ustvarjanje stolpcev za leto, četrtletje, FiscalMonth in tako naprej bomo uporabili številne te funkcije.

Opomba: Funkcije za datum in čas v sistemu DAX niso enake kot funkcije časovnih podatkov. Več informacij o časovnih podatkih v Dodatku Power pivot v programu Excel 2013.

DAX vključuje te funkcije datuma in ure:

Na voljo je tudi veliko drugih funkcij DAX, ki jih lahko uporabite v formulah. Na primer, številne formule, opisane tukaj, uporabljajo matematične in trigonometrične funkcije , kot sta mod in TRUNC, logične funkcije , kot so if, in funkcije Text , kot je oblika za več informacij o druge funkcije DAX, si oglejte 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, imenovan datum, že obstaja in vsebuje neprekinjen obseg datumov od 1/1/2010 do 12/31/2016.

Leto

= YEAR ([datum])

V tej formuli funkcija year vrne leto od vrednosti v stolpcu» datum «. Ker je vrednost v stolpcu» datum «podatkovni tip» datetime «, funkcija YEAR ve, kako ji vrniti leto.

Stolpec »Leto«

Mesec

= MONTH ([datum])

V tej formuli, podobno kot pri funkciji YEAR, lahko preprosto uporabite funkcijo month , da vrnete mesečno vrednost iz stolpca» datum «.

Stolpec »Mesec«

Četrtletje

= INT (([month] + 2)/3)

V tej formuli uporabljamo funkcijo int , če želite datumsko vrednost vrniti kot celo število. Argument, ki ga določite za funkcijo INT, je vrednost iz stolpca» mesec «, dodajte 2 in jo nato razdelite s 3, da dobite četrt, od 1 do 4.

Stolpec »Četrtletje«

Ime meseca

= FORMAT ([datum], "mmmm")

V tej formuli, če želite pridobiti ime meseca, s funkcijo» format «pretvorite številsko vrednost iz stolpca» datum «v besedilo. Datumski stolpec določamo kot prvi argument in nato obliko; ime meseca hočemo pokazati vse znake, zato bomo uporabili» mmmm «. Naš rezultat je videti tako:

Stolpec »Ime meseca«

Če želimo vrniti ime meseca skrajšano na tri črke, bi uporabili» mmm «v argumentu» oblika «.

Dan v tednu

= FORMAT ([datum], "DDD")

V tej formuli uporabljamo funkcijo FORMAT, da dobite ime dneva. Ker želimo le skrajšano ime dneva, v argumentu oblika določite» ddd «.

Stolpec »Dan v tednu«
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 primer, ta slika prikazuje polje» SalesAmount «iz tabele za prodajo v VREDNOSTih in leto in četrtletje v tabeli Dimenzija koledarja v VRSTICAh. SalesAmount je Združeno za leto in četrtletje.

Vzorčna vrtilna tabela

Primeri formul za poslovno leto

Poslovno leto

= IF ([month] < = 6; [leto], [leto] + 1)

V tem primeru se poslovno leto začne 1. julij.

Ni funkcije, ki bi lahko izvlekel poslovno leto iz datumske vrednosti, ker se začetni in končni datum poslovnega leta pogosto razlikujeta od tistih v koledarskem letu. Če želite pridobiti poslovno leto, najprej uporabite funkcijo if , če želite preskusiti, ali je vrednost za mesec manjša ali enaka 6. V drugem argumentu, če je vrednost za mesec manjša ali enaka 6, nato vrne vrednost iz stolpca year. V nasprotnem primeru, nato vrnite vrednost iz leta in dodajte 1.

Stolpec »Poslovno leto«

Če želite določiti vrednost za končni mesec v poslovnem letu, ustvarite merilo, ki preprosto določa mesec. Na primer FYE: = 6. Nato se lahko sklicujete na ime ukrepa na mestu številke meseca. Če je na primer = IF ([month] < = [FYE], [leto], [leto] + 1). To zagotavlja večjo fleksibilnost pri sklicevanju na končni mesec poslovnega leta v več različnih formulah.

Proračunski mesec

= IF ([month] < = 6, 6 + [mesec], [mesec]-6)

V tej formuli določite, ali je vrednost za [mesec] manjša ali enaka 6, nato pa še 6 in dodajte vrednost iz meseca, sicer odštejete 6 od vrednosti iz [meseca].

Stolpec »Poslovni mesec«

Poslovna četrt

= INT (([FiscalMonth] + 2)/3)

Formula, ki jo uporabljamo za FiscalQuarter, je precej enaka, kot je bila za četrtletje v našem koledarskem letu. Edina razlika je, da določite [FiscalMonth] namesto [month].

Stolpec »Poslovno četrtletje«

Prazniki ali posebni datumi

Morda boste želeli vključiti datumski stolpec, ki označuje, da so nekateri datumi prazniki ali kateri drugi posebni datum. Na primer, morda želite sešteti vsote prodaje za nov let, tako da v vrtilno tabelo dodate polje» praznik «, kot razčlenjevalnik ali filter. V drugih primerih boste morda želeli izločiti te datume iz drugih datumskih stolpcev ali v merilo.

Vključno s prazniki ali posebnimi dnevi je dokaj preprost. V Excelu lahko ustvarite tabelo z datumi, ki jih želite vključiti. Nato lahko kopirate ali uporabite Dodaj v podatkovni model, da ga dodate v podatkovni model kot povezano tabelo. V večini primerov ni treba ustvariti relacije med tabelo in tabelo koledar. Vse formule, ki se sklicujejo nanj, lahko uporabijo funkcijo LOOKUPVALUE , da vrnejo vrednosti.

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

Datum

Praznik

1/1/2010

Nova leta

11/25/2010

Zahvalni

12/25/2010

Božične

1. 1. 2011

Nova leta

11/24/2011

Zahvalni

12/25/2011

Božične

01.01.2012

Nova leta

22. 11. 2012

Zahvalni

12/25/2012

Božične

1/1/2013

Nova leta

11/28/2013

Zahvalni

12/25/2013

Božične

11/27/2014

Zahvalni

12/25/2014

Božične

1. 1. 2014

Nova leta

11/27/2014

Zahvalni

12/25/2014

Božične

1/1/2015

Nova leta

11/26/2014

Zahvalni

12/25/2015

Božične

01. 01. 2016

Nova leta

11/24/2016

Zahvalni

12/25/2016

Božične

V tabeli» datum «ustvarite stolpec z imenom» praznik «in uporabite formulo, kot je ta:

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

Oglejmo si to formulo bolj natančno.

Uporabite funkcijo LOOKUPVALUE, da pridobite vrednosti iz stolpca Holiday v tabeli prazniki. V prvem argumentu določite stolpec, kjer bo naša vrednost rezultata. V tabeli» prazniki «določite stolpec» praznik «, ker je to vrednost, ki jo želite vrniti.

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

Nato določite drugi argument, stolpec za iskanje z datumi, ki jih želite poiskati. Datumski stolpec določite v tabeli» prazniki «, na primer:

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

Na koncu določite stolpec v tabeli koledar , v katerem so datumi, ki jih želimo poiskati v praznični tabeli. To je seveda datumski stolpec v tabeli koledar .

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

Stolpec» praznik «bo vrnil ime praznika za vsako vrstico, ki ima datumsko vrednost, ki se ujema z datumom v tabeli prazniki.

Tabela »Praznik«

Koledar po meri – 13 4 – tedenska obdobja

Nekatere organizacije, kot je trgovina na drobno ali hrana, pogosto poročajo o različnih obdobjih, kot so 13 4 – tedenska obdobja. S koledarjem za 13 4-tedensko obdobje je vsako obdobje 28 dni; zato vsako obdobje vsebuje štiri ob ponedeljkih, štiri ob torkih, štiri srede in tako dalje. Vsako obdobje vsebuje enako število dni in običajno bodo prazniki v istem obdobju vsako leto. Izberete lahko, da začnete obdobje na kateri koli dan v tednu. Tako kot pri datumih v koledarju ali poslovnem letu lahko uporabite DAX, če želite ustvariti dodatne stolpce z datumi po meri.

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

Teden

Ta vrednost nam omogoča, da se število tednov začne s prvim polnim tednom v poslovnem letu. V tem primeru se prvi polni teden začne v nedeljo, tako da se prvi polni teden v prvem poslovnem letu v tabeli koledar dejansko začne na 7/4/2010 in se nadaljuje v zadnjem polnem tednu v tabeli koledar. Čeprav ta vrednost ni vse uporabna v analizi, je treba izračunati za uporabo v drugih formulah 28 dnevnega obdobja.

= INT ([datum]-40356)/7)

Oglejmo si to formulo bolj natančno.

Najprej ustvarimo formulo, ki vrne vrednosti iz stolpca» datum «kot celo število, na primer:

= INT ([datum])

Nato si želimo poiskati prvo nedeljo v prvem poslovnem letu. Vidimo, da je 7/4/2010.

Stolpec »Teden«

Zdaj odštejte 40356 (ki je celo število za 6/27/2010, zadnjo nedeljo iz prejšnjega poslovnega leta) od te vrednosti, da dobite število dni od začetka dni v tabeli koledar, na primer:

= INT ([datum]-40356)

Nato razdelite rezultat s 7 (dnevi v tednu), na primer:

= INT (([datum]-40356)/7)

Rezultat je videti tako:

Stolpec »Teden«

Obdobje

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

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

Oglejmo si to formulo bolj natančno.

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

=Int ([teden])

Nato dodajte 3 k tej vrednosti, na primer:

= INT ([teden]+ 3)

Nato razdelite rezultat s štirimi, na primer:

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

Rezultat je videti tako:

Stolpec »Obdobje«

Obdobje poslovnega leta

Ta vrednost vrne poslovno leto za obdobje.

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

Oglejmo si to formulo bolj natančno.

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

= ([Obdobje] + 12)

Rezultat delimo s 13, ker je v poslovnem letu 13 28 dnevnih obdobij:

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

Dodali smo 2010, ker je to prvo leto v tabeli:

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

Na koncu uporabite funkcijo INT, da odstranite kateri koli del rezultata in vrnete celo število, ko je deljeno s 13, na primer:

=Int(([obdobje] + 12)/13)+2010

Rezultat je videti tako:

Stolpec »Obdobje poslovnega leta«

Obdobje v FiscalYear

Ta vrednost vrne številko obdobja, 1 – 13, začenši s prvim polnim obdobjem (ki se začne v nedeljo) v vsakem poslovnem letu.

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

Ta formula je nekoliko bolj zapletena, zato jo bomo opisali najprej v jeziku, ki ga bolje razumemo. Ta formula določa, razdeli vrednost iz [obdobje] s 13, da dobimo številko obdobja (1-13) v letu. Če je ta številka 0, se vrnite 13.

Najprej ustvarimo formulo, ki vrne preostanek vrednosti od obdobja do 13. Uporabljamo lahko mod (matematične in trigonometrične funkcije), kot je ta:

=Mod ([obdobje]; 13)

To nam v večini primerov daje želeni rezultat, razen če je vrednost za obdobje 0, ker ti datumi ne spadajo v prvo poslovno leto, na primer v prvih petih dneh po našem vzorčnem koledarskem datumu. To lahko poskrbimo s funkcijo IF. Če je rezultat 0, se vrnemo 13, na primer:

=If(mod ([obdobje], 13), mod ([obdobje], 13), 13)

Rezultat je videti tako:

Stolpec »Obdobje v poslovnem letu«

Vzorčna vrtilna tabela

Na spodnji sliki je prikazana Vrtilna tabela z poljem» SalesAmount «iz tabele» Prodaja dejstev «v razdelku» vrednosti «in» PeriodFiscalYear in PeriodInFiscalYear «iz tabele» datum dimenzije koledarja «v VRSTICAh. SalesAmount je Združeno za kontekst v poslovnem letu in 28-dnevnem obdobju v poslovnem letu.

Vzorčna vrtilna tabela za poslovno leto

Relacije

Ko ustvarite datumsko tabelo v podatkovnem modelu, da začnete brskati po podatkih v vrtilnih tabelah in poročilih ter združite podatke glede na stolpce v tabeli» datumska dimenzija «, morate ustvariti relacijo med tabelo» Fact «s podatki o transakcijah in Datumska tabela.

Ker morate ustvariti relacijo, ki temelji na datumih, boste želeli zagotoviti, da ustvarite relacijo med stolpci, katerih vrednosti so vrste podatkov» datetime «(datum).

Za vsako datumsko vrednost v tabeli» Fact «mora v sorodni stolpec za iskanje v tabeli datum vsebovati ujemajoče se vrednosti. Če je na primer vrstica (zapis transakcije) v tabeli» Prodaja dejstev «z vrednostjo 8/15/2012 12:00 v stolpcu» poimenovali «, mora biti v stolpcu datum (imenovan koledar) ustrezna vrednost ustrezne vrednosti. To je eden od najpomembnejših razlogov za datumski stolpec v tabeli Date, da vsebuje neprekinjen obseg datumov, ki vključuje kateri koli možen datum v tabeli dejstev.

Relacije v pogledu diagrama

Opomba: Medtem ko mora biti stolpec» datum «v vsaki tabeli enak podatkovni tip (datum), oblika posameznega stolpca ni pomembna..

Opomba: Če Power pivot ne dovoli ustvarjanja relacij med obema tabelama, datumska polja morda ne bodo shranjevala datuma in časa na enako raven natančnosti. Glede na oblikovanje stolpca so vrednosti morda videti enake, vendar so shranjene drugače. Preberite več o delu s časom.

Opomba: Izogibajte se uporabi celoštevilskih nadomestnih ključev v relacijah. Ko uvažate podatke iz relacijskega vira podatkov, pogosto so stolpci datum in čas predstavljeni s nadomestnim ključem, ki je celo število stolpcev, ki predstavljajo Enolični datum. V Dodatku Power pivot se lahko izognete ustvarjanju relacij z uporabo celoštevilskih tipk za datum/čas in namesto tega uporabite stolpce, ki vsebujejo enolične vrednosti z datumskim podatkovnim tipom. Čeprav se uporaba nadomestnih ključev šteje kot najboljša praksa v tradicionalnih podatkovnih skladiščih, v Dodatku Power pivot ne potrebujete celih številskih ključev in lahko otežujejo združevanje vrednosti v vrtilnih tabelah po različnih datumskih obdobjih.

Če se pri poskusu ustvarjanja relacij prikaže sporočilo o neujemanju vrste, je to verjetno zato, ker stolpec v tabeli» dejstvo «ni datumskega podatkovnega tipa. To se lahko zgodi, ko Power pivot ne more samodejno pretvoriti nedatuma (navadno besedilnega podatkovnega tipa) na podatkovni tip datum. Stolpec lahko še vedno uporabite v tabeli dejstev, vendar boste morali pretvoriti podatke s formulo DAX v nov izračunani stolpec. Glejte Pretvorba datumov besedilnih podatkov v polje» datumski podatkovni tip «pozneje v dodatku.

Več relacij

V nekaterih primerih bo morda treba ustvariti več relacij ali ustvariti več datumskih tabel. Če je na primer več datumskih polj v tabeli» Prodajna dejstva «, na primer poimenovali, kontrolnika ShipDate «in ReturnDate, imajo lahko relacije do polja» datum «v tabeli» datum koledarja «, vendar je le ena od teh lahko aktivna relacija. V tem primeru, ker poimenovali predstavlja datum transakcije in je zato najpomembnejši datum, bi bilo to najbolje kot aktivno razmerje. Drugi imajo nedejavne relacije.

Ta vrtilna tabela izračuna skupno prodajo po proRačunskem letu in fiskalnem četrtletju. Merilo z imenom» skupna prodaja «s formulo» Skupna prodaja «: = sum ([SalesAmount]), je postavljena v vrednosti in polja FiscalYear in FiscalQuarter iz tabele datumov koledarja so postavljena v vrstice.

Vrtilna tabela skupne prodaje po poslovnem četrtletju Seznam polj vrtilne tabele

To neposredno poročilo vrtilne tabele deluje pravilno, ker želimo izračunati skupno vsoto prodaje s datumom transakcije v poimenovali. Naš skupni prodajni ukrep uporablja datume v poimenovali in je povzeto s proračunskim letom in proračunskim četrtletjem, ker obstaja relacija med poimenovali v tabeli» Prodaja «in datumom» datum «v tabeli» datum koledarja «.

NeDejavni relacije

Kaj pa, če želimo izračunati skupno vsoto prodaje po datumu transakcije, vendar z datumom ladje? Potrebujemo relacijo med stolpcem kontrolnika ShipDate «v tabeli Prodaja in datumski stolpec v tabeli koledar. Če ne ustvarimo tega odnosa, naše združevanje vedno temelji na datumu transakcije. Vendar pa lahko imamo več relacij, čeprav je le ena lahko aktivna in ker je datum transakcije najpomembnejši, postane dejaven odnos s tabelo koledarja.

V tem primeru ima kontrolnika ShipDate «neaktivno relacijo, zato mora vsak ukrep, ki je bil ustvarjen za združevanje podatkov, ki temeljijo na datumih ladje, določiti neaktivno relacijo z uporabo funkcije USERELATIONSHIP .

Ker je na primer nedejaven odnos med stolpcem kontrolnika ShipDate «v tabeli» Prodaja «in v stolpcu» datum «v tabeli» koledar «, lahko ustvarimo merilo, ki sešteje skupno prodajo po datumu dobave. S formulo, kot je ta, določite relacijo, ki jo želite uporabiti:

Skupna prodaja po datumu dobave: = IZRAČUNAJ (SUM (prodaja [SalesAmount]), USERELATIONSHIP (prodaja [kontrolnika ShipDate «], koledar [datum]))

Ta formula preprosto navaja: izračun vsote za SalesAmount, vendar filtriranje s povezavo med stolpcem kontrolnika ShipDate «v tabeli» Prodaja «in z datumskim stolpcem v tabeli koledar.

Če ustvarimo vrtilno tabelo in položimo skupno prodajo po datumu dobave po vrednosti, in poslovno leto in poslovno četrtletje v VRSTICAh, vidimo isti skupni znesek, vendar so vsi drugi zneski zneskov za poslovno leto in poslovno četrtletje drugačni, ker temeljijo na datumu ladje in ni datum transakcije.

Vrtilna tabela s skupno prodajo po datumu pošiljke Seznam polj vrtilne tabele

Uporaba neaktivnih relacij omogoča, da uporabite le eno datumsko tabelo, vendar zahteva, da so vsi ukrepi (kot je na primer skupna prodaja po datumu dobave) sklic neaktivne relacije v formuli. Obstaja druga možnost, kar pomeni, da uporabite več datumskih tabel.

Več datumskih tabel

Drug način dela z več datumskimi stolpci v tabeli dejstev je ustvarjanje več datumskih tabel in ustvarjanje ločenih aktivnih relacij med njimi. Še enkrat si oglejmo naš primer prodaje v tabeli. Imamo tri stolpce z datumi, ki bi jih morda želeli združiti s podatki:

  • Poimenovali z datumom prodaje za vsako transakcijo.

  • Kontrolnika ShipDate «– z datumom in časom, ko so bile prodane elemente poslane kupcu.

  • ReturnDate – z datumom in časom, ko je bil prejet en ali več vrnjenih elementov.

ZaPomnite si, da je polje» poimenovali «z datumom transakcije najpomembnejše. Večino svojih spojitev bomo naredili na podlagi teh datumov, zato si bomo zagotovo želeli relacijo med njim in datumsko kolumno v tabeli koledar. Če ne želite ustvariti neaktivnih relacij med kontrolnika ShipDate «in ReturnDate in poljem» datum «v tabeli koledarja, s čimer se zahtevajo posebne formule za merjenje, lahko ustvarite dodatne datumske tabele za datum in datum dobave. Nato lahko ustvarimo aktivne relacije med njimi.

Relacije z več datumskimi tabelami v pogledu diagrama

V tem primeru smo ustvarili drugo datumsko tabelo z imenom ShipCalendar. To seveda pomeni tudi ustvarjanje dodatnih datumskih stolpcev in ker so ti stolpci v drugi datumski tabeli, jih želimo poimenovati na način, ki jih razlikuje od istih stolpcev v tabeli koledar. Na primer, ustvarili smo stolpce z imenom ShipYear, ShipMonth, ShipQuarter in tako dalje.

Če ustvarite vrtilno tabelo in pokažemo celoten prodajni ukrep v vrednosti in ShipFiscalYear ter ShipFiscalQuarter v VRSTICAh, vidimo enake rezultate, kot smo jih videli, ko smo ustvarili nedejaven odnos in posebno skupno prodajo z izračunanim poljem» datum dobave «.

Vrtilna tabela s skupno prodajo po datumu pošiljke s koledarjem pošiljke Seznam polj vrtilne tabele

Vsak od teh pristopov zahteva skrbno upoštevanje. Ko uporabljate več relacij z eno tabelo za datum, boste morda morali ustvariti posebne ukrepe, ki bodo nedejavne relacije uporabljali s funkcijo USERELATIONSHIP. Na drugi strani lahko ustvarjanje več datumskih tabel zmede na seznamu polj in ker imate več tabel v podatkovnem modelu, bo zahtevalo več pomnilnika. Preskusite, kaj vam najbolje ustreza.

Lastnost» datumska tabela «

Z lastnostjo» datumska tabela «so metapodatki, ki so potrebni za funkcije časovne inteligence, kot so TOTALYTD, PREVIOUSMONTH in DATESBETWEEN, pravilno delovali. Ko je izračun zagnan z eno od teh funkcij, mehanizem formule Power pivot ve, kam iti, da dobite želene datume.

Opozorilo: Če ta lastnost ni nastavljena, ukrepi, ki uporabljajo funkcije časovne inteligence DAX, morda ne bodo vrnili pravilnih rezultatov.

Ko nastavite lastnost datumska tabela, določite datumsko tabelo in datumski stolpec v podatkovnem tipu datum (dateTime).

Pogovorno okno »Označi kot datumsko tabelo«

Kako: nastavitev lastnosti» datumska tabela «

  1. V oknu PowerPivot izberite tabelo koledar .

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

  3. V pogovornem oknu» Označi kot datum «izberite stolpec z enoličnimi vrednostmi in vrsto datumskega podatka.

Delo s časom

Vse datumske vrednosti z datumskim podatkovnim tipom v Excelu ali strežniku SQL Server so pravzaprav število. Vključeno v to število so števke, ki se sklicujejo na čas. V številnih primerih je ta čas za vsako vrstico polnoč. Če je na primer polje» ključ datumačasa «v tabeli z dejstvom prodaje vrednosti, kot je 10/19/2010 12:00:00, to pomeni, da so vrednosti na dan ravni natančnosti. Če imajo vrednosti polja» ključ datumačasa «čas, na primer 10/19/2010 8:44:00, to pomeni, da so vrednosti na minutni ravni natančnosti. Vrednosti so lahko tudi na ravni natančnosti ure ali celo sekundah natančnosti. Raven natančnosti v časovni vrednosti bo pomembno vplivala na to, kako ustvarite datumsko tabelo in relacije med njim in vašo tabelo dejstev.

Ugotoviti morate, ali boste podatke združili na dnevno raven natančnosti ali na časovno raven natančnosti. Z drugimi besedami, morda želite uporabiti stolpce v datumski tabeli, kot so na primer Morning, popoldne ali ura, kot polja z datumom v vrstici, stolpcu ali območjih filtriranja vrtilne tabele.

Opomba: Dnevi so najmanjša časovna enota, ki jo lahko funkcije s časovnimi potrebami v sistemu DAX delujejo. Če vam ni treba delati s časovnimi vrednostmi, zmanjšajte natančnost podatkov, da uporabite dneve kot minimalno enoto.

Če želite združiti podatke na časovno raven, bo datumska tabela potrebovala datumski stolpec s časom, ki je vključen. In – dejstvo je, da bo treba datumski stolpec z eno vrstico za vsako uro, ali morda celo vsako minuto, vsak dan, za vsako leto v datumskem obsegu. To je zato, ker če želite ustvariti relacijo med stolpcem ključ datumačasa v tabeli» Fact «in v stolpcu» datum «v tabeli» datum «, morate imeti ujemajoče se vrednosti. Kot si lahko predstavljate, če ste vključili veliko let, lahko to naredite za zelo veliko datumsko tabelo.

Če želite v večini primerov združiti podatke le na dan. Z drugimi besedami, kot polja v vrsticah, stolpcih ali filtrirnih območjih vrtilne tabele boste uporabili stolpce, kot so na primer year, month, Week ali Day of Week. V tem primeru mora stolpec» datum «v tabeli» datum «vsebovati le eno vrstico za vsak dan v letu, kot smo jo opisali prej.

Če je v stolpcu» datum «raven natančnosti, vendar pa boste združevali le na dnevno raven, če želite ustvariti relacijo med tabelo» Fact «in tabelo» datum «, boste morda morali spremeniti tabelo» Fact «tako, da ustvarite nov stolpec, ki prireže vrednosti v datumu c. tolpca na dnevno vrednost. Z drugimi besedami pretvorite vrednost, kot je 10/19/2010 8:44:00 , do 10/19/2010 12:00:00 am. Nato lahko ustvarite relacijo med tem novim stolpcem in datumskim stolpcem v tabeli datum, ker se vrednosti ujemajo.

Oglejmo si primer. Na tej sliki je prikazan stolpec ključ datumačasa v tabeli» Prodaja dejstev «. Vse združevanja podatkov v tej tabeli morajo biti le na dan, in sicer tako, da uporabite stolpce v tabeli datum v koledarju, kot so leto, mesec, četrtletje itd. Čas, ki je vključen v vrednost, ni pomemben, le dejanski datum.

Stolpec »Ključ DatumaČasa«

Ker nam ni treba analizirati teh podatkov na časovno raven, ne potrebujemo stolpca» datum «v tabeli» datum koledarja «, da vključite eno vrstico za vsako uro in vsako minuto vsakega dne v vsakem letu. Datumski stolpec v tabeli» datum «je videti tako:

Datumski stolpec v dodatku Power Pivot

Če želite ustvariti relacijo med stolpcem ključ datumačasa v tabeli» Prodaja «in datumski stolpec v tabeli koledar, lahko ustvarite nov izračunani stolpec v tabeli» Prodaja Fact «in uporabite funkcijo TRUNC , da skrajšate datumsko in časovno vrednost v ključ datumačasa stolpec v datumsko vrednost, ki se ujema z vrednostmi v stolpcu» datum «v tabeli» koledar «. Naša formula je videti tako:

= TRUNC ([ključ datumačasa]; 0)

To nam daje nov stolpec (imenovan poimenovali) z datumom iz stolpca ključ datumačasa in čas 12:00:00 za vsako vrstico:

Stolpec »Ključ datuma«

Zdaj lahko ustvarite relacijo med tem novim (poimenovali) stolpcem in datumskim stolpcem v tabeli koledar.

V tabeli» Prodaja «lahko ustvarite izračunani stolpec, ki zmanjša časovno natančnost v stolpcu» ključ datumačasa «na urni ravni natančnosti. V tem primeru funkcija TRUNC ne bo delovala, vendar lahko še vedno uporabljamo druge funkcije datuma in ure DAX, da ekstrahiramo in znova združimo novo vrednost na uro natančnosti. Uporabite lahko formulo, kot je ta:

= DATE (YEAR ([ključ datumačasa]), MONTH ([ključ datumačasa]), dan ([ključ datumačasa])) + TIME (ura ([ključ datumačasa]); 0; 0)

Naš novi stolpec je videti tako:

Stolpec »Ključ DatumaČasa«

Če je naš datumski stolpec v tabeli» datum «na ravni natančnosti, lahko ustvarite relacijo med njimi.

Ustvarjanje datumov, ki so bolj uporabni

Veliko datumskih stolpcev, ki jih ustvarite v tabeli» datum «, je potrebnih za druga polja, vendar v analizi ni vse uporabne. Polje» poimenovali «v tabeli» prodaja «, ki smo ga označili in je prikazano v tem članku, je pomembno, ker je za vsako transakcijo zabeleženo, da je transakcija prikazana na določen datum in čas. Vendar pa z vidika analize in poročanja ni vse to uporabno, ker je ne moremo uporabiti kot polje vrstice, stolpca ali filtra v vrtilni tabeli ali poročilu.

Podobno je v našem primeru datumski stolpec v tabeli koledar zelo uporaben, kritičen, vendar ga ne morete uporabiti kot dimenzijo v vrtilni tabeli.

Če želite, da so tabele in stolpci čim bolj uporabni, in če želite, da se seznam polj poročila vrtilne tabele ali Power View lažje premikate, je pomembno, da skrijete nepotrebne stolpce iz orodij odjemalca. Morda boste želeli tudi skriti določene tabele. Prikazana tabela» prazniki «vsebuje praznične datume, ki so pomembni za določene stolpce v tabeli koledar, ne morete pa uporabljati stolpcev» datum «in» prazniki «v tabelah» prazniki «kot polja v vrtilni tabeli. Če želite, da se seznami polj lažje pomikajo, lahko skrijete celotno tabelo za praznike.

Še en pomemben vidik dela z datumi je poimenovanje konvencij. V Dodatku Power pivot lahko po želji poimenujete tabele in stolpce. Vendar ne pozabite, še posebej, če boste delovni zvezek delili z drugimi uporabniki, je za dobro poimenovanje mogoče poenostaviti prepoznavanje tabel in datumov, ne le na seznamu polj, temveč tudi v Dodatku Power pivot in v formulah DAX.

Ko imate datumsko tabelo v podatkovnem modelu, lahko začnete ustvarjati ukrepe, s katerimi boste lažje izkoristili podatke. Nekatere so lahko tako preproste, kot povzema vsote prodaje za tekoče leto, druge pa so lahko bolj zapletene, kjer morate filtrirati na določen obseg enoličnih datumov. Več informacij najdete v razdelku» mere «v funkciji Power pivot in časovnih podatkov.

Dodatek

Pretvorba datumov besedilnih podatkov v podatkovni tip» datum «

V nekaterih primerih je tabela dejstev s podatki o transakcijah morda vsebovala datume podatkovnega tipa» besedilo «. To pomeni, da je datum, ki je prikazan kot 2012 – 12 – 04T11:47:09, pravzaprav sploh ni datum ali pa vsaj ne morete razumeti vrste dodatka Power pivot. To je res samo besedilo, ki se glasi kot datum. Če želite ustvariti relacijo med datumskim stolpcem v tabeli» Fact «in z datumskim stolpcem v datumski tabeli, morata biti oba stolpca datumskega podatkovnega tipa.

Ko poskušate spremeniti vrsto podatkov za stolpec z datumi, ki so podatkovni tip Text, na podatkovni tip datum, lahko Power pivot prepozna datume in jih samodejno pretvori v podatkovni tip True. Če Power pivot ne more narediti pretvorbe podatkovnega tipa, se prikaže napaka neujemanja vrste.

Lahko pa še vedno pretvorite datume v podatkovni tip True Date. Ustvarite lahko nov izračunani stolpec in uporabite formulo DAX, če želite razčleniti leto, mesec, dan, čas itd. iz besedilnih nizov, nato pa ga znova združiti na način, v katerem se Power pivot lahko glasi kot pravi datum.

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

Stolpec »DatumČas« v tabeli dejstev.

Če pogledamo podatkovni tip v razdelku oblikovanje skupine Power pivot na zavihku» Osnovno «, vidimo, da je besedilni podatkovni tip.

Vrsta podatkov v traku

Ne moremo ustvariti relacije med stolpcem» DateTime «in datumskim stolpcem v tabeli» datum «, ker se podatkovni tipi ne ujemajo. Če poskušamo spremeniti podatkovni tip do datuma, dobimo napako neujemanja vrste:

Napaka zaradi neujemanja

V tem primeru Power pivot ni mogel pretvoriti podatkovnega tipa iz besedila v datum. Ta stolpec lahko še vedno uporabljamo, toda če ga želite dobiti v podatkovni tip TRUE, moramo ustvariti nov stolpec, ki razčleni besedilo in ga znova ustvari v Dodatku Power pivot.

ZaPomnite si, v razdelku delo s časom v tem članku; Če ni nujno, da je vaša analiza na časovno obdobje natančnosti, morate pretvoriti datume v tabelo» Facts «na dnevno raven natančnosti. S tem v mislih želimo, da so vrednosti v našem novem stolpcu na ravni dneva natančnosti (brez časa). Vrednosti v stolpcu» DateTime «lahko pretvorite v podatkovni tip datum in odstranite časovno raven natančnosti s to formulo:

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

To nam daje nov stolpec (v tem primeru imenovan datum). Power pivot celo zazna vrednosti za datume in samodejno nastavi podatkovni tip.

Stolpec »Datum« v tabeli z dejstvi

Če želimo ohraniti časovno raven natančnosti, preprosto podaljšamo formulo, da vključite ure, minute in sekunde.

= DATE (levo ([DateTime], 4), MID ([DateTime], 6, 2), MID ([DateTime], 9; 2)) +

ČAS (MID ([DateTime], 12, 2), MID ([DateTime], 15, 2), MID ([DateTime], 18; 2))

Zdaj, ko imamo datumski stolpec z datumskim podatkovnim tipom, lahko ustvarimo relacijo med njim in datumsko kolumno v datumu.

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

Sklici na izraze analize podatkov

Središče za vire Dax

Razširite poznavanje Officea
Oglejte si izobraževanje
Prvi dobite nove funkcije
Pridružite se programu Office Insider

Vam je bila informacija v pomoč?

Zahvaljujemo se vam za povratne informacije.

Zahvaljujemo se vam za povratne informacije. Videti je, da bi vam prišla prav pomoč enega od naših Officeovih agentov za podporo.

×