Tablice s datumima u dodatku Power Pivot u programu Excel i njihovo stvaranje

Tablice datuma u dodatku Power Pivot ključne su za pregledavanje i izračunavanje podataka tijekom vremena. U ovom se članku detaljno objašnjava tablice datuma i upute za njihovo stvaranje u dodatku Power Pivot. U ovom se članku konkretno opisuje:

• Zašto je tablica datuma važna za pregledavanje i izračunavanje podataka prema datumima i vremenu.
• Upute za dodavanje tablice datuma u podatkovni model pomoću dodatka Power Pivot.
• Kako u tablici datuma stvoriti nove stupce s datumima, kao što su godina, mjesec i razdoblje.
• Kako stvoriti odnose između tablica datuma i tablica činjenica.
• Kako raditi s vremenom.

Ovaj je članak namijenjen korisnicima koji tek počinju koristiti 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 način korištenja funkcija jezika DAX Time-Intelligence u formulama za mjerenje. Dodatne informacije o stvaranju mjera pomoću DAX funkcija inteligencije vremena potražite u članku Obavještavanje o vremenu u dodatku Power Pivot u programu Excel.

Sadržaj

• Tablice datuma

• Dodavanje tablice datuma u podatkovni model

  • Uvoz iz relacijske baze podataka

  • Stvaranje tablice datuma u programu Excel

    • Upute: stvaranje tablice datuma u programu Excel i njezino kopiranje u podatkovni model

• Dodavanje novih stupaca s datumima u tablicu datuma

  • DAX funkcije datuma i vremena

  • Primjeri formula za kalendarsku godinu

    • Godina
    • Mjesec
    • Tromjesečje
    • Naziv mjeseca

  • Primjeri formula za fiskalnu godinu

    • Fiskalna godina
    • Fiskalni mjesec
    • Fiskalno tromjesečje

• praznici ili posebni datumi

• Prilagođeni kalendar – trinaest razdoblja od četiri tjedna

  • Tjedan
  • Točka
  • Razdoblje fiskalne godine
  • Razdoblje u fiskalnoj godini

• Odnosi

  • Više odnosa
  • Neaktivni odnosi
  • Više tablica datuma

• Svojstvo Tablica datuma

• Rad s vremenom

• Upotrebljivost datuma

• Dodatak

  • Pretvaranje datuma tekstualne vrste podataka u vrstu podataka datuma
  • Dodatni resursi

Tablice datuma

Gotovo sva analiza podataka obuhvaća pregledavanje i usporedbu podataka tijekom datuma i vremena. Primjerice, možete zbrojiti iznose prodaje za proteklo fiskalno tromjesečje, a zatim te ukupne zbrojeve usporediti s drugim tromjesečjima ili pak izračunati završni saldo računa na kraju mjeseca. U svakom od ovih slučajeva koristite datume kao način grupiranja i agregiranja prodajnih transakcija ili salda za određeno vremensko razdoblje.

Izvješće dodatka Power View

Tablica datuma može sadržavati mnogo različitih prikaza datuma i vremena. Tablica s datumima, primjerice, često će sadržavati stupce kao što su Fiskalna godina, Mjesec, Kvartal ili Razdoblje koje možete odabrati kao polja s popisa polja prilikom rezanja i filtriranja podataka u izvješćima zaokretnih tablica ili dodatka Power View.

Popis polja programa Power View

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

Ako, primjerice, podaci koje želite pregledati sadržavaju datume od 1. veljače 2010. do 30. studenoga 2012., a vi izvješćujete o kalendarskoj godini, trebat će vam tablica datuma s rasponom barem od 1. siječnja 2010. do 31. prosinca 2012. Tablica datuma svake godine mora sadržavati sve dane svake godine. Ako ćete redovito osvježavati podatke novijim podacima, trebali biste datum završetka pomaknuti za godinu ili dvije da ne biste morali ažurirati tablicu datuma u toku vremena.

Datumska tablica s neprekidnim skupom datuma

Ako izvješćujete o fiskalnoj godini, možete stvoriti tablicu datuma s neprekinutim skupom datuma za svaku fiskalnu godinu. Na primjer, ako vaša fiskalna godina počinje 1. ožujka, a imate podatke za fiskalnu godinu od 2010. do trenutnog datuma (na primjer, u fiskalnoj godini 2013.), možete stvoriti tablicu datuma koja započinje 1. 3. 2009. i obuhvaća najmanje svaki dan u svakoj fiskalnoj godini do zadnjeg datuma u fiskalnoj godini 2013.

Ako ćete izvješćivati i za kalendarsku godinu i za fiskalnu godinu, ne morate stvarati zasebne tablice s datumima. Jedna tablica datuma može sadržavati stupce za kalendarsku godinu, fiskalnu godinu, pa čak i kalendar s trinaest četiri tjedna. Važno je da tablica s datumima sadrži skup uzastopnih datuma za sve obuhvaćene godine.

Dodavanje tablice datuma u podatkovni model

Nekoliko je načina na koje možete dodati tablicu datuma u podatkovni model:

• Uvoz iz relacijske baze podataka ili nekog drugog izvora podataka.
• Stvorite tablicu s datumima u programu Excel, a zatim kopirajte novu tablicu u dodatku Power Pivot ili se povežite s njom.
• Uvoz iz trgovine Microsoft Azure Marketplace.

Pogledajmo pobliže svaku od njih.

Uvoz iz relacijske baze podataka

Ako uvozite dio ili sve podatke iz skladišta podataka ili neke druge vrste relacijske baze podataka, vjerojatno već postoji tablica datuma i odnosi između nje i ostalih podataka koje uvozite. Datumi i oblik vjerojatno će se podudarati s datumima u činjenicama, a datumi će vjerojatno započinjati u prošlosti i sežu daleko u budućnost. Tablica datuma koju želite uvesti može biti vrlo velika i sadržavati raspon koji premašuje onime što ćete morati uvrstiti u podatkovni model. Pomoću naprednih značajki filtriranja čarobnjaka za uvoz tablica dodatka Power Pivot možete selektivno odabrati samo datume i određene stupce koji su vam zaista potrebni. Time se može znatno smanjiti veličina radne knjige i poboljšati performanse.

Čarobnjak za uvoz tablica

U većini slučajeva nećete morati stvarati dodatne stupce kao što su Fiskalna godina, Tjedan, Naziv mjeseca itd. jer će oni već postojati u uvezenoj tablici. No u nekim ćete slučajevima nakon uvoza tablice datuma u podatkovni model morati stvoriti dodatne stupce s datumima, ovisno o konkretnim potrebama izvješćivanja. To je srećom jednostavno pomoću jezika DAX. Kasnije ćete saznati više o stvaranju polja tablice datuma. Svako okruženje je drugačije. Ako niste sigurni sadrže li izvori podataka povezanu tablicu datuma ili kalendara, obratite se administratoru baze podataka.

Stvaranje tablice datuma u programu Excel

Tablicu datuma možete stvoriti u programu Excel, a zatim je kopirati u novu tablicu u podatkovnom modelu. To je zaista prilično jednostavno učiniti i daje vam veliku fleksibilnost.

Kada u programu Excel stvorite tablicu datuma, počinjete s jednim stupcem sa susjednim rasponom datuma. Nakon toga možete pomoću formula programa Excel stvoriti dodatne stupce, kao što su godina, kvartal, mjesec, fiskalna godina, razdoblje itd., a nakon kopiranja tablice u podatkovni model možete ih stvoriti kao izračunate stupce. Stvaranje dodatnih stupaca s datumima u dodatku Power Pivot opisano je u odjeljku Dodavanje novih stupaca datuma u tablicu datuma u nastavku članka.

Upute: stvaranje tablice datuma u programu Excel i njezino kopiranje u podatkovni model

1. U praznom radnom listu programa Excel u ćeliju A1 upišite naziv zaglavlja stupca da biste prepoznali raspon datuma. Obično će to biti nešto poput Date, DateTime ili DateKey.

2. U ćeliju A2 upišite datum početka. Primjerice, 1.1.2010.

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

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 Oblikuj kao tablicu kliknite U redu.
   

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

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

9. U pretpregledu>lijepljenja Naziv tablice unesite naziv, na primjer Datum ili Kalendar. Leave Use first row as column headerschecked, and then click OK (U redu).
   
   Nova tablica datuma (u ovom primjeru pod nazivom Calendar) u dodatku Power Pivot izgleda ovako:
   

   Napomena

   Povezanu tablicu možete stvoriti i pomoću funkcije Dodaj u podatkovni model. No time radna knjiga postaje nepotrebno velika jer sadrži dvije verzije tablice datuma: jedan u programu Excel i jedan u dodatku Power Pivot.

Sada u podatkovnom modelu imate tablicu s datumima. Pomoću jezika DAX možete dodati nove stupce s datumima, kao što su godina, mjesec itd.

Dodavanje novih stupaca s datumima u tablicu datuma

Tablica datuma s jednim stupcem datuma koji ima jedan redak za svaki dan u svakoj godini važna je za definiranje svih datuma u rasponu datuma. Ta funkcija nužna je i za stvaranje odnosa između tablice činjenica i tablice datuma. No taj stupac s jednim retkom za svaki dan nije koristan kada analizirate prema datumima u izvješću zaokretne tablice ili dodatka Power View. Želite da tablica datuma sadrži stupce koji olakšavaju agregiranje podataka za raspon ili grupu datuma. Primjerice, možete zbrojiti iznose prodaje prema mjesecu ili kvartalu ili pak stvoriti mjeru koja izračunava rast iz godine u godinu. U svakom su navedenom slučaju tablici datuma potrebni stupci godine, mjeseca ili tromjesečja koji omogućuju agregaciju podataka za to razdoblje.

Ako ste tablicu datuma uvezli iz relacijskog izvora podataka, ona možda već obuhvaća druge vrste željenih stupaca s datumima. U nekim slučajevima možda ćete htjeti izmijeniti neke od tih stupaca ili stvoriti dodatne stupce datuma. To osobito vrijedi ako u programu Excel stvorite vlastitu tablicu datuma i kopirate je u podatkovni model. Srećom, stvaranje novih stupaca s datumima u dodatku Power Pivot prilično je jednostavno uz funkcije datuma i vremena u jeziku DAX.

DAX funkcije datuma i vremena

Ako ste ikada radili s funkcijama datuma i vremena u formulama programa Excel, vjerojatno su vam poznate funkcije datuma i vremena. Premda su te funkcije slične svojim pandanima u programu Excel, postoje neke bitne razlike:

• DAX funkcije datuma i vremena koriste vrstu podataka datuma i vremena.
• Mogu uzeti vrijednosti iz stupca kao argument.
• Mogu se koristiti za vraćanje datumskih vrijednosti i/ili rukovanje njima.

Te se funkcije često koriste prilikom stvaranja prilagođenih stupaca s datumima u tablici datuma, pa ih je važno razumjeti. Pomoću nekih od tih funkcija stvorit ćemo stupce za godinu, tromjesečje, fiskalniMjesec itd.

DAX sadrži sljedeće funkcije datuma i vremena:

• DATUM
• DATEVALUE
• SLJEDEĆI DAN
• EDATE
• EOMONTH
• HOUR
• MINUTE
• MONTH
• NOW
• SECOND
• TIME
• TIMEVALUE
• DANAS
• WEEKDAY
• WEEKNUM
• YEAR
• YEARFRAC

U formulama možete koristiti i mnoge druge DAX funkcije. Mnoge formule koje se ovdje opisuju, primjerice, koriste matematičke i trigonometrijske funkcije kao što su MOD i TRUNC, logičke funkcije kao što su IF, i tekstne funkcije kao što je FORMAT Dodatne informacije o drugim DAX funkcijama potražite u odjeljku Dodatni resursi u nastavku članka.

Primjeri formula za kalendarsku godinu

U primjerima u nastavku opisane su formule koje služe za stvaranje dodatnih stupaca u tablici datuma pod nazivom Calendar. Jedan stupac pod nazivom Datum već postoji i sadrži raspon uzastopnih 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 datum/vrijeme, funkcija YEAR zna kako iz nje vratiti godinu.

Mjesec

=MONTH([datum])

U ovoj formuli, slično kao i kod funkcije YEAR, pomoću funkcije MONTH možemo jednostavno vratiti vrijednost mjeseca iz stupca Datum.

Tromjesečje

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

U ovoj formuli koristimo funkciju INT da bismo vratili vrijednost datuma kao cijeli broj. Argument koji određujemo za funkciju INT jest vrijednost iz stupca Mjesec, dodajte 2 pa podijelite to s 3 da biste dobili tromjesečje, od 1 do 4.

Naziv mjeseca

=FORMAT([datum],"mmmm")

U ovoj formuli koristimo funkciju FORMAT za pretvaranje brojčane vrijednosti iz stupca Datum u tekst. Kao prvi argument određujemo stupac Datum, a zatim oblik; Želimo da se u nazivu mjeseca prikazuju svi znakovi, pa koristimo "mmmm". Rezultat izgleda ovako:

Ako želimo vratiti naziv mjeseca skraćen na tri slova, u argumentu oblik koristimo "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 format navodimo "ddd".

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 je slici, primjerice, prikazano polje SalesAmount iz tablice Sales fact u odjeljku VRIJEDNOSTI te polje Godina i tromjesečje iz tablice dimenzija Calendar u izborniku RECI. SalesAmount se zbraja za kontekst godine i tromjesečja.

Primjeri formula za fiskalnu godinu

Fiskalna godina

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

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

Ne postoji funkcija koja može izdvojiti fiskalnu godinu iz vrijednosti datuma jer se početni i završni datumi fiskalne godine često razlikuju od datuma u kalendarskoj godini. Da bismo dohvatili fiskalnu godinu, najprije ćemo pomoću funkcije IF provjeriti je li vrijednost za argument Mjesec manja od 6 ili jednaka. Ako je u drugom argumentu vrijednost argumenta Mjesec manja ili jednaka 6, vratite vrijednost iz stupca Godina. Ako nije, vratite vrijednost iz argumenta Year i dodajte 1.

Drugi način navođenja vrijednosti mjeseca na kraju fiskalne godine jest stvaranje mjere koja jednostavno određuje mjesec. Na primjer, FYE:=6. Zatim umjesto broja mjeseca možete unijeti referencu na naziv mjere. Na primjer, =IF([Mjesec]<=[Fiskalno],[Godina],[Godina]+1). To omogućuje više fleksibilnosti pri referiranju na završni mjesec fiskalne godine u nekoliko različitih formula.

Fiskalni mjesec

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

U ovoj formuli navodimo je li vrijednost argumenta [Mjesec] manja ili jednaka 6, zatim uzimamo 6 i dodajemo vrijednost iz argumenta Mjesec, a u suprotnom oduzimamo 6 od vrijednosti iz argumenta [Mjesec].

Fiskalno tromjesečje

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

Formula koju koristimo za FiskalnoKvartal otprilike je jednaka formuli koju smo koristili za Tromjesečje u kalendarskoj godini. Jedina je razlika u tome što umjesto argumenta [Mjesec] navedemo [FiskalniMjesec].

praznici ili posebni datumi

Možda ćete htjeti dodati stupac s datumima koji označava da su određeni datumi blagdani ili neki drugi posebni datumi. Na primjer, možda ćete htjeti zbrojiti ukupnu prodaju za Novu godinu tako da u zaokretnu tablicu dodate polje Praznici kao rezač ili filtar. U drugim slučajevima možda te datume želite izuzeti iz drugih stupaca datuma ili u mjeri.

Uključivanje praznika ili posebnih dana prilično je jednostavno. U programu Excel možete stvoriti tablicu s datumima koje želite uvrstiti. Zatim možete kopirati ili upotrijebiti značajku 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 upućuju na njega mogu koristiti funkciju LOOKUPVALUE za vraćanje vrijednosti.

Slijedi primjer tablice stvorene u programu Excel koja obuhvaća praznike koji će se dodati u tablicu s datumima:

Datum	Praznik
1/1/2010	Nova godina
11/25/2010	Dan zahvalnosti
12/25/2010	Božić
1. 1. 2011.	Nova godina
11/24/2011	Dan zahvalnosti
12/25/2011	Božić
1.1.2012.	Nova godina
22.11.12.	Dan zahvalnosti
12/25/2012	Božić
1/1/2013	Nova godina
11/28/2013	Dan zahvalnosti
12/25/2013	Božić
11/27/2014	Dan zahvalnosti
12/25/2014	Božić
1. 1. 2014.	Nova godina
11/27/2014	Dan zahvalnosti
12/25/2014	Božić
1/1/2015	Nova godina
11/26/2014	Dan zahvalnosti
12/25/2015	Božić
01.01.16.	Nova godina
11/24/2016	Dan zahvalnosti
12/25/2016	Božić

U tablici datuma stvorit ćemo stupac pod nazivom Blagdani i upotrijebiti formulu kao što je ova:

=LOOKUPVALUE(Praznici[Praznici];Praznici[datum];Calendar[datum])

Pogledajmo pažljivije ovu formulu.

Funkciju LOOKUPVALUE dohvaćamo da bismo dohvatili vrijednosti iz stupca Praznici u tablici Praznici. U prvom argumentu određujemo stupac u kojem će biti vrijednost rezultata. Stupac Praznici u tablici Praznici navodimo jer želimo da se njegova vrijednost prikazuje.

=LOOKUPVALUE(Praznici[Praznici];Praznici[datum];Calendar[datum])

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

=LOOKUPVALUE(Praznici[Praznici];Praznici[datum];Calendar[datum])

Naposljetku ćemo u tablici Calendar odrediti stupac koji sadrži datume koje želite potražiti u tablici praznika. To je, naravno, stupac Datum u tablici Kalendar.

=LOOKUPVALUE(Praznici[Praznici];Praznici[datum];Calendar[datum])

Stupac praznici vratit će naziv praznika za svaki redak koji sadrži vrijednost datuma koja odgovara datumu u tablici Praznici.

Prilagođeni kalendar – trinaest razdoblja od četiri tjedna

Neke organizacije, poput maloprodaje ili ugostiteljstva, često izvještavaju o različitim razdobljima, poput trinaest razdoblja od četiri tjedna. Uz kalendar s trinaest i četiri tjedna, svako razdoblje traje 28 dana; Stoga svako razdoblje sadrži četiri ponedjeljka, četiri utorka, četiri srijede i tako dalje. Svako razdoblje sadrži isti broj dana, a blagdani obično padaju u isto razdoblje svake godine. Razdoblje možete započeti bilo koji dan u tjednu. Kao i za datume u kalendarskoj ili fiskalnoj godini, DAX možete koristiti za stvaranje dodatnih stupaca s prilagođenim datumima.

U primjerima u nastavku prvo puno razdoblje počinje prve nedjelje u fiskalnoj godini. U ovom slučaju fiskalna godina počinje 1. 7.

Tjedan

Ta nam vrijednost daje broj tjedna koji počinje s prvim punim tjednom u fiskalnoj godini. U ovom primjeru prvi puni tjedan započinje u nedjelju, što znači da prvi puni tjedan u prvoj fiskalnoj godini u tablici Calendar zapravo počinje 4. 7. 2010. i nastavlja se do posljednjeg cijelog tjedna u tablici Calendar. Iako ta vrijednost sama po sebi nije toliko korisna u analizi, potrebno ju je izračunati za korištenje u drugim formulama za razdoblje od 28 dana.

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

Pogledajmo pažljivije ovu formulu.

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

=INT([datum])

Zatim želimo potražiti prvu nedjelju u prvoj fiskalnoj godini. Vidimo da je 4.7.2010.

Od te vrijednosti oduzmite 40356 (a to je cijeli broj u prethodnoj fiskalnoj godini, odnosno 27.6.2010., posljednju nedjelju iz prethodne fiskalne godine) da biste dobili broj dana od početka dana u tablici Kalendar, i to ovako:

=INT([datum]-40356)

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

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

Rezultat izgleda ovako:

Točka

Razdoblje u ovom prilagođenom kalendaru sadrži 28 dana i uvijek počinje u nedjelju. Ovaj stupac vraća broj razdoblja koje počinje prve nedjelje u prvoj fiskalnoj godini.

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

Pogledajmo pažljivije ovu formulu.

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

= INT([Tjedan])

Zatim toj vrijednosti dodajte 3, ovako:

=INT([Tjedan]+3)

Zatim rezultat podijelite s 4, ovako:

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

Rezultat izgleda ovako:

Razdoblje fiskalne godine

Ta vrijednost vraća fiskalnu godinu za razdoblje.

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

Pogledajmo pažljivije ovu formulu.

Najprije ćemo stvoriti formulu koja vraća vrijednost iz argumenta točka i zbraja 12:

=([Točka]+12)

Rezultat dijelimo s 13 jer fiskalna godina ima trinaest razdoblja od 28 dana:

=(([Točka]+12)/13)

Zbrajamo 2010. jer je ona prva godina u tablici:

=(([Točka]+12)/13)+2010

Konačno, funkcijom INT uklanjamo bilo koji dio rezultata i vraćamo cijeli broj dijeljen s 13, ovako:

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

Rezultat izgleda ovako:

Razdoblje u fiskalnoj godini

Ta vrijednost vraća broj razdoblja, od 1 do 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)

Ova je formula malo složenija, pa ćemo je prvo opisati na jeziku koji bolje razumijemo. Ova formula kaže da podijelite vrijednost iz argumenta [Točka] s 13 da biste dobili broj razdoblja (od 1 do 13) u godini. Ako je taj broj 0, vrati se na 13.

Najprije ćemo stvoriti formulu koja ostatak vrijednosti iz argumenta Točka vraća za 13. MOD (matematičke i trigonometrijske funkcije) možemo koristiti ovako:

= MOD([Točka];13)

To nam uglavnom daje željeni rezultat, osim kada je vrijednost za polje Razdoblje 0 jer ti datumi ne spadaju u prvu fiskalnu godinu, kao u prvih pet dana ogledne tablice datuma iz Kalendara. To možemo riješiti pomoću funkcije IF. Ako je rezultat 0, vratit ćemo 13, ovako:

= IF(MOD([Period];13);MOD([Period];13);13)

Rezultat izgleda ovako:

Primjer zaokretne tablice

Na slici u nastavku prikazana je zaokretna tablica s poljem SalesAmount iz tablice Sales fact u odjeljku VALUES te poljima PeriodFiscalYear i PeriodInFiscalYear iz tablice datumske dimenzije Calendar u području ROWS. SalesAmount se zbraja za kontekst prema fiskalnoj godini i 28-dnevnom razdoblju fiskalne godine.

Odnosi

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

Budući da morate stvoriti odnos utemeljen na datumima, preporučujemo da taj odnos stvorite između stupaca čija su vrijednost vrste podataka Datum/vrijeme (Datum).

Za svaku vrijednost datuma u tablici činjenica, povezani stupac s vrijednostima u tablici datuma mora sadržavati podudarne vrijednosti. Na primjer, redak (zapis transakcije) u tablici Podaci o prodaji s vrijednošću 15.8.2012. u 12.00 sati u stupcu KljučDatuma mora imati odgovarajuću vrijednost u povezanom stupcu Datum u tablici datuma (pod nazivom Calendar). To je jedan od najvažnijih razloga zašto želite da stupac s datumima u tablici datuma sadrži raspon uzastopnih datuma koji obuhvaća sve moguće datume u tablici činjenica.

Ako se prikazuje pogreška o nepodudaranju vrste kada pokušavate stvoriti odnos, stupac u tablici Činjenice vjerojatno nije vrste podataka Datum. To se može dogoditi kada Power Pivot ne može automatski pretvoriti vrstu podataka koja nije datum (obično je riječ o tekstnoj vrsti podataka) u vrstu podataka datuma. I dalje možete koristiti stupac u tablici činjenica, ali morat ćete pomoću DAX formule pretvoriti podatke u novi izračunati stupac. Pročitajte odjeljak Pretvaranje datuma tekstne vrste podataka u vrstu podataka datuma u nastavku u dodatku.

Više odnosa

U nekim je slučajevima potrebno stvoriti više odnosa ili tablice s datumima. Na primjer, ako u tablici Podaci o prodaji postoji više polja datuma, kao što su KljučDatuma, DatumOtpreme i DatumPovrata, sva polja mogu imati odnos s poljem Datum u tablici datuma u tablici datuma u Kalendar, ali samo jedno od njih može biti aktivan odnos. Budući da KljučDatuma predstavlja datum transakcije, a time i najvažniji datum, to će u najboljem slučaju poslužiti kao aktivni odnos. Ostali su u neaktivnim odnosima.

Sljedeća zaokretna tablica izračunava ukupnu prodaju po fiskalnoj godini i fiskalnom tromjesečju. Mjera Ukupna prodaja s formulom Ukupna prodaja:=SUM([IznosProdaje]) dodaje se u VRIJEDNOSTI, a polja FiskalnaGodina i FiskalnoKvartal iz tablice datuma u Kalendar smještaju se u RECI.

Ova jednostavna zaokretna tablica ispravno funkcionira jer želimo zbrojiti ukupnu prodaju prema datumu transakcije u ključu DatumaDatuma. Naša mjera Ukupna prodaja koristi datume iz tablice KljučDatuma i zbraja se po fiskalnoj godini i fiskalnom tromjesečju jer postoji odnos između stupca KljučDatuma u tablici Prodaja i stupca Datum u tablici datuma u Kalendar.

Neaktivni odnosi

No što ako ukupnu prodaju ne želimo zbrojiti prema transakcijskom datumu, nego prema datumu isporuke? Potreban nam je odnos između stupca DatumOtpreme u tablici Prodaja i stupca Datum u tablici Kalendar. Ako ne stvorimo taj odnos, agregacije se 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 Calendar.

U ovom slučaju DatumOtpreme postoji neaktivan odnos pa svaka formula mjere stvorena radi agregacije podataka na temelju datuma isporuke mora navesti neaktivan odnos pomoću funkcije USERELATIONSHIP .

Na primjer, budući da postoji neaktivan odnos između stupca DatumOtpreme u tablici Prodaja i stupca Datum u tablici Kalendar, možemo stvoriti mjeru koja zbraja ukupnu prodaju prema datumu isporuke. Koristimo ovakvu formulu da bismo naveli odnos koji će se koristiti:

Ukupna prodaja po datumu isporuke:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))

Ova formula jednostavno navodi: Izračunaj zbroj za IznosProdaje, ali filtriraj pomoću odnosa između stupca DatumOtpreme u tablici Prodaja i stupca Datum u tablici Kalendar.

Ako stvorimo zaokretnu tablicu i mjeru Ukupna prodaja po datumu isporuke stavimo u VRIJEDNOSTI, a Fiskalna godina i Fiskalno tromjesečje u RECI, vidjet ćemo isti sveukupni zbroj, ali svi ostali iznosi zbrojeva za fiskalnu godinu i fiskalno tromjesečje razlikuju se jer se temelje na datumu isporuke, a ne na datumu transakcije.

Neaktivni odnosi omogućuju vam da koristite samo jednu tablicu datuma, no potrebno je da sve mjere (npr. Ukupna prodaja po datumu isporuke) u formuli navode neaktivan odnos. Postoji još jedna alternativa, a to jest korištenje više tablica s datumima.

Više tablica datuma

Drugi način rada s više stupaca datuma u tablici činjenica jest stvaranje više tablica datuma te stvaranje zasebnih aktivnih odnosa među njima. Pogledajmo ponovno naš primjer tablice Prodaja. Imamo tri stupca s datumima na temelju kojih možda želite zbrojiti podatke:

• KljučDatuma s datumom prodaje za svaku transakciju.
• DatumOtpreme – datum i vrijeme otpreme prodanih artikala kupcu.
• DatumPovrata – datum i vrijeme kada je vraćena jedna stavka ili više njih.

Imajte na umu da je najvažnije polje DatumKljuč s datumom transakcije. Većinu agregacija izvodit ćemo na temelju tih datuma, tako da ćemo sigurno poželjeti odnos između tih datuma i stupca Datum u tablici Calendar. Ako ne želimo stvarati neaktivne odnose između polja DatumOtpreme i DatumRezultata te polja Datum u tablici Calendar, zbog čega su potrebne formule posebnih mjera, možemo stvoriti dodatne tablice datuma za datum isporuke i datum rezultata. Tada možemo stvoriti aktivne odnose između njih.

U ovom smo primjeru stvorili drugu tablicu datuma naziva KalendarIsporuke. To, naravno, znači i stvaranje dodatnih stupaca datuma, a budući da se ti stupci s datumima nalaze u drugoj tablici datuma, želimo im dodijeliti nazive na način da se razlikuju od istih stupaca u tablici Calendar. Stvorili smo, primjerice, stupce pod nazivom Godina isporuke, MjesecIsporuke, Kvartal isporuke itd.

Ako stvorimo zaokretnu tablicu i mjeru Ukupna prodaja stavimo u VRIJEDNOSTI, a FiskalnaIsporuka i FiskalnaIsporuka u RECI, vidjet ćemo iste rezultate kao i kad smo stvorili neaktivan odnos i posebno izračunato polje Ukupna prodaja po datumu isporuke.

Svaki od tih pristupa zahtijeva pomno razmatranje. Kada koristite više odnosa s jednom tablicom datuma, možda ćete morati stvoriti posebne mjere koje pomoću funkcije USERELATIONSHIP prolaze kroz neaktivne odnose. S druge strane, stvaranje više tablica s datumima može biti komplicirano na popisu polja, a budući da u podatkovnom modelu imate više tablica, trebat će više memorije. Isprobajte ono što vam najbolje odgovara.

Svojstvo Tablica datuma

Svojstvo tablice datuma postavlja metapodatke potrebne za ispravno funkcioniranje funkcija Time-Intelligence, kao što su TOTALYTD, PREVIOUSMONTH i DATESBETWEEN Kada se izračun pokrene pomoću neke od tih funkcija, modul za formule dodatka Power Pivot zna gdje pronaći potrebne datume.

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

Upute: postavljanje svojstva datumske tablice

1. U prozoru dodatka PowerPivot odaberite tablicu Calendar.
2. Na kartici Dizajn kliknite Označi kao tablicu datuma.
3. U dijaloškom okviru Tablica označavanja kao datuma odaberite stupac s jedinstvenim vrijednostima i vrstu podataka Datum.

Rad s vremenom

Sve vrijednosti datuma s vrstom podataka Datum u programu Excel ili sustavu SQL Server zapravo su brojevi. U taj se broj nalaze znamenke koje se odnose na vrijeme. U mnogim slučajevima to je vrijeme za svaki red ponoć. Na primjer, ako polje DatumVrijemeKljuč u tablici Podaci o prodaji ima vrijednosti kao što su 19/10/2010 12.00:00 AM, to znači da su vrijednosti točne na razini dana. Ako je u vrijednost polja DatumDatumKljuč uključeno vrijeme, primjerice 19.10.2010. 8.44:00, to znači da su vrijednosti precizne minimalne. Vrijednosti mogu biti i na razini preciznosti na razini sata ili čak na razini preciznosti u sekundama. Razina preciznosti vrijednosti vremena značajno će utjecati na način stvaranja tablice datuma i odnose između nje i tablice činjenica.

Morate odrediti hoćete li podatke zbrojiti s razinom točnosti po danu ili s vremenskom preciznošću. Drugim riječima, možda ćete htjeti koristiti stupce u tablici datuma, kao što su Jutro, Poslijepodne ili Sat, kao polja vremena i datuma u područjima Redak, Stupac ili Filtar zaokretne tablice.

Ako podatke želite zbrojiti na vremensku razinu, za tablicu datuma trebat će se nalaziti stupac s datumima s uvrštenim vremenom. Trebat će joj stupac s datumima s jednim retkom za svaki sat, možda čak i svaku minutu, svakog dana i za svaku godinu u rasponu datuma. Razlog je to što za stvaranje odnosa između stupca DatumVremenskiKljuč u tablici činjenica i stupca datuma u tablici datuma morate imati podudarne vrijednosti. Kao što možete zamisliti, ako uvrstite mnogo godina, tablica s datumima može biti vrlo velika.

No u većini slučajeva podatke želite agregirati samo za određeni dan. Drugim riječima, kao polja u područjima retka, stupca ili filtra zaokretne tablice koristit ćete stupce kao što su godina, mjesec, tjedan ili dan u tjednu. U tom slučaju, stupac s datumima u tablici datuma mora sadržavati samo jedan redak za svaki dan u godini, kao što smo to ranije opisali.

Ako stupac datuma obuhvaća razinu točnosti vremena, ali ćete agregirati samo na dnevnoj razini, da biste stvorili odnos između tablice činjenica i tablice datuma, možda ćete morati izmijeniti tablicu činjenica stvaranjem novog stupca koji skraćuje vrijednosti u stupcu datuma na vrijednost dana. Drugim riječima, pretvorite vrijednost kao što je 19.10.2010. 8.44:00 u 19.10.2010. 12.00:00. Nakon toga možete stvoriti odnos između tog novog stupca i stupca datuma u tablici datuma jer se vrijednosti podudaraju.

Pogledajmo primjer. Ova slika prikazuje stupac DatumVremenskiKljuč u tablici Podaci o prodaji. Sve agregacije za podatke u ovoj tablici trebaju biti na razini dana pomoću stupaca u tablici datuma u aplikaciji Calendar, kao što su godina, mjesec, kvartal itd. Vrijeme koje vrijednost sadrži nije relevantno, već samo stvarni datum.

Budući da ne moramo analizirati te podatke do vremenske razine, nije nam potrebno da stupac Datum u tablici datuma u Calendar obuhvaća jedan redak za svaki sat i svaku minutu svakog dana u svakoj godini. Dakle, stupac Datum u tablici datuma izgleda ovako:

Da bismo stvorili odnos između stupca DatumVremenskiKljuč u tablici Prodaja i stupca Datum u tablici Kalendar, u tablici Podaci o prodaji stvorit ćemo novi izračunati stupac i pomoću funkcije TRUNC skratiti vrijednost datuma i vremena u stupcu DatumDatumKljučDatuma u datumsku vrijednost koja se podudara s vrijednostima u stupcu Datum u tablici Kalendar. Formula izgleda ovako:

=TRUNC([KljučDatumaVremena];0)

Tako dobivamo novi stupac (koji smo nazvali KljučDatuma) s datumom iz stupca DatumskoVremenskiKljuč i vremenom 12.00:00 za svaki redak:

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

Slično tome, u tablici Prodaja možemo stvoriti izračunati stupac koji preciznost vremena u stupcu DatumDatumKljuč smanjuje na razinu preciznosti tijekom sata. U ovom slučaju funkcija TRUNC neće funkcionirati, ali svejedno možemo koristiti druge DAX funkcije datuma i vremena da bismo izdvojili i ponovno povezali novu vrijednost s točnošću od sat vremena. Možemo koristiti formulu poput ove:

= DATE (YEAR([KljučDatumaVremena]), MONTH ([KljučDatumaVremena]), DAY([KljučDatumVremena]) + TIME (HOUR([KljučDatumVremena]), 0, 0)

Naš novi stupac izgleda ovako:

Pod uvjetom da stupac Datum u tablici datuma sadrži vrijednosti točnosti tijekom sata, možemo stvoriti odnos između njih.

Upotrebljivost datuma

Mnogi stupci datuma koje stvorite u tablici datuma nužni su za druga polja, ali zapravo nisu toliko korisni u analizi. Polje KljučDatuma u tablici Prodaja koje smo spomenuli i prikazali u ovom članku, primjerice, važno je jer se za svaku transakciju bilježi da se ta transakcija dogodila na određeni datum i vrijeme. No sa stajališta analize i izvješćivanja nije toliko koristan jer ga ne možemo koristiti kao redak, stupac ili polje 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, no ne možete ga koristiti kao dimenziju u zaokretnoj tablici.

Da bi tablice i stupci u njima bili što korisniji te da bi se popisima polja u zaokretnim tablicama i izvješćima značajke Power View jednostavnije kretalo, važno je sakriti nepotrebne stupce od klijentskih alata. Možda ćete htjeti 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 ne možete koristiti stupce Datum i Praznici u tablici Praznici kao polja u zaokretnoj tablici. I ovdje možete sakriti cijelu tablicu Praznici, da biste pojednostavnili kretanje po popisima polja.

Drugi važan aspekt rada s datumima jesu konvencije imenovanja. Tablicama i stupcima u dodatku Power Pivot možete dati naziv kako god želite. No imajte na umu da, posebno ako ćete radnu knjigu zajednički koristiti s drugim korisnicima, dobra konvencija imenovanja olakšava prepoznavanje tablica i datuma, ne samo na popisima polja, nego i u dodatku Power Pivot te u DAX formulama.

Kada u podatkovnom modelu stvorite tablicu datuma, možete početi stvarati mjere koje će vam pomoći da u potpunosti iskoristite svoje podatke. Neke značajke mogu biti jednostavne kao zbrajanje ukupnih prodaja za tekuću godinu, a druge mogu biti složenije jer je potrebno filtrirati određeni raspon jedinstvenih datuma. Saznajte više u članku Mjere u dodatku Power Pivot i funkcijama inteligencije vremena.

Dodatak

Pretvaranje datuma tekstualne vrste podataka u vrstu podataka datuma

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

Obično, kada pokušate promijeniti vrstu podataka za stupac s datumima koji su tekstne vrste u vrstu podataka datuma, Power Pivot može interpretirati datume i automatski ih pretvoriti u pravu vrstu podataka datuma. Ako Power Pivot ne može izvesti pretvorbu vrste podataka, prikazat će se poruka o pogrešci o nepodudaranju vrsta.

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

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

Ako pogledamo vrstu podataka u grupi Oblikovanje na kartici Polazno dodatka Power Pivot, vidjet ćete da je riječ o vrsti podataka Tekst.

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

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

Sjetite se, iz odjeljka Rad s vremenom ranije u ovom članku; Osim ako vam nije nužno da analiza bude precizna prema dobu dana, pretvorite datume u tablici činjenica u dnevnu preciznost. Imajući to na umu, želimo da vrijednosti u novom stupcu budu na razini točnosti dana (bez vremena). Vrijednosti u stupcu DatumI vrijeme 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))

Na taj će se način dobiti novi stupac (u ovom slučaju pod nazivom Datum). Power Pivot čak otkriva da su vrijednosti datumi i automatski postavlja vrstu podataka na Datum.

Ako želimo zadržati vremensku razinu točnosti, jednostavno ćemo proširiti formulu na 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))

Sad kada imamo stupac Datum vrste podataka, 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

Centar za resurse za DAX