Razumevanje i kreiranje tabela sa datumima u programskom dodatku Power Pivot u programu Excel

Tabele sa datumima u programskom dodatku Power Pivot su od suštinske važnosti za pregledanje i izračunavanje podataka tokom vremena. Ovaj članak pruža detaljan pregled tabela sa datumima i načina na koji ih možete napraviti u programskom dodatku Power Pivot. Ovaj članak konkretno opisuje:

• Zašto je tabela sa datumima važna za pregledanje i izračunavanje podataka po datumima i vremenu.
• Kako se koristi Power Pivot za dodavanje tabele sa datumima u model podataka.
• Kako da napravite nove kolone sa datumima kao što su "Godina", "Mesec" i "Period" u tabeli datuma.
• Kako da kreirate relacije između tabela sa datumima i tabela sa činjenicama.
• Kako raditi sa vremenom.

Ovaj članak je namenjen novim korisnicima programskog dodatka Power Pivot. Međutim, važno je da već dobro razumete uvoz podataka, kreiranje relacija i kreiranje izračunatih kolona i mera.

Ovaj članak ne opisuje kako da koristite DAX Time-Intelligence funkcije u formulama mera. Više informacija o načinu pravljenja mera pomoću DAX funkcija vremenske inteligencije potražite u članku "Vremenska inteligencija" u programskom dodatku Power Pivot u programu Excel.

Sadržaj

• Razumevanje tabela sa datumima

• Dodavanje tabele sa datumima u model podataka

  • Uvoz iz relacione baze podataka

  • Kreiranje tabele sa datumima u programu Excel

    • Kako da: Napravite tabelu sa datumima u programu Excel i kopirate je u model podataka

• Dodavanje novih kolona sa datumima u tabelu datuma

  • DAX funkcije datuma i vremena

  • Primeri formula za kalendarsku godinu

    • Godina
    • Mesec dana
    • Kvartal
    • Ime meseca

  • Primeri formula za fiskalnu godinu

    • Fiskalna godina
    • Fiskalni mesec
    • Fiskalni kvartal

• Praznici ili posebni datumi

• Prilagođeni kalendar – trinaest perioda od četiri sedmice

  • Sedmica
  • Obračunski period
  • Period Fiskalna godina
  • Period u fiskalnoj godini

• Relacije

  • Više relacija
  • Neaktivni odnosi
  • Više tabela sa datumima

• Svojstvo tabele datuma

• Rad sa vremenom

• Pravljenje korisnijih datuma

• Dodatak

  • Konvertovanje datuma tekstualnog tipa podataka u tip podataka datuma
  • Dodatni resursi

Razumevanje tabela sa datumima

Skoro svaka analiza podataka uključuje pregledanje i upoređivanje podataka tokom datuma i vremena. Na primer, možda želite da saberete iznose prodaje za prethodni fiskalni kvartal, a zatim uporedite te ukupne vrednosti sa drugim kvartalima, ili želite da izračunate stanje na kraju meseca za račun. U svakom od ovih slučajeva, datume koristite kao način grupisanja i agregiranja prodajnih transakcija ili salda za određeni vremenski period.

Power View izveštaj

Tabela sa datumima može da sadrži mnogo različitih prikaza datuma i vremena. Na primer, tabela sa datumima će često imati kolone kao što su "Fiskalna godina", "Mesec", "Kvartal" ili "Period" koje možete izabrati kao polja sa liste polja prilikom segmentiranja i filtriranja podataka u izvedenim tabelama ili izveštajima prikaza Power View.

Power View lista polja

Da bi kolone sa datumima kao što su "Godina", "Mesec" i "Kvartal" uključivale sve datume unutar odgovarajućeg opsega, tabela datuma mora da ima najmanje jednu kolonu sa susednim skupom datuma. To jest, ta kolona mora da ima jedan red za svaki dan za svaku godinu uključen u tabelu datuma.

Na primer, ako podaci koje želite da pregledate imaju datume od 1. februara 2010. do 30. novembra 2012, a vi izveštavate o kalendarskoj godini, onda ćete želeti tabelu sa datumima sa najmanje opsegom datuma od 1. januara 2010. do 31. decembra 2012. Svaka godina u tabeli datuma mora da sadrži sve dane za svaku godinu. Ako ćete redovno osvežavati podatke novijim podacima, možda ćete želeti da krajnji datum skratite za godinu ili dve da ne biste morali da ažurirate tabelu sa datumima kako vreme prolazi.

Tabela datuma sa celovitim skupom datuma

Ako pravite izveštaj o fiskalnoj godini, možete da napravite tabelu datuma sa susednim skupom datuma za svaku fiskalnu godinu. Na primer, ako vam fiskalna godina počinje 1. marta i imate podatke za fiskalne godine od 2010. do tekućeg datuma (na primer, za fiskalnu 2013.), možete da napravite tabelu sa datumima koja počinje 01.03.2009. i uključuje najmanje svaki dan u svakoj fiskalnoj godini do poslednjeg datuma u fiskalnoj 2013. godini.

Ako ćete izveštavati i o kalendarskoj i o fiskalnoj godini, ne morate da pravite zasebne tabele sa datumima. Tabela sa jednim datumom može da sadrži kolone za kalendarsku godinu, fiskalnu godinu, pa čak i za kalendar sa trinaest četvoronedeljnih perioda. Važno je da tabela sa datumima sadrži susedni skup datuma za sve uključene godine.

Dodavanje tabele sa datumima u model podataka

Postoji nekoliko načina na koje možete da dodate tabelu sa datumima u model podataka:

• Uvezite ih iz relacione baze podataka ili drugog izvora podataka.
• Kreirajte tabelu sa datumima u programu Excel, a zatim u programskom dodatku Power Pivot kopirajte novu tabelu ili se povežite sa njom.
• Uvezite iz usluge Microsoft Azure Marketplace.

Pogledajmo detaljnije svaku od njih.

Uvoz iz relacione baze podataka

Ako neke ili sve podatke uvezete iz skladišta podataka ili drugog tipa relacione baze podataka, moguće je da već postoji tabela sa datumima i relacije između nje i ostalih podataka koje uvozite. Datum i oblikovanje će se verovatno podudarati sa datumima u podacima o činjenicama, a datumi verovatno počinju daleko u prošlosti i odlaze daleko u budućnost. Tabela sa datumima koju želite da uvezete može biti veoma velika i sadržati opseg datuma koji prevazilazi ono što ćete morati da uključite u model podataka. Možete da koristite funkcije naprednog filtriranja Power Pivot čarobnjaka za uvoz tabele da biste selektivno odabrali samo datume i određene kolone koje su vam zaista potrebne. Ovo može znatno da smanji veličinu radne sveske i poboljša performanse.

Čarobnjak za uvoz tabele

U većini slučajeva nećete morati da pravite dodatne kolone kao što su "Fiskalna godina", "Sedmica", "Ime meseca" itd., jer će one već postojati u uvezenoj tabeli. Međutim, u nekim slučajevima, kada uvezete tabelu sa datumima u model podataka, možda ćete morati da napravite dodatne kolone sa datumima, u zavisnosti od određene potrebe za izveštavanjem. Srećom, ovo možete lako uraditi pomoću DAX-a. Kasnije ćete saznati više o kreiranju polja tabele sa datumima. Svako okruženje je drugačije. Ako niste sigurni da li izvori podataka imaju srodan datum ili tabelu kalendara, obratite se administratoru baze podataka.

Kreiranje tabele sa datumima u programu Excel

Tabelu sa datumima možete da kreirate u programu Excel, a zatim da je kopirate u novu tabelu u modelu podataka. Ovo je zaista prilično lako uraditi i daje vam veliku fleksibilnost.

Kada kreirate tabelu datuma u programu Excel, počinjete sa jednom kolonom sa kontinuiranim opsegom datuma. Zatim možete da napravite dodatne kolone kao što su "Godina", "Kvartal", "Mesec", "Fiskalna godina", "Period" itd. u Excel radnom listu pomoću Excel formula ili, kada kopirate tabelu u model podataka, možete da ih kreirate kao izračunate kolone. Kreiranje dodatnih kolona sa datumima u programskom dodatku Power Pivot opisano je u odeljku "Dodavanje novih kolona sa datumima u tabelu datuma " u nastavku ovog članka.

Kako da: Napravite tabelu sa datumima u programu Excel i kopirate je u model podataka

1. U programu Excel, u praznom radnom listu, u ćeliji A1 otkucajte ime zaglavlja kolone da biste identifikovali opseg datuma. Obično će to biti nešto poput Datum, DatumVreme ili ŠifraDatuma.

2. U ćeliji A2 otkucajte datum početka. Na primer, 01.01.2010.

3. Kliknite na pokazivač za popunjavanje i prevucite ga nadole do broja reda koji sadrži datum završetka. Na primer, 31.12.2016.
   

4. Izaberite sve redove u koloni " Datum " (uključujući ime zaglavlja u ćeliji A1).

5. U grupi Stilovi izaberite stavku "Oblikuj kao tabelu", a zatim izaberite stil.

6. U dijalogu "Oblikuj kao tabelu " kliknite na dugme " U redu".
   

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

8. U programskom dodatku Power Pivot, na kartici " Početak " kliknite na dugme "Nalepi".

9. U pregledu > pre lepljenjaIme tabele otkucajte ime, kao što je Datum ili Calendar. Ostavite potvrđen izbor opcije "Koristi prvi red kao zaglavlja kolona", a zatim kliknite na dugme " U redu".
   
   Nova tabela sa datumima (u ovom primeru nazvana Calendar) u programskom dodatku Power Pivot izgleda ovako:
   

   Napomena

   Povezanu tabelu možete da kreirate i pomoću opcije "Dodaj u model podataka". Međutim, to radnu svesku čini nepotrebno velikom jer ona ima dve verzije tabele sa datumima; jedan u programu Excel, a drugi u programskom dodatku Power Pivot.

Sada imate tabelu sa datumima u modelu podataka. Pomoću jezika DAX možete da dodate nove kolone sa datumima, kao što su "Godina", "Mesec" itd.

Dodavanje novih kolona sa datumima u tabelu datuma

Tabela sa datumima sa jednom kolonom sa datumima koja ima jedan red za svaki dan za svaku godinu važna je za definisanje svih datuma u opsegu datuma. To je neophodno i za kreiranje relacije između tabele sa činjenicama i tabele sa datumima. Ali ta kolona sa jednim datumom sa po jednim redom za svaki dan nije korisna kada se analizira po datumima u izvedenoj tabeli ili Power View izveštaju. Želite da tabela datuma uključuje kolone koje vam pomažu da agregirate podatke za opseg ili grupu datuma. Na primer, možda ćete želeti da saberete iznose prodaje po mesecima ili kvartalima ili možete napraviti meru koja izračunava godišnji rast. U svakom od ovih slučajeva, tabeli sa datumima potrebne su kolone godine, meseca ili kvartala koje vam omogućavaju da prikupite podatke za taj period.

Ako ste tabelu sa datumima uvezli iz relacionog izvora podataka, ona možda već obuhvata različite željene tipove kolona sa datumima. U nekim slučajevima, možda ćete želeti da izmenite neke od tih kolona ili kreirate dodatne kolone sa datumima. Ovo posebno važi ako napravite sopstvenu tabelu sa datumima u programu Excel i kopirate je u model podataka. Srećom, kreiranje novih kolona sa datumima u programskom dodatku Power Pivot prilično je lako uz funkcije datuma i vremena u DAX-u.

DAX funkcije datuma i vremena

Ako ste ikada radili sa funkcijama datuma i vremena u Excel formulama, onda ćete verovatno biti upoznati sa funkcijama datuma i vremena. Iako su ove funkcije slične svojim kolegama u programu Excel, postoje neke važne razlike:

• DAX funkcije za datum i vreme koriste tip podataka datum/vreme.
• Oni mogu da uzimaju vrednosti iz kolone kao argument.
• Oni mogu da se koriste za vraćanje i/ili manipulisanje vrednostima datuma.

Ove funkcije se često koriste prilikom kreiranja prilagođenih kolona sa datumima u tabeli datuma, tako da ih je važno razumeti. Koristićemo određeni broj tih funkcija da bismo napravili kolone za kolonu za argumente "Godina", "Kvartal", "FiskalniMesec" itd.

DAX uključuje sledeće funkcije za datum i vreme:

• DATUM
• DATEVALUE
• SLEDEĆI DAN
• EDATE
• EOMONTH
• ČAS
• MINUT
• MESEC
• NOW
• SEKUND
• TIME
• TIMEVALUE
• TODAY
• WEEKDAY
• WEEKNUM
• GODINA
• YEARFRAC

Postoje i mnoge druge DAX funkcije koje možete koristiti u formulama. Na primer, mnoge formule opisane ovde koriste matematičke i trigonometrijske funkcije kao što su MOD i TRUNC, logičke funkcije kao što je IF i tekstualne funkcije kao što je FORMAT Više informacija o drugim DAX funkcijama potražite u odeljku "Dodatni resursi " u nastavku ovog članka.

Primeri formula za kalendarsku godinu

Sledeći primeri opisuju formule koje se koriste za pravljenje dodatnih kolona u tabeli sa datumima po imenu Calendar. Jedna kolona, nazvana "Datum", već postoji i sadrži susedni opseg datuma od 01.01.2010. do 31.12.2016.

Godina

=YEAR([datum])

U ovoj formuli, funkcija YEAR daje godinu iz vrednosti u koloni "Datum". Funkcija YEAR zna kako da iz nje izračuna godinu jer je vrednost u koloni "Datum" tipa podataka.

Mesec

=MONTH([datum])

U ovoj formuli, slično kao i sa funkcijom YEAR, možemo jednostavno da koristimo funkciju MONTH da bismo dobili vrednost meseca iz kolone "Datum".

Kvartal

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

U ovoj formuli koristimo funkciju INT koja vraća vrednost datuma kao ceo broj. Argument koji navedemo za funkciju INT je vrednost iz kolone "Mesec", dodajte 2, a zatim podelite sa 3 da biste dobili kvartal, od 1 do 4.

Ime meseca

=FORMAT([datum],"mmmm")

U ovoj formuli, da bismo dobili ime meseca, koristimo funkciju FORMAT za konvertovanje numeričke vrednosti iz kolone "Datum" u tekst. Kao prvi argument navodimo kolonu "Datum", a zatim format; Želimo da ime meseca prikazuje sve znakove, pa koristimo "mmmm". Rezultat izgleda ovako:

Ako želimo da vratimo ime meseca skraćeno na tri slova, koristili bismo "mmm" u argumentu format.

Dan sedmice

=FORMAT([datum],"ddd")

U ovoj formuli koristimo funkciju FORMAT da bismo dobili ime dana. Pošto želimo samo skraćeno ime dana, navodimo "ddd" u argumentu format.

Uzorak izvedene tabele

Kada imate polja za datume kao što su Godina, Kvartal, Mesec itd., možete da ih koristite u izvedenoj tabeli ili izveštaju. Na primer, na sledećoj slici je prikazano polje "IznosProdaje" iz tabele "Činjenice prodaje" u oblasti "VREDNOSTI", kao i polje "Godina i kvartal" iz tabele dimenzija Calendar" u oblasti "REDOVI". SalesAmount se agregira za kontekst godine i kvartala.

Primeri formula za fiskalnu godinu

Fiskalna godina

=IF([Mesec]<= 6,[Godina],[Godina]+1)

U ovom primeru, fiskalna godina počinje 1. jula.

Ne postoji funkcija koja može da izdvoji fiskalnu godinu iz vrednosti datuma zato što se datumi početka i završetka fiskalne godine često razlikuju od datuma kalendarske godine. Da bismo dobili fiskalnu godinu, prvo koristimo funkciju IF da bismo testirali da li je vrednost za polje "Mesec" manja od ili jednaka 6. U drugom argumentu, ako je vrednost za polje "Mesec" manja od ili jednaka broju 6, vratite vrednost iz kolone "Godina". Ako nije, onda vratite vrednost iz "Year" i dodajte 1.

Drugi način da navedete vrednost meseca na kraju fiskalne godine jeste da napravite meru koja jednostavno navodi taj mesec. Na primer, FYE:=6. Zatim možete da uputite na ime mere umesto broja meseca. Na primer, =IF([Mesec]<=[FYE],[Godina],[Godina]+1). To pruža veću fleksibilnost prilikom upućivanja na mesec koji završava fiskalnu godinu u nekoliko različitih formula.

Fiskalni mesec

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

U ovoj formuli navodimo ako je vrednost za [Mesec] manja od ili jednaka 6, zatim uzimamo 6 i sabirate vrednost iz meseca, u suprotnom oduzimamo 6 od vrednosti iz argumenta [Mesec].

Fiskalni kvartal

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

Formula koju koristimo za fiskalni kvartal je skoro ista kao i za kvartal kalendarske godine. Jedina razlika je to što navedemo [fiskalniMesec] umesto [Mesec].

Praznici ili posebni datumi

Možda ćete želeti da uključite kolonu sa datumom koja ukazuje na to da su određeni datumi praznici ili neki drugi posebni datumi. Na primer, možda želite da saberete ukupne vrednosti prodaje za dan Nove godine tako što ćete dodati polje "Praznik" u izvedenu tabelu, kao modul za sečenje ili filter. U drugim slučajevima, možda ćete želeti da isključite te datume iz drugih kolona datuma ili u određenoj meri.

Uključivanje praznika ili posebnih dana prilično je jednostavno. U programu Excel možete da napravite tabelu sa datumima koje želite da uključite. Zatim možete da kopirate ili koristite dugme "Dodaj u model podataka" da biste ga dodali u model podataka kao povezanu tabelu. U većini slučajeva nije neophodno da kreirate relaciju između tabele i tabele u aplikaciji Calendar. Sve formule koje upućuju na nju mogu da koriste funkciju LOOKUPVALUE da bi vratile vrednosti.

Sledi primer tabele kreirane u programu Excel koja sadrži praznike koji se dodaju u tabelu datuma:

Datum	Praznik
1/1/2010	Nova godina
11/25/2010	Dan zahvalnosti
12/25/2010	Božić
01.01.11.	Nova godina
11/24/2011	Dan zahvalnosti
12/25/2011	Božić
01.01.2012.	Nova godina
22.11.2012.	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.2016.	Nova godina
11/24/2016	Dan zahvalnosti
12/25/2016	Božić

U tabeli datuma pravimo kolonu pod imenom "Praznik " i koristimo formulu poput ove:

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

Hajde pažljivije da pogledamo ovu formulu.

Koristimo funkciju LOOKUPVALUE da bismo preuzeli vrednosti iz kolone "Praznik" u tabeli "Praznici". U prvom argumentu navodimo kolonu u kojoj će biti vrednost rezultata. Navodimo kolonu "Praznik " u tabeli "Praznici " zato što je to vrednost koju želimo da dobijemo.

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

Zatim navodimo drugi argument, kolonu za pretragu koja sadrži datume koje želimo da tražimo. Kolonu "Datum" navodimo u tabeli "Praznici", ovako:

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

Na kraju, navodimo kolonu u tabeli "Calendar koja sadrži datume koje želimo da pretražujemo u tabeli "Praznik". To je, naravno, kolona "Datum" u tabeli Calendar.

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

Kolona "Praznik" daje ime praznika za svaki red koji ima vrednost datuma koja se podudara sa datumom u tabeli "Praznici".

Prilagođeni kalendar – trinaest perioda od četiri sedmice

Neke organizacije, kao što su maloprodaja ili ugostiteljstvo, često izveštavaju o različitim periodima, na primer o trinaest perioda od četiri sedmice. Sa kalendarom od trinaest četvoronedeljnih perioda, svaki period iznosi 28 dana; Dakle, svaki period sadrži četiri ponedeljka, četiri utorka, četiri srede, i tako dalje. Svaki period sadrži isti broj dana i obično praznici padaju unutar istog perioda svake godine. Možete odabrati da period započnete bilo kojim danom u sedmici. Kao i sa datumima u kalendaru ili fiskalnoj godini, DAX možete da koristite za kreiranje dodatnih kolona sa prilagođenim datumima.

U dolenavedenim primerima, prvi puni period počinje prve nedelje u fiskalnoj godini. U ovom slučaju, fiskalna godina počinje 1.7.

Sedmica

Ova vrednost nam daje broj sedmice koja počinje sa prvom punom sedmicom u fiskalnoj godini. U ovom primeru, prva cela sedmica počinje u nedelju, tako da prva puna sedmica u prvoj fiskalnoj godini u tabeli "Calendar zapravo počinje 04.07.2010. i nastavlja se kroz poslednju celu sedmicu u tabeli "Calendar. Iako ova vrednost sama po sebi nije toliko korisna u analizi, neophodno ju je izračunati za upotrebu u drugim formulama za period od 28 dana.

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

Hajde pažljivije da pogledamo ovu formulu.

Prvo ćemo napraviti formulu koja daje vrednosti iz kolone "Datum" kao ceo broj, na sledeći način:

=INT([datum])

Zatim želimo da potražimo prvu nedelju u prvoj fiskalnoj godini. Vidimo da je 4.7.2010.

Sada, oduzmite 40356 (što je ceo broj za 27.6.2010, poslednju nedelju od prethodne fiskalne godine) od te vrednosti da biste dobili broj dana od početka dana u tabeli Calendar, na sledeći način:

=INT([datum]-40356)

Zatim podelite rezultat sa 7 (dana u sedmici), na sledeći način:

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

Rezultat izgleda ovako:

Obračunski period

Period u ovom prilagođenom kalendaru sadrži 28 dana i uvek počinje u nedelju. Ova kolona daje broj perioda koji počinje sa prvom nedeljom u prvoj fiskalnoj godini.

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

Hajde pažljivije da pogledamo ovu formulu.

Prvo ćemo napraviti formulu koja vraća vrednost iz kolone "Sedmica" kao ceo broj, na sledeći način:

= INT([Sedmica])

Zatim dodajte 3 toj vrednosti na sledeći način:

=INT([Sedmica]+3)

Zatim podelite rezultat sa 4, ovako:

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

Rezultat izgleda ovako:

Period Fiskalna godina

Ova vrednost daje fiskalnu godinu za neki period.

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

Hajde pažljivije da pogledamo ovu formulu.

Prvo ćemo napraviti formulu koja vraća vrednost iz argumenta "Tačka" i sabira 12:

=([Tačka]+12)

Rezultat delimo sa 13 zato što u fiskalnoj godini postoji trinaest perioda od 28 dana:

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

Dodajemo 2010, zato što je to prva godina u tabeli:

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

Na kraju, koristimo funkciju INT da uklonimo bilo koji deo rezultata i vratimo ceo broj kada se podeli sa 13, ovako:

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

Rezultat izgleda ovako:

Period u fiskalnoj godini

Ova vrednost daje broj perioda, 1 – 13, počev od prvog punog perioda (koji počinje u nedelju) u svakoj fiskalnoj godini.

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

Ova formula je malo složenija, pa ćemo je prvo opisati na jeziku koji bolje razumemo. Ova formula kaže da podelite vrednost od [Period] sa 13 da biste dobili broj perioda (1-13) u godini. Ako je taj broj 0, vratite 13.

Prvo pravimo formulu koja daje ostatak vrednosti iz argumenta "Tačka" sa 13. Možemo da koristimo MOD (matematičke i trigonometrijske funkcije) na sledeći način:

= MOD([Tačka],13)

To nam najvećim delom daje rezultat koji želimo, osim gde je vrednost za polje "Period" 0 zato što ti datumi ne padaju u prvu fiskalnu godinu, kao u prvih pet dana našeg primera Calendar tabele sa datumima. To možemo da obavimo pomoću funkcije IF. U slučaju da je rezultat 0, vraćamo 13, ovako:

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

Rezultat izgleda ovako:

Uzorak izvedene tabele

Slika ispod prikazuje izvedenu tabelu sa poljem "IznosProdaje" iz tabele "Činjenice prodaje" u poljima "VREDNOSTI" i polja "PeriodFiscalYear i PeriodInFiscalYear iz tabele dimenzija datuma Calendar u oblasti "REDOVI". IznosProdaje se u kontekstu agregira po fiskalnoj godini i periodu od 28 dana u fiskalnoj godini.

Relacije

Kada napravite tabelu sa datumima u modelu podataka, da biste počeli da pretražujete podatke u izvedenim tabelama i izveštajima i da biste prikupili podatke na osnovu kolona u tabeli dimenzija datuma, morate da kreirate relaciju između tabele sa činjenicama i podataka o transakcijama i tabele datuma.

Pošto treba da kreirate relaciju zasnovanu na datumima, trebalo bi da proverite da li ste napravili tu relaciju između kolona čije su vrednosti tipa podataka datum/vreme (datum).

Za svaku vrednost datuma u tabeli sa činjenicama, srodna kolona za pronalaženje u tabeli datuma mora da sadrži vrednosti koje se podudaraju. Na primer, red (zapis transakcije) u tabeli "Činjenice prodaje" sa vrednošću od 15.8.2012. do 12:00 časova u koloni "ŠifraDatuma" mora da ima odgovarajuću vrednost u srodnoj koloni "Datum" u tabeli "Datum pod imenom Calendar). Ovo je jedan od najvažnijih razloga zašto želite da kolona sa datumima u tabeli datuma sadrži susedni opseg datuma koji uključuje sve moguće datume u tabeli sa činjenicama.

Ako dobijete grešku nepodudaranja tipa kada pokušate da kreirate relaciju, to je verovatno zbog toga što kolona u tabeli sa činjenicama nije tipa podataka "Datum". To se može desiti kada Power Pivot ne može automatski da konvertuje tip podataka koji nije datum (obično je to tekstualni tip podataka) u tip podataka datuma. I dalje možete da koristite tu kolonu u tabeli sa činjenicama, ali ćete morati da konvertujete podatke pomoću DAX formule u novoj izračunatoj koloni. Pogledajte "Konvertovanje datuma tekstualnog tipa podataka u tip podataka datuma " u nastavku dodatka.

Više relacija

U nekim slučajevima, možda će biti neophodno da napravite više relacija ili da kreirate više tabela sa datumima. Na primer, ako u tabeli "Činjenice prodaje" postoji više polja sa datumom, kao što su DateKey, ShipDate i ReturnDate, sva ona mogu da imaju relacije sa poljem za datum u tabeli datuma Calendar, ali samo jedno od njih može biti aktivna relacija. U ovom slučaju, pošto Šifra Datuma predstavlja datum transakcije, a samim tim i najvažniji datum, ovo bi najbolje služilo kao aktivna relacija. Ostali imaju neaktivne odnose.

Sledeća izvedena tabela izračunava ukupnu prodaju po fiskalnoj godini i fiskalnom kvartalu. Mera pod imenom "Ukupna prodaja" sa formulom "Ukupna prodaja:=SUM([IznosProdaje])" postavlja se u oblast "VREDNOSTI", a polja "FiskalnaGodina" i "FiskalniKvartal" iz tabele sa datumima Calendar-a postavljaju se u "REDOVI".

Ova jednostavna izvedena tabela ispravno funkcioniše jer želimo da sazbiramo ukupnu prodaju do datuma transakcije u ključu DateKey. Mera "Ukupna prodaja" koristi datume u tabeli "ŠifraDatuma" i sabira se po fiskalnoj godini i fiskalnom kvartalu zato što postoji relacija između kolone "ŠifraDatuma" u tabeli "Prodaja" i kolone "Datum" u tabeli Calendar datuma.

Neaktivni odnosi

Ali, šta ako želimo da saberemo ukupnu prodaju ne po datumu transakcije, već po datumu isporuke? Potrebna nam je relacija između kolone "DatumIsporuke" u tabeli "Prodaja" i kolone "Datum" u tabeli "Calendar. Ako ne kreiramo tu relaciju, naše agregacije su uvek zasnovane na datumu transakcije. Međutim, možemo da imamo više relacija, iako samo jedna može da bude aktivna, a pošto je datum transakcije najvažniji, ona dobija aktivnu relaciju sa tabelom Calendar.

U ovom slučaju, ShipDate ima neaktivnu relaciju, tako da svaka formula mere koja se kreira radi agregacije podataka na osnovu datuma isporuke mora da navede neaktivnu relaciju pomoću funkcije USERELATIONSHIP .

Na primer, budući da postoji neaktivna relacija između kolone "DatumIsporuke" u tabeli "Prodaja" i kolone "Datum" u tabeli Calendar, možemo da napravimo meru koja sabira ukupne prodaje po datumu isporuke. Koristimo formulu kao što je ova da bismo naveli relaciju koju treba koristiti:

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

Ova formula jednostavno glasi: Izračunajte zbir za polje "IznosProdaje", ali filtrirajte pomoću relacije između kolone "DatumIsporuke" u tabeli "Prodaja" i kolone "Datum" u tabeli Calendar.

Sada, ako napravimo izvedenu tabelu i stavimo meru "Ukupna prodaja po datumu isporuke" u "VREDNOSTI", a fiskalnu godinu i fiskalni kvartal u "REDOVE", videćemo isti ukupni zbir, ali svi ostali iznosi zbirova za fiskalnu godinu i fiskalni kvartal razlikuju se jer su zasnovani na datumu isporuke, a ne na datumu transakcije.

Korišćenje neaktivnih relacija omogućava da koristite samo jednu tabelu sa datumima, ali zahteva da sve mere (kao što je "Ukupna prodaja po datumu isporuke") upućuju na neaktivnu relaciju u formuli. Postoji još jedna alternativa, odnosno korišćenje više tabela sa datumima.

Više tabela sa datumima

Drugi način za rad sa više kolona sa datumima u tabeli sa činjenicama je da kreirate više tabela sa datumima i napravite zasebne aktivne relacije između njih. Hajde da ponovo pogledamo primer tabele "Prodaja". Imamo tri kolone sa datumima na osnovu kojih bismo možda želeli da prikupimo podatke:

• Šifra datuma sa datumom prodaje za svaku transakciju.
• Datum isporuke – sa datumom i vremenom isporuke prodatih stavki kupcu.
• Datum povraćaja – sa datumom i vremenom kada je primljena jedna ili više vraćenih stavki.

Ne zaboravite, najvažnije je polje "ŠifraDatuma" sa datumom transakcije. Većinu agregacija ćemo raditi na osnovu ovih datuma, tako da ćemo sigurno želeti relaciju između tih datuma i kolone "Datum u tabeli Calendar. Ako ne želimo da kreiramo neaktivne relacije između polja "DatumIsporuke" i "DatumVraćanja" i polja "Datum" u tabeli "Calendar, što zahteva posebne formule mera, možemo da kreiramo dodatne tabele sa datumima za datum isporuke i datum povratka. Tada možemo da stvorimo aktivne odnose između njih.

U ovom primeru, napravili smo drugu tabelu sa datumima koja se zove "KalendarIsporuke". To, naravno, znači i kreiranje dodatnih kolona sa datumima, a pošto se ove kolone sa datumima nalaze u drugoj tabeli sa datumima, želimo da ih imenujemo na način koji ih razlikuje od istih kolona u tabeli Calendar. Na primer, kreirali smo kolone pod imenom "GodinaIsporuke", "MesecIsporuke", "KvartalIsporuke" itd.

Ako napravimo izvedenu tabelu i meru "Ukupna prodaja" stavimo u oblasti "VREDNOSTI" i "FiskalnaGodina" i "FiskalniKvartal" otpreme u "REDOVE", videćemo iste rezultate koje smo videli i kada smo napravili neaktivnu relaciju i specijalno izračunato polje "Ukupna prodaja do datuma isporuke".

Svaki od ovih pristupa zahteva pažljivo razmatranje. Kada koristite više relacija sa jednom tabelom datuma, možda ćete morati da napravite posebne mere koje prelaze neaktivne relacije pomoću funkcije USERELATIONSHIP. S druge strane, kreiranje više tabela sa datumima može da vas zbuni u listi polja, a pošto u modelu podataka imate više tabela, to zahteva više memorije. Eksperimentišite sa onim što vam najviše odgovara.

Svojstvo tabele datuma

Svojstvo tabele datuma postavlja metapodatke neophodne za ispravno funkcionisanje Time-Intelligence funkcija kao što su TOTALYTD, PREVIOUSMONTH i DATESBETWEEN. Kada se izračunavanje pokrene pomoću jedne od tih funkcija, Power Pivot mašina formula zna gde da ide da bi dobila datume koji su mu potrebni.

Kada postavite svojstvo "Tabela datuma", u njoj navodite tabelu sa datumima i kolonu datuma koja ima tip podataka "Datum (datum/vreme)".

Kako da: Podešavanje svojstva tabele datuma

1. U prozoru programskog dodatka PowerPivot izaberite tabelu Calendar.
2. On the Design tab, click Mark as date Table.
3. U dijalogu "Označi kao tabelu datuma" izaberite kolonu sa jedinstvenim vrednostima i tipom podataka "Datum".

Rad sa vremenom

Sve vrednosti datuma sa tipom podataka "Datum" u programu Excel ili sistemu SQL Server zapravo su brojevi. U taj broj su uključene cifre koje se odnose na vreme. U mnogim slučajevima to vreme za svaki red je ponoć. Na primer, ako polje "ŠifraDatuma/vremena" u tabeli sa činjenicama prodaje ima vrednosti kao što je 19/10/2010 00:00:00, to znači da su vrednosti na nivou preciznosti za dan. Ako vrednosti polja DateTimeKey imaju uključeno vreme, na primer 19.10.2010 8:44:00, to znači da su vrednosti tačno minimalne preciznosti. Vrednosti mogu da budu i na nivou preciznosti tokom sata ili čak na nivou preciznosti u sekundama. Nivo preciznosti vrednosti vremena će značajno uticati na način na koji kreirate tabelu sa datumima i relacije između nje i tabele sa činjenicama.

Morate da utvrdite da li ćete agregirati podatke na nivo preciznosti jednog dana ili na nivo vremenske preciznosti. Drugim rečima, možda ćete želeti da koristite kolone u tabeli datuma kao što su "Jutro", "Popodne" ili "Sat" kao polja za vreme i datum u oblastima izvedene tabele "Red", "Kolona" ili "Filter".

Ako nameravate da agregirate podatke do nivoa vremena, onda će tabeli datuma biti potrebna kolona sa datumima sa uključenim vremenom. U stvari, biće mu potrebna kolona sa datumima sa jednim redom za svaki sat, ili možda čak i svaki minut svakog dana, za svaku godinu u opsegu datuma. To je zato što za kreiranje relacije između kolone "ŠifraDatuma/Vremena" u tabeli sa činjenicama i kolone "Datum" u tabeli sa datumima moraju postojati vrednosti koje se podudaraju. Kao što možete da zamislite, ako uključite mnogo godina, to može da bude veoma velika tabela sa datumima.

Međutim, u većini slučajeva želite da prikupite podatke samo u toku dana. Drugim rečima, koristićete kolone kao što su "Godina", "Mesec", "Sedmica" ili "Dan sedmice" kao polja u oblastima "Red", "Kolona" ili "Filter" izvedene tabele. U ovom slučaju, kolona "Datum" u tabeli "Datumi" mora da sadrži samo jedan red za svaki dan u godini, kao što smo ranije opisali.

Ako kolona sa datumima uključuje nivo preciznosti u vremenu, ali ćete agregirati samo na nivo dana, da biste kreirali relaciju između tabele sa činjenicama i tabele datuma, možda ćete morati da izmenite tabelu sa činjenicama tako što ćete napraviti novu kolonu koja skraćuje vrednosti u koloni "Datum" na vrednost dana. Drugim rečima, konvertujte vrednost kao što je 19.10.2010 08:44:00 u 19.10.2010. 12:00:00. Zatim možete da kreirate relaciju između te nove kolone i kolone datuma u tabeli sa datumima zato što se vrednosti podudaraju.

Hajde da pogledamo primer. Ova slika prikazuje kolonu "ŠifraDatuma/vremena" u tabeli sa činjenicama prodaje. Sve agregacije za podatke u ovoj tabeli treba da budu samo na nivou dana, pomoću kolona iz tabele sa datumima Calendar kao što su "Godina", "Mesec", "Kvartal" itd. Vreme uključeno u vrednost nije relevantno, samo stvarni datum.

Pošto ne moramo da analiziramo ove podatke do nivoa vremena, nije nam potrebno da kolona "Datum" u tabeli datuma Calendar uključuje jedan red za svaki sat i svaki minut svakog dana u svakoj godini. Kolona "Datum" u tabeli sa datumima izgleda ovako:

Da bismo kreirali relaciju između kolone "ŠifraDatuma/Vremena" u tabeli "Prodaja" i kolone "Datum" u tabeli "Calendar", možemo da napravimo novu izračunatu kolonu u tabeli "Činjenice prodaje" i da upotrebimo funkciju TRUNC da skratimo vrednost datuma i vremena u koloni "ŠifraDatuma" na vrednost datuma koja se podudara sa vrednostima u koloni "Datum" u tabeli "Calendar. Naša formula izgleda ovako:

=TRUNC([DateTimeKey],0)

To nam pruža novu kolonu (nazvali smo se DateKey) sa datumom iz kolone "DateTimeKey" i vremenom od 12:00:00 za svaki red:

Sada možemo da napravimo relaciju između ove nove kolone (ŠifraDatuma) i kolone "Datum" u tabeli "Calendar.

Slično tome, možemo da napravimo izračunatu kolonu u tabeli "Prodaja" koja smanjuje preciznost vremena u koloni "ŠifraDatuma/vremena" na nivo preciznosti koji označava jedan čas. U ovom slučaju funkcija TRUNC neće raditi, ali i dalje možemo da koristimo druge DAX funkcije za datum i vreme da bismo izdvojili i ponovo spojili novu vrednost sa nivoom preciznosti jedan čas. Možemo da koristimo formulu poput ove:

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

Naša nova kolumna izgleda ovako:

Pod uslovom da kolona "Datum" u tabeli datuma ima vrednosti do nivoa preciznosti po času, možemo da kreiramo relaciju između njih.

Pravljenje korisnijih datuma

Mnoge kolone sa datumima koje napravite u tabeli datuma su neophodne za druga polja, ali zapravo nisu toliko korisne za analizu. Na primer, polje "Šifra datuma" u tabeli "Prodaja" koje smo pomenuli i prikazali u ovom članku važno je zato što se za svaku transakciju zapisuje da se ta transakcija dešava određenog datuma i vremena. Ali sa stanovišta analize i izveštavanja, to nije korisno zato što ne možemo da ga koristimo kao red, kolonu ili polje filtera u izvedenoj tabeli ili izveštaju.

Slično tome, u našem primeru, kolona "Datum" u tabeli "Calendar je veoma korisna, zapravo kritična, ali je ne možete koristiti kao dimenziju u izvedenoj tabeli.

Da bi tabele i kolone u njima bile što korisnije, a navigacija po listama polja izvedene tabele ili Power View izveštaja bila lakša, važno je sakriti nepotrebne kolone od klijentskih alatki. Možda ćete želeti da sakrijete i određene tabele. Tabela "Praznici" koja je prikazana ranije sadrži datume praznika koji su važni za određene kolone u tabeli Calendar, ali ne možete da koristite kolone "Datum" i "Praznici" u samim tabelama "Praznici" kao polja u izvedenoj tabeli. I ovde, da biste olakšali navigaciju po listama polja, možete sakriti celu tabelu "Praznici".

Drugi važan aspekt rada sa datumima jeste imenovanje konvencija. Tabele i kolone u programskom dodatku Power Pivot možete da imenujete kako god želite. Međutim, imajte na umu da, naročito ako ćete radnu svesku deliti sa drugim korisnicima, dobra konvencija imenovanja olakšava identifikovanje tabela i datuma, ne samo u listama polja, već i u programskom dodatku Power Pivot i DAX formulama.

Kada napravite tabelu sa datumima u modelu podataka, možete da počnete da pravite mere koje će vam pomoći da na najbolji način iskoristite podatke. Neki mogu biti jednostavni kao sabiranje ukupnih vrednosti prodaje za trenutnu godinu, a drugi mogu biti složeniji, gde je potrebno da filtrirate po određenom opsegu jedinstvenih datuma. Više saznajte u članku "Mere" u programskom dodatku Power Pivot i funkcijama vremenske inteligencije.

Dodatak

Konvertovanje datuma tekstualnog tipa podataka u tip podataka datuma

U nekim slučajevima, tabela sa podacima o transakcijama može da sadrži datume tekstualnog tipa podataka. To jest, datum koji se pojavljuje kao 2012-12-04T11:47:09 u stvari uopšte nije datum ili bar nije tip datuma koji Power Pivot može da razume. To je zapravo samo tekst koji se čita kao datum. Da biste kreirali relaciju između kolone sa datumima u tabeli sa činjenicama i kolone "Datum" u tabeli datuma, obe kolone moraju biti tipa podataka "Datum ".

Kada obično pokušate da promenite tip podataka za kolonu datuma koji su tekstualni tip podataka u tip podataka datuma, Power Pivot obično može da interpretira datume i automatski ih konvertuje u pravi tip podataka datuma. Ako Power Pivot ne može da izvrši konverziju tipa podataka, dobićete grešku nepodudaranja tipa.

Međutim, i dalje možete da konvertujete datume u pravi tip podataka datuma. Možete da napravite novu izračunatu kolonu i koristite DAX formulu za raščlanjivanje godine, meseca, dana, vremena itd. od tekstualnih niski, a zatim da ih ponovo spojite na način koji Power Pivot može da čita kao pravi datum.

U ovom primeru, uvezli smo tabelu sa činjenicama pod imenom "Prodaja" u Power Pivot. Sadrži kolonu pod imenom "DatumVreme". Vrednosti izgledaju ovako:

Ako pogledamo tip podataka u grupi "Oblikovanje", videćemo da je to "Tekstualni tip podataka".

Ne možemo da kreiramo relaciju između kolona "Datum/vreme" i "Datum" u tabeli datuma jer se tipovi podataka ne podudaraju. Ako pokušamo da promenimo tip podataka u "Datum", dobijamo grešku nepodudaranja tipa:

U ovom slučaju, Power Pivot nije mogao da konvertuje tip podataka iz teksta u datum. I dalje možemo da koristimo ovu kolonu, ali da bismo je stavili u pravi tip podataka "Datum", moramo da napravimo novu kolonu koja raščlanjuje tekst i ponovo ga kreira u vrednost koju Power Pivot može da napravi tip podataka "Datum".

Ne zaboravite, iz odeljka "Rad sa vremenom" ranije u ovom članku; Osim ako nije neophodno da analiza bude na nivou preciznosti doba dana, trebalo bi da konvertujete datume u tabeli sa činjenicama u nivo preciznosti jednog dana. Imajući to na umu, želimo da vrednosti u novoj koloni budu na nivou preciznosti za dan (isključujući vreme). Možemo da konvertujemo vrednosti u koloni "DatumVreme" u tip podataka datuma i da uklonimo nivo preciznosti vremena pomoću sledeće formule:

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

To nam daje novu kolonu (u ovom slučaju pod imenom "Datum"). Power Pivot čak otkriva vrednosti kao datume i automatski postavlja tip podataka na vrednost "Datum".

Ako želimo da očuvamo vremenski nivo preciznosti, jednostavno proširimo formulu tako da uključuje 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 kolonu "Datum" koja ima tip podataka "Datum", možemo da napravimo relaciju između nje i kolone datuma u datumu.

Dodatni resursi

Datumi u programskom dodatku Power Pivot

Izračunavanja u programskom dodatku Power Pivot

Brzi početak: Naučite DAX osnove za 30 minuta

Referenca za izraze za analizu podataka

Centar za DAX resurse