Prijavite se pomoću Microsofta
Prijavi se ili izradi račun.
Zdravo,
Odaberite drugi račun.
Imate više računa
Odaberite račun putem kojeg se želite prijaviti.

Tablice datuma u dodatku Power Pivot ključne su za pregledavanje i izračunavanje podataka tijekom vremena. U ovom se članku detaljno razumiju datumski tablice i način na koji ih možete stvoriti u dodatku Power Pivot. U ovom se članku posebice opisuje sljedeće:

  • Zašto je tablica datuma važna za pregledavanje i izračunavanje podataka prema datumima i vremenu.

  • Dodavanje tablice datuma u podatkovni model pomoću dodatka Power Pivot.

  • Stvaranje novih stupaca datuma kao što su Godina, Mjesec i Razdoblje u tablici datuma.

  • Stvaranje odnosa između tablica datuma i tablica činjenica.

  • Kako raditi s vremenom.

Ovaj je članak namijenjen korisnicima koji su novi u dodatku Power Pivot. No važno je već dobro razumjeti uvoz podataka, stvaranje odnosa te stvaranje izračunatih stupaca i mjera.

U ovom se članku ne opisuje kako koristiti DAX Time-Intelligence funkcije u formulama za mjerenje. Dodatne informacije o stvaranju mjera pomoću funkcija dax inteligencije vremena potražite u članku Inteligencija vremena u dodatku Power Pivot Excel.

Napomena: U dodatku Power Pivot nazivi "mjera" i "izračunato polje" sinonimi su. Mjeru naziva emo koristiti u cijelom ovom članku. Dodatne informacije potražite u članku Mjere u dodatku Power Pivot.

Sadržaj

Objašnjenje tablica datuma

Gotovo sva analiza podataka obuhvaća pregledavanje i usporedbu podataka tijekom datuma i vremena. Možete, primjerice, zbrojiti iznose prodaje za proteklo fiskalno tromjesečje, a zatim ih usporediti s drugim tromjesečjima ili možda želite izračunati završni saldo za račun na kraju mjeseca. U svakom od tih slučajeva koristite datume kao način grupiranje i agregaciju prodajnih transakcija ili salda za određeno vremensko razdoblje.

Power View izvješće

Zaokretna tablica ukupne prodaje po fiskalnom tromjesečju

Tablica datuma može sadržavati mnogo različitih prikaza datuma i vremena. Na primjer, tablica datuma često će imati stupce kao što su fiskalna godina, mjesec, tromjesečje ili razdoblje koje možete odabrati kao polja s popisa polja prilikom sjekanja i filtriranja podataka u zaokretnim tablicama ili Power View izvješćima.

Power View Popis polja

Popis polja programa Power View

Da bi stupci datuma, kao što su Godina, Mjesec i Tromjesečje, uvrstili sve datume unutar odgovarajućeg raspona, tablica datuma mora imati najmanje jedan stupac s skupom datuma. To jest, taj stupac mora imati jedan redak za svaki dan za svaku godinu uključenu u tablicu datuma.

Na primjer, ako podaci koje želite pregledati imaju datume od 1. veljače 2010. do 30. studenog 2012., a vi izvješće o kalendarskoj godini, tada ćete trebati tablicu datuma s rasponom datuma od 1. siječnja 2010. do 31. prosinca 2012. Svake godine u tablici datuma moraju sadržavati sve dane za svaku godinu. Ako ćete redovito osvježavati podatke novijim podacima, završni datum možete pokrenuti za godinu ili dvije, pa ne morate ažurirati tablicu datuma kako vrijeme prolazi.

Date table with a contiguous set of dates

Datumska tablica s neprekidnim datumima

Ako prijavite poslovnu godinu, možete stvoriti tablicu datuma s nesusmislenim skupom datuma za svaku fiskalnu godinu. Ako fiskalna godina, primjerice, počinje 1. ožujka, a imate podatke za fiskalne godine 2010. do trenutnog datuma (npr. u GJ 2013.), možete stvoriti tablicu datuma koja počinje 3. 1. 2009. i obuhvaća najmanje svaki dan u svakoj fiskalnoj godini do zadnjeg datuma fiskalne godine 2013.

Ako ćete izvješće prijaviti u kalendarskoj i fiskalnoj godini, ne morate stvarati zasebne tablice datuma. Jedna tablica datuma može obuhvaćati stupce za kalendarsku godinu, fiskalnu godinu, pa čak i trinaest četverotjedne kalendare. Važno je da vaša tablica datuma sadrži skup datuma za sve godine.

Dodavanje tablice datuma u podatkovni model

U podatkovni model možete dodati tablicu datuma na nekoliko načina:

  • Uvoz iz relacijske baze podataka ili iz drugog izvora podataka.

  • Stvorite tablicu datuma u Excel a zatim kopirajte ili povežite s novom tablicom u dodatku Power Pivot.

  • Uvoz iz trgovine Microsoft Azure Marketplace.

Pogledajmo sve ovo pobliћe.

Uvoz iz relacijske baze podataka

Ako neke podatke ili sve podatke uvezete iz podatkovnog skladišta ili druge vrste relacijske baze podataka, vjerojatno već postoji tablica datuma i odnosi između njih i ostalih podataka koje uvozite. Datumi i oblik vjerojatno će se podudarati s datumima u podacima o činjenicama, a datumi vjerojatno dobro počinju u prošlosti i idu daleko u budućnost. Tablica datuma koju želite uvesti može biti vrlo velika i sadrži raspon datuma izvan onoga što ćete morati uvrstiti u podatkovni model. Napredne značajke filtra čarobnjaka za uvoz tablica dodatka Power Pivot možete koristiti da biste selektivno odabrali samo datume i određene stupce koje su vam zaista potrebne. To može znatno smanjiti veličinu radne knjige i poboljšati performanse.

Čarobnjak za uvoz tablice

Dijaloški okvir čarobnjaka za uvoz tablice

U većini slučajeva nećete morati stvarati dodatne stupce kao što su fiskalna godina, tjedan, naziv mjeseca itd. jer će već postojati u uvezenoj tablici. No u nekim ćete slučajevima nakon uvoza tablice datuma u podatkovni model možda morati stvoriti dodatne stupce datuma, ovisno o određenoj potrebi izvješćivanja. Srećom, to je jednostavno učiniti pomoću DAX-a. Kasnije ćete saznati više o stvaranju polja tablice datuma. Svako je okruženje drugačije. Ako niste sigurni imaju li izvori podataka povezani datum ili tablicu kalendara, obratite se administratoru baze podataka.

Stvaranje tablice datuma u Excel

Tablicu datuma možete stvoriti u Excel a zatim je kopirati u novu tablicu u podatkovnom modelu. To je vrlo jednostavno učiniti i pruža vam mnogo fleksibilnosti.

Kada stvorite tablicu datuma u Excel, počinjete s jednim stupcem s nesuslim rasponom datuma. Zatim možete stvoriti dodatne stupce kao što su Godina, Tromjesečje, Mjesec, Fiskalna godina, Razdoblje itd. na radnom listu Excel pomoću formula programa Excel ili, nakon kopiranja tablice u podatkovni model, možete ih stvoriti kao izračunate stupce. Stvaranje dodatnih stupaca datuma u dodatku Power Pivot opisano je u odjeljku Dodavanje novih stupaca datuma u odjeljak Tablica datuma u nastavku ovog članka.

Upute: Stvaranje tablice datuma u Excel i kopiranje u podatkovni model

  1. U Excel praznom radnom listu u ćeliju A1upišite naziv zaglavlja stupca da biste prepoznali raspon datuma. Obično će to biti nešto kao što su Datum, DateTime ili DateKey.

  2. U ćeliju A2upišite datum početka. Primjerice, 1. 1. 2010.

  3. Kliknite ručicu za ispunu i povucite je prema dolje na broj retka koji sadrži završni datum. Primjerice, 31. 12. 2016.

    Stupac s datumima u programu Excel

  4. Odaberite sve retke u stupcu Datum (uključujući naziv zaglavlja u ćeliji A1).

  5. U grupi Stilovi kliknite Oblikuj kao tablicu, a zatim odaberite stil.

  6. U dijaloškom okviru Oblikovanje kao tablice kliknite U redu.

    Stupac s datumima u dodatku Power Pivot

  7. Kopirajte sve retke, uključujući zaglavlje.

  8. U dodatku Power Pivot na kartici Polazno kliknite Zalijepi.

  9. U odjeljku Zalijepi pretpregled > naziv tablice upišite naziv kao što su Datum ili Kalendar. Ostavite Potvrđeno Koristi prvi redak kao zaglavlja stupaca, a zatim kliknite U redu.

    Lijepljenje pretpregleda

    Nova tablica datuma (pod nazivom Kalendar u ovom primjeru) u dodatku Power Pivot izgleda ovako:

    Datumska tablica u dodatku Power Pivot

    Napomena: Povezanu tablicu možete stvoriti i pomoću značajke Dodaj u podatkovni model. No to radnu knjigu čini nepotrebnom velikom jer radna knjiga sadrži dvije verzije tablice datuma; jedan u Excel a drugi u dodatku Power Pivot.

Napomena: Datum naziva ključna je riječ u dodatku Power Pivot. Ako u datumu dodatka Power Pivot navedete naziv tablice koju stvorite, naziv tablice morat ćete priložiti jednim navodnicima u bilo kojoj DAX formulama koje se pozivaju na njega u argumentu. Sve ogledne slike i formule u ovom članku odnose se na tablicu datuma stvorenu u dodatku Power Pivot pod nazivom Kalendar.

Sada imate tablicu datuma u podatkovnom modelu. DaX možete dodati nove stupce datuma, kao što su Godina, Mjesec itd.

Dodavanje novih stupaca datuma u tablicu datuma

Tablica datuma s jednim stupcem datuma koja ima jedan redak za svaki dan za svaku godinu važna je za definiranje svih datuma u rasponu datuma. Potrebno je i stvoriti odnos između tablice činjenica i tablice datuma. No taj stupac s jednim datumom s jednim redom za svaki dan nije koristan prilikom analize po datumima u zaokretnoj tablici ili Power View izvješću. Želite da tablica datuma sadrži stupce koji vam pomažu da zbrajate podatke za raspon ili grupu datuma. Na primjer, možda želite zbrojiti iznose prodaje po mjesecu ili tromjesečju ili možete stvoriti mjeru koja izračunava rast iz godine u godinu. U svakom od tih slučajeva u tablici datuma potrebni su stupci godine, mjeseca ili tromjesečja koji omogućuju prikupljanje podataka za to razdoblje.

Ako ste uvezli tablicu datuma iz relacijskih izvora podataka, ona možda već sadrži različite vrste stupaca datuma koje želite. U nekim ćete slučajevima možda trebati izmijeniti neke od tih stupaca ili stvoriti dodatne stupce datuma. To se osobito odnosi na stvaranje vlastite tablice datuma u Excel i kopiranje u podatkovni model. Srećom, stvaranje novih stupaca datuma u dodatku Power Pivot vrlo je jednostavno uz funkcije datuma i vremena u daxu.

Savjet: Ako još niste radili s DAX-om, sjajno je mjesto za početak učenja uz QuickStart: Saznajte osnove dax-a u 30 minuta na Office.com.

Funkcije datuma i vremena za DAX

Ako ste ikad radili s funkcijama datuma i vremena u Excel formulama, vjerojatno ćete biti upoznati s funkcijama Datum i vrijeme. Premda su te funkcije slične njihovim kolegama u Excel, postoje neke važne razlike:

  • Funkcije DAX Date and Time koriste vrstu podataka datetime.

  • Vrijednosti iz stupca mogu preuzeti kao argument.

  • Mogu se koristiti za vraćanje i/ili rukovanje vrijednostima datuma.

Te se funkcije često koriste prilikom stvaranja prilagođenih stupaca datuma u tablici datuma, pa ih je važno razumjeti. Pomoću nekoliko tih funkcija stvorit ćemo stupce za godinu, tromjesečje, fiskalni mjesec i tako dalje.

Napomena: Funkcije datuma i vremena u DAX-u nisu iste kao funkcije inteligencije vremena. Saznajte više o inteligenciji vremena u dodatku Power Pivot u programu Excel 2013.

DAX obuhvaća sljedeće funkcije Datum i vrijeme:

U formulama možete koristiti i mnoge druge DAX funkcije. Mnoge ovdje opisane formule, primjerice, koriste matematičke i trigonometrijske funkcije kao što su MOD i TRUNC, Logičke funkcije kao što su IFi Tekstne funkcije kao što su FORMAT Da biste saznali više o drugim DAX funkcijama, pogledajte odjeljak Dodatni resursi u nastavku ovog članka.

Primjeri formula za kalendarsku godinu

U sljedećim se primjerima opisuju formule koje se koriste za stvaranje dodatnih stupaca u tablici datuma pod nazivom Kalendar. Jedan stupac pod nazivom Datum već postoji i sadrži raspon datuma od 1. 1. 2010. do 31. 12. 2016.

Godina

=YEAR([datum])

U ovoj formuli funkcija YEAR vraća godinu iz vrijednosti u stupcu Datum. Budući da je vrijednost u stupcu Datum vrste podataka datetime, funkcija YEAR zna kako vratiti godinu iz njega.

Stupac Godina

Mjesec

=MONTH([datum])

U ovoj formuli, baš kao i u funkciji YEAR, jednostavno pomoću funkcije MONTH možemo vratiti vrijednost mjeseca iz stupca Datum.

Stupac Mjesec

Tromjesečje

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

U ovoj formuli funkciju INT koristimo za vraćanje vrijednosti datuma kao cijeli broj. Argument koji navedemo za funkciju INT vrijednost je stupca Mjesec, dodajte 2, a zatim ga podijelite s 3 da biste dobili naše tromjesečje, od 1 do 4.

Stupac Tromjesečje

Naziv mjeseca

=FORMAT([datum];"mmmm")

U ovoj formuli da bismo dobili naziv mjeseca, pomoću funkcije FORMAT pretvaramo brojčanu vrijednost iz stupca Datum u tekst. Stupac Datum određujemo kao prvi argument, a zatim oblik; želimo da naziv mjeseca prikazuje sve znakove, pa koristimo "mmmm". Rezultat izgleda ovako:

Stupac Naziv mjeseca

Ako želimo vratiti naziv mjeseca skraćen na tri slova, u argumentu oblika koristili bismo "mmm".

Dan u tjednu

=FORMAT([datum];"ddd")

U ovoj formuli koristimo funkciju FORMAT da bismo dobili naziv dana. Budući da samo želimo skraćeni naziv dana, u argumentu oblikovanje određujemo "ddd".

Stupac Dan u tjednu
Primjer zaokretne tablice

Kada imate polja za datume kao što su Godina, Tromjesečje, Mjesec itd., možete ih koristiti u zaokretnoj tablici ili izvješću. Na sljedećoj se slici, primjerice, prikazuje polje IznosProdaje iz tablice Činjenica prodaje u vrijednostima VALUES te Godina i Tromjesečje iz tablice Dimenzije Kalendara u recima. IznosProdaje zbraja se za kontekst godine i tromjesečja.

Primjer zaokretne tablice

Primjeri formula za poslovnu godinu

Fiskalna godina

=IF([Mjesec]<= 6;[Godina];[Godina]+1)

U ovom primjeru fiskalna godina započinje 1. srpnja.

Ne postoji funkcija koja može izdvojiti fiskalnu godinu iz vrijednosti datuma jer se datumi početka i završetka fiskalne godine često razlikuju od datuma kalendarske godine. Da bismo dobili fiskalnu godinu, najprije koristimo funkciju IF da bismo provjerili je li vrijednost za Mjesec manja ili jednaka 6. U drugom argumentu, ako je vrijednost za Mjesec manja od ili jednaka 6, vratite vrijednost iz stupca Godina. Ako nije, vratite vrijednost iz godine i dodajte 1.

Stupac Fiskalna godina

Drugi način određivanja vrijednosti za kraj fiskalne godine jest stvaranje mjere koja jednostavno određuje mjesec. Primjerice, FYE:=6. Nakon toga možete referencu na naziv mjere na mjestu broja mjeseca. Na primjer, =IF([Mjesec]<=[FYE],[Godina],[Godina]+1). Time se omogućuje veća fleksibilnost prilikom pozivanja na kraj fiskalne godine u nekoliko različitih formula.

Fiskalni mjesec

=IF([Mjesec]<= 6, 6+[Mjesec], [Mjesec]- 6)

U ovoj formuli određujemo je li vrijednost za [Mjesec] manja od ili jednaka 6, a zatim uzmite 6 i dodajte vrijednost iz mjeseca, u suprotnom oduzmite 6 od vrijednosti od [Mjesec].

Stupac Fiskalni mjesec

Fiskalno tromjesečje

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

Formula koju koristimo za FiscalQuarter ista je kao i za tromjesečje u kalendarskoj godini. Jedina je razlika što određujemo [FiscalMonth] umjesto [Mjesec].

Stupac Fiskalno tromjesečje

Praznici ili posebni datumi

Možda želite uvrstiti stupac datuma koji označava da su određeni datumi praznici ili neki drugi posebni datum. Možete, primjerice, zbrojiti ukupne iznose prodaje za dan nove godine dodavanjem polja Praznik u zaokretnu tablicu, kao rezač ili filtar. U drugim slučajevima te datume možete izuzeti iz drugih stupaca datuma ili u mjeri.

Uključivanje blagdana ili posebnih dana vrlo je jednostavno. Tablicu možete stvoriti u Excel koja sadrži datume koje želite uvrstiti. Zatim možete kopirati ili koristiti dodaj u podatkovni model da biste ga dodali u podatkovni model kao povezanu tablicu. U većini slučajeva nije potrebno stvoriti odnos između tablice i tablice Kalendar. Sve formule koje se pozivaju na njega mogu koristiti funkciju LOOKUPVALUE za vraćanje vrijednosti.

U nastavku je prikazan primjer tablice stvorene u Excel koja obuhvaća praznike koje je potrebno dodati u tablicu datuma:

Datum

Praznik

1/1/2010

Nove godine

11/25/2010

Dan zahvalnosti

12/25/2010

Božić

1. 1. 2011.

Nove godine

11/24/2011

Dan zahvalnosti

12/25/2011

Božić

1.1.2012.

Nove godine

22.11.12.

Dan zahvalnosti

12/25/2012

Božić

1/1/2013

Nove godine

11/28/2013

Dan zahvalnosti

12/25/2013

Božić

11/27/2014

Dan zahvalnosti

12/25/2014

Božić

1. 1. 2014.

Nove godine

11/27/2014

Dan zahvalnosti

12/25/2014

Božić

1/1/2015

Nove godine

11/26/2014

Dan zahvalnosti

12/25/2015

Božić

01.01.16.

Nove godine

11/24/2016

Dan zahvalnosti

12/25/2016

Božić

U tablici datuma stvaramo stupac pod nazivom Praznik i koristimo formulu kao što je ova:

=LOOKUPVALUE(Praznici[Praznik],Praznici[datum],Kalendar[datum])

Pogledajmo ovu formulu pažljivije.

Funkciju LOOKUPVALUE koristimo za dohvaćanje vrijednosti iz stupca Praznik u tablici Praznici. U prvom argumentu određujemo stupac u kojem će biti vrijednost rezultata. U tablici Praznici određujemostupac Praznik jer je to vrijednost koju želimo vratiti.

=LOOKUPVALUE(Praznici[Praznik],Praznici[datum],Kalendar[datum])

Zatim određujemo drugi argument, stupac za pretraživanje koji sadrži datume koje želimo potražiti. Stupac Datum u tablici Praznici određujemo ovako:

=LOOKUPVALUE(Praznici[Praznik],Praznici[datum],Kalendar[datum])

Naposljetku, u tablici Kalendar navodimo stupac s datumima koje želimo potražiti u tablici Praznik. To je, naravno, stupac Datum u tablici Kalendar.

=LOOKUPVALUE(Praznici[Praznik],Praznici[datum],Kalendar[datum])

Stupac Praznik vratit će naziv praznika za svaki redak koji ima vrijednost datuma koja odgovara datumu u tablici Praznici.

Tablica Blagdani

Prilagođeni kalendar – trinaest razdoblja od četiri tjedna

Neke tvrtke ili ustanove, kao što su maloprodaja ili servis za hranu, često izvješćuju o različitim razdobljima, npr. trinaest razdoblja od četiri tjedna. Uz trinaest četverotjesni kalendar, svako razdoblje je 28 dana; stoga svako razdoblje sadrži četiri ponedjeljka, četiri utorka, četiri srijede i tako dalje. Svako razdoblje sadrži isti broj dana, a praznici će obično biti u istom razdoblju svake godine. Razdoblje možete započeti bilo kojim danom u tjednu. Baš kao i s datumima u kalendaru ili fiskalnoj godini, dax možete koristiti za stvaranje dodatnih stupaca s prilagođenim datumima.

U primjerima u nastavku prvo puno razdoblje započinje prvom nedjeljom fiskalne godine. U tom slučaju fiskalna godina počinje 1. 7. 2011.

Tjedan

Ta nam vrijednost daje broj tjedna počevši od prvog punog tjedna fiskalne godine. U ovom primjeru prvi cijeli tjedan započinje nedjeljom, pa prvi cijeli tjedan u prvoj fiskalnoj godini u tablici Kalendar zapravo počinje 4. 7. 2010. i nastavlja se tijekom zadnjeg punog tjedna u tablici Kalendar. Premda sama ta vrijednost nije korisna u analizi, potrebno je izračunati za korištenje u drugim formulama razdoblja od 28 dana.

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

Pogledajmo ovu formulu pažljivije.

Najprije stvaramo formulu koja vraća vrijednosti iz stupca Datum kao cijeli broj, ovako:

=INT([datum])

Zatim želimo tražiti prvu nedjelju u prvoj fiskalnoj godini. Vidimo da je 7. 4. 2010.

Stupac Tjedan

Sada oduzmite 40356 (što je cijeli broj za 27.6.2010., zadnju nedjelju iz prethodne fiskalne godine) od te vrijednosti da biste dobili broj dana od početka dana u tablici Kalendar, ovako:

=INT([datum]-40356)

Zatim podijelite rezultat sa 7 (dana u tjednu), ovako:

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

Rezultat izgleda ovako:

Stupac Tjedan

Točka

Razdoblje u ovom prilagođenom kalendaru sadrži 28 dana i uvijek će početi nedjeljom. Taj će stupac vratiti broj razdoblja počevši od prve nedjelje u prvoj fiskalnoj godini.

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

Pogledajmo ovu formulu pažljivije.

Najprije stvaramo formulu koja vraća vrijednost iz stupca Tjedan kao cijeli broj, ovako:

=INT([Tjedan])

Zatim u tu vrijednost dodajte 3, ovako:

=INT([Tjedan]+3)

Zatim podijelite rezultat s 4, ovako:

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

Rezultat izgleda ovako:

Stupac Razdoblje

Razdoblje fiskalne godine

Ta vrijednost vraća fiskalnu godinu za razdoblje.

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

Pogledajmo ovu formulu pažljivije.

Najprije stvaramo formulu koja vraća vrijednost iz razdoblja i dodaje 12:

= ([Razdoblje]+12)

Rezultat dijelimo s 13 jer u fiskalnoj godini postoji trinaest 28 dana:

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

Dodajemo 2010 jer je to prva godina u tablici:

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

Na kraju funkciju INT koristimo za uklanjanje bilo kojeg dijela rezultata i vraćanje cijelog broja, kada je podijeljeno s 13, ovako:

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

Rezultat izgleda ovako:

Stupac Razdoblje fiskalne godine

Razdoblje u fiskalnoj godini

Ta vrijednost vraća broj razdoblja, 1 – 13, počevši od prvog punog razdoblja (počevši od nedjelje) u svakoj fiskalnoj godini.

=IF(MOD([Razdoblje];13), MOD([Razdoblje];13),13)

Formula je malo složenija, pa ćemo je najprije opisati na jeziku koji bolje razumijemo. Ova formula navodi, podijelite vrijednost s [Točka] s 13 da biste dobili broj razdoblja (1- 13) u godini. Ako je taj broj 0, vratite 13.

Najprije stvaramo formulu koja vraća ostatak vrijednosti iz razdoblja od 13. Mod (matematičke i trigonometrijske funkcije) možemo koristiti ovako:

=MOD([Razdoblje];13)

To nam, u većini slučajeva, daje rezultat koji želimo, osim ako je vrijednost za razdoblje 0 jer ti datumi ne spadaju u prvu fiskalnu godinu, kao u prvih pet dana ogledne tablice datuma kalendara. To možemo učiniti pomoću funkcije IF. U slučaju da je rezultat 0, vratit ćemo 13, ovako:

=IF(MOD([Razdoblje];13);MOD([Razdoblje];13),13)

Rezultat izgleda ovako:

Stupac Razdoblje u fiskalnoj godini

Primjer zaokretne tablice

Na slici u nastavku prikazana je zaokretna tablica s poljem IznosProdaje iz tablice Činjenica prodaje u vrijednostima VALUES i Polja PeriodFiscalYear i PeriodInFiscalYear iz tablice Dimenzija datuma kalendara u recima. IznosProdaje zbraja se za kontekst prema fiskalnoj godini i razdoblju od 28 dana u fiskalnoj godini.

Primjer zaokretne tablice za fiskalnu godinu

Odnosi

Kada stvorite tablicu datuma u podatkovnom modelu, da biste počeli pregledavati podatke u zaokretnim tablicama i izvješćima te zbrajali podatke na temelju stupaca u tablici dimenzije datuma, morate stvoriti odnos između tablice činjenica s podacima o transakcijama i tablice datuma.

Budući da morate stvoriti odnos na temelju datuma, morate stvoriti taj odnos između stupaca čije su vrijednosti vrste podataka datetime (Date).

Za svaku vrijednost datuma u tablici činjenica povezani stupac s vrijednostima u tablici datuma mora sadržavati podudarne vrijednosti. Na primjer, redak (zapis o transakciji) u tablici Činjenica prodaje s vrijednošću 8/15/2012 12:00 AM u stupcu DateKey mora imati odgovarajuću vrijednost u povezanom stupcu Datum u tablici datuma (pod nazivom Kalendar). To je jedan od najvažnijih razloga zbog kojih želite da stupac datuma u tablici datuma sadrži raspon datuma koji obuhvaća sve moguće datume u tablici činjenica.

Odnosi u prikazu dijagrama

Napomena: Premda stupac datuma u svakoj tablici mora biti iste vrste podataka (Datum), oblik svakog stupca nije važan..

Napomena: Ako Power Pivot ne dopusti stvaranje odnosa između dviju tablica, polja datuma možda neće pohraniti datum i vrijeme na istu razinu preciznosti. Ovisno o oblikovanju stupca, vrijednosti mogu izgledati isto, ali se pohranjuju drugačije. Saznajte više o radu s vremenom.

Napomena: Izbjegavajte korištenje zamjenskih ključeva cijelih brojeva u odnosima. Kada uvozite podatke iz relacijskih izvora podataka, često stupci datuma i vremena predstavljaju zamjenski ključ, koji je cijeli stupac koji se koristi za predstavljanje jedinstvenog datuma. U dodatku Power Pivot trebali biste izbjegavati stvaranje odnosa pomoću cijelih tipki datuma/vremena, a umjesto toga koristite stupce koji sadrže jedinstvene vrijednosti s vrstom podataka datuma. Iako se korištenje zamjenskih ključeva smatra najboljim primjerom u tradicionalnim podatkovnim skladištima, cijeli ključevi nisu potrebni u dodatku Power Pivot i mogu otežati grupiranje vrijednosti u zaokretnim tablicama po različitim datumima.

Ako vam se prilikom pokušaja stvaranja odnosa prikazuje pogreška neusklađenost vrste, to je vjerojatno zato što stupac u tablici činjenica nije vrste podataka Datum. To se može dogoditi kada Power Pivot ne može automatski pretvoriti datum (obično tekstnu vrstu podataka) u vrstu podataka datuma. Stupac u tablici činjenica i dalje možete koristiti, ali podatke ćete morati pretvoriti pomoću DAX formule u novom izračunatom stupcu. Pogledajte pretvorbu datuma vrste podataka teksta u vrstu podataka datuma u nastavku dodatka.

Višestruki odnosi

U nekim slučajevima možda će biti potrebno stvoriti više odnosa ili stvoriti više tablica datuma. Ako, primjerice, u tablici Činjenice o prodaji postoji više polja datuma, kao što su DatumKljuč, DatumProdaje i DatumProdaje, svi mogu imati odnose s poljem Datum u tablici Datum kalendara, ali samo jedan od njih može biti aktivan odnos. U tom slučaju, budući da DateKey predstavlja datum transakcije, a time i najvažniji datum, to bi najbolje bilo da služi kao aktivan odnos. Ostali imaju neaktivne odnose.

Sljedeća zaokretna tablica izračunava ukupnu prodaju po fiskalnoj godini i fiskalnom tromjesečju. Mjera pod nazivom Ukupna prodaja, s formulom Ukupna prodaja:=SUM([IznosProdaje]), nalazi se u poljima VRIJEDNOSTI, a polja Fiskalna Godina i Fiskalni Tromjesečje iz tablice Datum kalendara smještaju se u ROWS.

Zaokretna tablica ukupnog iznosa prodaje za fiskalno tromjesečje Popis polja zaokretne tablice

Ta zaokretna tablica s ravnim prosljeđivanjem funkcionira pravilno jer želimo zbrojiti ukupnu prodaju prema datumu transakcijeu datumu DateKey. Naša mjera Ukupna prodaja koristi datume u datumuKljuč i sažeta je fiskalnom godinom i fiskalnim tromjesečjem jer postoji odnos između tipke DateKey u tablici Prodaja i stupca Datum u tablici Datum kalendara.

Neaktivni odnosi

No što ako želimo zbrojiti ukupnu prodaju ne prema datumu transakcije, već prema datumu otpreme? Potreban nam je odnos između stupca DatumProdaje u tablici Prodaja i stupca Datum u tablici Kalendar. Ako ne stvorimo taj odnos, naše se agregacije uvijek temelje na datumu transakcije. No možemo imati više odnosa, iako samo jedan može biti aktivan, a budući da je datum transakcije najvažniji, on dobiva aktivan odnos s tablicom Kalendar.

U tom slučaju DatumDatoteka ima neaktivan odnos, pa svaka formula mjere stvorena radi zbrajanja podataka na temelju datuma otpreme mora navesti neaktivan odnos pomoću funkcije USERELATIONSHIP.

Na primjer, budući da postoji neaktivan odnos između stupca DatumProdaje u tablici Prodaja i stupca Datum u tablici Kalendar, možemo stvoriti mjeru koja zbraje ukupnu prodaju prema datumu otpreme. Formulu kao što je ova koristimo da bismo odredili odnos koji će se koristiti:

Ukupna prodaja po datumu otpreme:=CALCULATE(SUM(Prodaja[IznosProdaje]), USERELATIONSHIP(Prodaja[DatumProdaje], Kalendar[Datum]))

Ova formula jednostavno navodi: izračunajte zbroj za IznosProdaje, ali filtrirajte pomoću odnosa između stupca DatumProdaje u tablici Prodaja i stupca Datum u tablici Kalendar.

Sada, ako stvorimo zaokretnu tablicu i mjeru Ukupna prodaja prema datumu otpreme u vrijednosti, a fiskalna godina i fiskalno tromjesečje u recima, vidjet ćemo isti ukupni zbroj, ali svi ostali iznosi zbroja za fiskalnu godinu i fiskalno tromjesečje razlikuju se jer se temelje na datumu otpreme, a ne na datumu transakcije.

Zaokretna tablica ukupnog iznosa prodaje po datumu isporuke Popis polja zaokretne tablice

Korištenje neaktivnih odnosa omogućuje korištenje samo jedne tablice datuma, ali zahtijeva da se sve mjere (npr. Ukupna prodaja prema datumu otpreme) pozivaju na neaktivan odnos u formuli. Postoji još jedna alternativa, to jest, korištenje više tablica datuma.

Više tablica datuma

Drugi način rada s više stupaca datuma u tablici činjenica jest stvaranje više tablica datuma i stvaranje zasebnih aktivnih odnosa između njih. Pogledajmo ponovno primjer tablice Prodaja. Imamo tri stupca s datumima na koje bismo mogli agregirati podatke:

  • DateKey s datumom prodaje za svaku transakciju.

  • DatumOtpreme – s datumom i vremenom kada su prodani artikli otpremljeni kupcu.

  • ReturnDate – s datumom i vremenom kada je primljena jedna ili više vraćenih stavki.

Zapamtite da je najvažnije polje DateKey s datumom transakcije. Većinu naših agregacija radimo na temelju tih datuma, pa ćemo zasigurno želeti odnos između njega i stupca Datum u tablici Kalendar. Ako u tablici Kalendar ne želimo stvarati neaktivne odnose između datuma i datuma otpreme i polja Datum u tablici Kalendar, što zahtijeva formule posebne mjere, možemo stvoriti dodatne tablice datuma za datum otpreme i datum vraćanja. Zatim možemo stvoriti aktivne odnose između njih.

Odnosi s više tablica s datumima u prikazu dijagrama

U ovom smo primjeru stvorili drugu tablicu datuma pod nazivom ShipCalendar. To, naravno, znači i stvaranje dodatnih stupaca datuma, a budući da se ti stupci datuma nalaze u drugoj tablici datuma, želimo im dati naziv na način koji ih razlikuje od istih stupaca u tablici Kalendar. Na primjer, stvorili smo stupce pod nazivom ShipYear, ShipMonth, ShipQuarter i tako dalje.

Ako stvorimo zaokretnu tablicu i mjeru Ukupne prodaje stavimo u VRIJEDNOSTI i ShipFiscalYear i ShipFiscalQuarter u rows, vidjet ćemo iste rezultate koje smo vidjeli kada smo stvorili neaktivan odnos i posebno izračunato polje Datum prodaje po otpremi.

Zaokretna tablica Ukupni iznos prodaje po datumu isporuke s kalendarom isporuke Popis polja zaokretne tablice

Svaki od tih pristupa zahtijeva pažljivo razmatranje. Kada koristite više odnosa s jednom tablicom datuma, možda ćete morati stvoriti posebne mjere koje provoze neaktivne odnose pomoću funkcije USERELATIONSHIP. S druge strane, stvaranje više tablica datuma može biti zbunjujuće na popisu polja, a budući da u podatkovnom modelu imate više tablica, potrebno je više memorije. Eksperimentirajte s ono što vam najbolje odgovara.

Svojstvo Date Table

Svojstvo Datum tablica postavlja metapodatke potrebne za Time-Intelligence funkcije kao što su TOTALYTD, PREVIOUSMONTH i DATESBETWEEN da bi ispravno funkcionirale. Kada se izračun pokrene pomoću neke od tih funkcija, modul formule dodatka Power Pivot zna gdje treba otići da bi dobio potrebne datume.

Upozorenje: Ako to svojstvo nije postavljeno, mjere koje koriste DAX Time-Intelligence možda neće vratiti točne rezultate.

Kada postavite svojstvo Tablica datuma, u njemu navedite tablicu datuma i stupac datuma vrste podataka Datum (datum i vrijeme).

Dijaloški okvir Označavanje u obliku tablice s datumima

Upute: postavljanje svojstva Datum tablice

  1. U PowerPivot odaberite tablicu Kalendar.

  2. Na kartici Dizajn kliknite Označi kao tablicu datuma.

  3. U dijaloškom okviru Označi kao tablicu datuma odaberite stupac s jedinstvenim vrijednostima i vrstu podataka Datum.

Rad s vremenom

Sve vrijednosti datuma s vrstom podataka Datum u Excel ili SQL Server zapravo su broj. U taj se broj nalaze znamenke koje se odnose na vrijeme. U mnogim je slučajevima vrijeme za svaki redak ponoć. Ako, primjerice, polje DateTimeKey u tablici Činjenica prodaje sadrži vrijednosti kao što su 10/19/2010 12:00:00 AM, to znači da su vrijednosti na razinu točnosti dana. Ako su vrijednosti polja DateTimeKey obuhvaćene, primjerice, 10.19.2010. 8.44.00 am, to znači da su vrijednosti na razini minuta preciznosti. Vrijednosti mogu biti precizne na razini sata ili čak u sekundama preciznosti. Razina preciznosti u vremenskoj vrijednosti znatno će utjecati na način stvaranja tablice datuma i odnosa između te vrijednosti i tablice činjenica.

Morate odrediti hoćete li zbrojeti podatke na razinu točnosti dana ili na razinu preciznosti vremena. Drugim riječima, možda ćete u područjima redaka, stupca ili filtra zaokretne tablice koristiti stupce u tablici datuma, kao što su Polja datuma ujutro, Poslijepodne ili Sat.

Napomena: Dani su najmanja jedinica vremena s 2 funkcije DAX inteligencije vremena. Ako ne morate raditi s vrijednostima vremena, trebali biste smanjiti preciznost podataka da biste dane koristili kao minimalnu jedinicu.

Ako podatke namjeravate agregirati na razinu vremena, u tablici datuma bit će potreban stupac datuma s uključenim vremenom. Zapravo, bit će mu potreban stupac datuma s jednim redom za svaki sat ili možda čak i svaku minutu, svakog dana, za svaku godinu u rasponu datuma. To je zato što da biste stvorili odnos između stupca DateTimeKey u tablici činjenica i stupca datuma u tablici datuma, morate imati podudarne vrijednosti. Kao što možete zamisliti, ako uvrstite mnogo godina, to može biti vrlo velika tablica datuma.

No u većini slučajeva podatke želite objediniti samo na dan. Drugim riječima, stupce kao što su Godina, Mjesec, Tjedan ili Dan u tjednu koristit ćete kao polja u područjima redaka, stupca ili filtra zaokretne tablice. U tom slučaju stupac datuma u tablici datuma mora sadržavati samo jedan redak za svaki dan u godini, kao što smo prethodno opisali.

Ako stupac datuma sadrži razinu preciznosti vremena, ali zbrojiti ćete samo razinu dana da biste stvorili odnos između tablice činjenica i tablice datuma, možda ćete morati izmijeniti tablicu činjenica stvaranjem novog stupca koji stiša vrijednosti u stupcu datuma na vrijednost dana. Drugim riječima, pretvorite vrijednost kao što je 19.10.10.8.44.00 u 19.10.2010. 12.00.00. Zatim možete stvoriti odnos između tog novog stupca i stupca datuma u tablici datuma jer se vrijednosti podudaraju.

Pogledajmo primjer. Na ovoj se slici prikazuje stupac DateTimeKey u tablici Činjenica prodaje. Sve agregacije za podatke u ovoj tablici moraju biti samo na razinu dana pomoću stupaca u tablici Datum kalendara kao što su Godina, Mjesec, Tromjesečje itd. Vrijeme obuhvaćeno vrijednošću nije relevantno, samo stvarni datum.

Stupac DatumskoVremenskiKljuč

Budući da ne moramo analizirati te podatke na vremensku razinu, ne treba nam stupac Datum u tablici Datum kalendara da bismo uvrstili jedan redak za svaki sat i svaku minutu svakog dana u svakoj godini. Stupac Datum u tablici datuma izgleda ovako:

Stupac s datumima u dodatku Power Pivot

Da biste stvorili odnos između stupca DateTimeKey u tablici Prodaja i stupca Datum u tablici Kalendar, možemo stvoriti novi izračunati stupac u tablici Činjenica prodaje i pomoću funkcije TRUNC vrijednost datuma i vremena u stupcu DateTimeKey možete sniti u vrijednost datuma koja se podudara s vrijednostima u stupcu Datum u tablici Kalendar. Formula izgleda ovako:

=TRUNC([DateTimeKey];0)

To nam daje novi stupac (nazvali smo DateKey) s datumom iz stupca DateTimeKey i vremenom od 12:00:00 za svaki redak:

Stupac DatumskiKljuč

Sada možemo stvoriti odnos između tog novog stupca (DateKey) i stupca Datum u tablici Kalendar.

Slično tome, u tablici Prodaja možemo stvoriti izračunati stupac koji smanjuje preciznost vremena u stupcu DateTimeKey na razinu preciznosti sata. U tom slučaju funkcija TRUNC neće funkcionirati, ali i dalje možemo koristiti druge funkcije DAX Date and Time da bismo izdvojili i ponovno uvrstili novu vrijednost u razinu točnosti sata. Formulu možemo koristiti ovako:

= DATE (YEAR([DateTimeKey]), MONTH([DateTimeKey]), DAY([DateTimeKey]) ) + TIME (HOUR([DateTimeKey]), 0, 0)

Naš novi stupac izgleda ovako:

Stupac DatumskoVremenskiKljuč

Pod uvjetom da stupac Datum u tablici datuma ima vrijednosti do razine preciznosti sata, možemo stvoriti odnos između njih.

Stvaranje datuma što je više moguće

Mnogi stupci datuma koje stvorite u tablici datuma nužni su za druga polja, ali zapravo nisu svi korisni u analizi. Na primjer, polje DateKey u tablici Prodaja na koju smo se odnosili i prikazano u cijelom ovom članku važno je jer se za svaku transakciju ta transakcija bilježi kao da se odvija na određeni datum i vrijeme. No iz analitičkog i izvještajnog prikaza to nije toliko korisno jer ga ne možemo koristiti kao polje retka, stupca ili filtra u zaokretnoj tablici ili izvješću.

Slično tome, u našem je primjeru stupac Datum u tablici Kalendar vrlo koristan, zapravo ključan, ali ga ne možete koristiti kao dimenziju u zaokretnoj tablici.

Da bi tablice i stupci u njima bili što korisniji te da biste olakšali navigaciju popisima polja zaokretne tablice ili Power View izvješća, važno je sakriti nepotrebne stupce iz klijentskih alata. Možete i sakriti određene tablice. Prethodno prikazana tablica Praznici sadrži datume praznika koji su važni za određene stupce u tablici Kalendar, ali stupce Datum i praznik u tablici Praznici ne možete koristiti kao polja u zaokretnoj tablici. Da biste se lakše kretali popisima polja, možete sakriti cijelu tablicu Praznici.

Još jedan važan aspekt rada s datumima jest konvencija imenovanja. Tablice i stupce u dodatku Power Pivot možete nazivati što god želite. No imati na umu, osobito ako radnu knjigu zajednički koristite s drugim korisnicima, dobra konvencija imenovanja olakšava prepoznavanje tablica i datuma, ne samo u popisima polja, već i u dodatku Power Pivot i u DAX formulama.

Kada u podatkovnom modelu u podatkovnom modelu imate tablicu datuma, možete početi stvarati mjere koje će vam pomoći da što više možete koristiti podatke. Neki mogu biti jednostavniji od zbrojeva prodaje za trenutnu godinu, a drugi složeniji, gdje morate filtrirati određeni raspon jedinstvenih datuma. Saznajte više u mjerama u dodatku Power Pivot ifunkcijama inteligencije vremena.

Dodatak

Pretvaranje datuma vrste podataka teksta u vrstu podataka datuma

U nekim slučajevima tablica činjenica s podacima o transakcijama može sadržavati datume vrste tekstnih podataka. To jest, datum koji se prikazuje kao 2012-12-04T11:47:09 zapravo nije uopće datum ili barem ne može razumjeti vrstu datuma koju Power Pivot može razumjeti. To je zapravo samo tekst koji čita kao datum. Da biste stvorili odnos između stupca datuma u tablici činjenica i stupca datuma u tablici datuma, oba stupca moraju biti vrste podataka Datum.

Kada pokušate promijeniti vrstu podataka za stupac datuma koji su vrsta podataka teksta u vrstu podataka datuma, Power Pivot može automatski protumačiti datume i pretvoriti ih u vrstu podataka true date. Ako Power Pivot ne može napraviti pretvorbu vrste podataka, prikazuje se pogreška neusklađenost vrste.

No i dalje možete pretvoriti datume u vrstu podataka true date. Možete stvoriti novi izračunati stupac i pomoću DAX formule raščlaniti godinu, mjesec, dan, vrijeme itd. iz tekstnih nizova, a zatim ga ponovno spojiti na način da Power Pivot može čitati kao pravi datum.

U ovom smo primjeru uvezli tablicu činjenica pod nazivom Prodaja u Power Pivot. Sadrži stupac pod nazivom DateTime. Vrijednosti se pojavljuju ovako:

Stupac DatumVrijeme u tablici činjenice.

Ako u grupi Oblikovanje u grupi Polazno u dodatku Power Pivot gledamo vrstu podataka, vidjet ćemo da je to vrsta podataka Tekst.

Vrsta podatka na vrpci

Ne možemo stvoriti odnos između stupca DateTime i stupca Datum u tablici datuma jer se vrste podataka ne podudaraju. Ako vrstu podataka pokušamo promijeniti u Datum,prikazuje se pogreška neusklađenost vrste:

Pogreška nepodudaranja

U tom slučaju Power Pivot nije mogao pretvoriti vrstu podataka iz teksta u datum. I dalje možemo koristiti taj stupac, ali da bismo ga uvrstili u vrstu podataka true date, moramo stvoriti novi stupac koji raščlanjuje tekst i ponovno ga stvara u vrijednost Power Pivot može stvoriti vrstu podataka Datum.

Sjećate se, iz odjeljka Rad s vremenom u prethodnom odjeljku ovog članka; osim ako analiza nije nužna na razini preciznosti za vrijeme dana, trebali biste pretvoriti datume u tablici činjenica u razinu točnosti dana. Imajte na umu da želimo da vrijednosti u novom stupcu bude na razini dana preciznosti (isključujući vrijeme). Vrijednosti u stupcu DateTime možemo pretvoriti u vrstu podataka datuma i ukloniti razinu preciznosti vremena pomoću sljedeće formule:

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

To nam daje novi stupac (u ovom slučaju pod nazivom Datum). Power Pivot čak otkriva vrijednosti koje su datumi i automatski postavlja vrstu podataka na Datum.

Stupac Datum u tablici s činjenicama

Ako želimo zadržati razinu preciznosti vremena, formulu jednostavno proširujemo tako da obuhvaća sate, minute i 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))

Sada kada imamo stupac Datum vrste podataka Datum, možemo stvoriti odnos između njega i stupca datuma u datumu.

Dodatni resursi

Datumi u dodatku Power Pivot

Zbrajanja u dodatku Power Pivot

Brzi početak rada: naučite osnove DAX-a za 30 minuta

Referenca izraza za analizu podataka

DAX Resource Center

Potrebna vam je dodatna pomoć?

Želite dodatne mogućnosti?

Istražite pogodnosti pretplate, pregledajte tečajeve za obuku, saznajte kako zaštititi uređaj i još mnogo toga.

Zajednice vam pomažu da postavljate pitanja i odgovarate na njih, pošaljete povratne informacije i čujete se sa stručnjacima s bogatim znanjem.

Jesu li vam ove informacije bile korisne?

Koliko ste zadovoljni jezičnom kvalitetom?
Što je utjecalo na vaše iskustvo?
Ako pritisnete Pošalji, vaše će se povratne informacije iskoristiti za poboljšanje Microsoftovih proizvoda i usluga. Vaš će IT administrator moći prikupiti te podatke. Izjava o zaštiti privatnosti.

Hvala vam na povratnim informacijama!

×