Logg på med Microsoft
Logg på, eller opprett en konto.
Hei,
Velg en annen konto.
Du har flere kontoer
Velg kontoen du vil logge på med.

Datotabeller i Power Pivot er avgjørende for å bla gjennom og beregne data over tid. Denne artikkelen gir en grundig forståelse av datotabeller og hvordan du kan opprette dem i Power Pivot. Spesielt beskriver denne artikkelen:

  • Hvorfor en datotabell er viktig for å bla gjennom og beregne data etter datoer og klokkeslett.

  • Slik bruker du Power Pivot til å legge til en datotabell i datamodellen.

  • Slik oppretter du nye datokolonner, for eksempel År, Måned og Periode i en datotabell.

  • Slik oppretter du relasjoner mellom datotabeller og faktatabeller.

  • Slik arbeider du med tid.

Denne artikkelen er ment for brukere som ikke har brukt Power Pivot før. Det er imidlertid viktig å allerede ha en god forståelse av å importere data, opprette relasjoner og opprette beregnede kolonner og mål.

Denne artikkelen beskriver ikke hvordan du bruker DAX-Time-Intelligence i måleformler. Hvis du vil ha mer informasjon om hvordan du oppretter mål med DAX-funksjoner for tidsintelligens, kan du se Tidsintelligens i Power Pivot i Excel.

Obs!: I Power Pivot er navnene «mål» og «beregnet felt» synonymt. Vi bruker navnemålet i hele denne artikkelen. Hvis du vil ha mer informasjon, kan du se Mål i Power Pivot.

Innhold

Forstå datotabeller

Nesten alle dataanalyser innebærer å bla gjennom og sammenligne data over datoer og klokkeslett. Du kan for eksempel summere salgsbeløp for det siste regnskapskvartalet og deretter sammenligne disse totalsummene med andre kvartaler, eller du vil beregne en avslutningssaldo for måned for en konto. I hvert av disse tilfellene bruker du datoer som en måte å gruppere og aggregere salgstransaksjoner eller saldoer for en bestemt periode i tid.

Power View rapport

Pivottabell for totalt salg etter regnskapskvartal

En datotabell kan inneholde mange forskjellige representasjoner av datoer og klokkeslett. En datotabell vil for eksempel ofte ha kolonner som regnskapsår, måned, kvartal eller periode som du kan velge som felt fra en feltliste når du skal sortere og filtrere dataene i pivottabeller eller Power View rapporter.

Power View feltliste

Feltliste i Power View

For datokolonner som År, Måned og Kvartal for å inkludere alle datoene innenfor det respektive området, må datotabellen ha minst én kolonne med et sammenhengende sett med datoer. Det vil si at kolonnen må ha én rad for hver dag for hvert år som er inkludert i datotabellen.

Hvis for eksempel dataene du vil bla gjennom, har datoer fra 1. februar 2010 til 30. november 2012, og du rapporterer om et kalenderår, vil du ha en datotabell med minst et datointervall fra 1. januar 2010 til 31. desember 2012. Hvert år i datotabellen må inneholde alle dagene for hvert år. Hvis du oppdaterer dataene regelmessig med nyere data, kan det være lurt å kjøre sluttdatoen med et år eller to, slik at du ikke trenger å oppdatere datotabellen etter hvert som klokkeslettet går.

Datotabell med et sammenhengende sett med datoer

Datotabell med sammenhengende datoer

Hvis du rapporterer om et regnskapsår, kan du opprette en datotabell med et sammenhengende sett med datoer for hvert regnskapsår. Hvis for eksempel regnskapsåret begynner 1. mars, og du har data for regnskapsårene 2010 opp til gjeldende dato (for eksempel i FY 2013), kan du opprette en datotabell som begynner 01.03.2009 og inneholder minst hver dag i hvert regnskapsår til og med siste dato i regnskapsåret 2013.

Hvis du skal rapportere både kalenderåret og regnskapsåret, trenger du ikke å opprette separate datotabeller. En enkelt datotabell kan inneholde kolonner for et kalenderår, et regnskapsår og til og med en tretten fire ukers periodekalender. Det viktigste er at datotabellen inneholder et sammenhengende sett med datoer for alle år som er inkludert.

Legge til en datotabell i datamodellen

Du kan legge til en datotabell i datamodellen på flere måter:

  • Importer fra en relasjonsdatabase eller en annen datakilde.

  • Opprett en datotabell i Excel deretter kopiere eller koble til en ny tabell i Power Pivot.

  • Importer fra Microsoft Azure Marketplace.

La oss se nærmere på hver av disse.

Importere fra en relasjonsdatabase

Hvis du importerer noen eller alle dataene fra et datalager eller en annen type relasjonsdatabase, er det sannsynligvis allerede en datotabell og relasjoner mellom den og resten av dataene du importerer. Datoene og formatet samsvarer sannsynligvis med datoene i faktadataene, og datoene begynner sannsynligvis bra i fortiden og går langt ut i fremtiden. Datotabellen du vil importere, kan være svært stor og inneholder en rekke datoer utover det du må ta med i datamodellen. Du kan bruke de avanserte filterfunksjonene i tabellimportveiviseren i Power Pivot til å velge bare datoene og de bestemte kolonnene du virkelig trenger. Dette kan redusere størrelsen på arbeidsboken betydelig og forbedre ytelsen.

Veiviser for tabellimport

Dialogboksen Veiviser for tabellimport

I de fleste tilfeller trenger du ikke å opprette flere kolonner, for eksempel regnskapsår, uke, månedsnavn og så videre, fordi de allerede finnes i den importerte tabellen. I noen tilfeller kan det imidlertid hende at du må opprette flere datokolonner etter at du har importert datotabellen til datamodellen, avhengig av et bestemt rapporteringskrav. Heldigvis er dette enkelt å gjøre ved hjelp av DAX. Du vil lære mer om hvordan du oppretter datotabellfelt senere. Alle miljøer er forskjellige. Hvis du er usikker på om datakildene har en relatert dato eller kalendertabell, kan du kontakte databaseadministratoren.

Opprette en datotabell i Excel

Du kan opprette en datotabell i Excel og deretter kopiere den til en ny tabell i datamodellen. Dette er egentlig ganske enkelt å gjøre, og det gir deg mye fleksibilitet.

Når du oppretter en datotabell i Excel, begynner du med én kolonne med et sammenhengende datoområde. Deretter kan du opprette flere kolonner, for eksempel År, Kvartal, Måned, Regnskapsår, Periode og så videre i Excel-regnearket ved hjelp av Excel-formler, eller når du har kopiert tabellen til datamodellen, kan du opprette dem som beregnede kolonner. Oppretting av flere datokolonner i Power Pivot er beskrevet i delen Legge til nye datokolonner i datotabellen senere i denne artikkelen.

Slik oppretter du en datotabell i Excel og kopierer den til datamodellen

  1. Skriv Excel kolonneoverskrift i celle A1 i et tomt regneark i celle A1for å identifisere et datoområde. Vanligvis vil dette være noe sånt som Date, DateTime eller DateKey.

  2. Skriv inn en startdato i celle A2. For eksempel 01.01.2010.

  3. Klikk fyllhåndtaket, og dra det ned til et radnummer som inneholder en sluttdato. For eksempel 31.12.2016.

    Datokolonne i Excel

  4. Merk alle radene i Dato-kolonnen (inkludert overskriftsnavnet i celle A1).

  5. Klikk Formater som tabell i Stiler-gruppen,og velg deretter en stil.

  6. Klikk OK i dialogboksen Formater som tabell.

    Datokolonne i Power Pivot

  7. Kopier alle rader, inkludert toppteksten.

  8. Klikk Lim inn på Hjem-fanen i Power Pivot.

  9. Skriv inn et navn > for eksempel Dato eller Kalender i Forhåndsvisning av innliming. La Bruk første rad være merket av for Kolonneoverskrifter,og klikk deretter OK.

    Forhåndsvisning av innliming

    Den nye datotabellen (kalt Kalender i dette eksemplet) i Power Pivot ser slik ut:

    Datotabell i Power Pivot

    Obs!: Du kan også opprette en koblet tabell ved hjelp av Legg til i datamodell. Dette gjør imidlertid arbeidsboken unødvendig stor fordi arbeidsboken har to versjoner av datotabellen. én i Excel og én i Power Pivot.

Obs!:  Navnedatoen er et nøkkelord i Power Pivot. Hvis du gir tabellen et navn som du oppretter i Power Pivot-datoen, må du omslutte tabellnavnet med enkle anførselstegn i eventuelle DAX-formler som refererer til det i et argument. Alle eksemplene på bilder og formler i denne artikkelen refererer til en datotabell opprettet i Power Pivot kalt Kalender.

Du har nå en datotabell i datamodellen. Du kan legge til nye datokolonner, for eksempel År, Måned og så videre, ved hjelp av DAX.

Legge til nye datokolonner i datotabellen

En datotabell med én enkelt datokolonne som har én rad for hver dag for hvert år, er viktig for å definere alle datoene i et datoområde. Det er også nødvendig for å opprette en relasjon mellom faktatabellen og datotabellen. Men den enkle datokolonnen med én rad for hver dag er ikke nyttig når du analyserer etter datoer i en pivottabell eller Power View rapporten. Du vil at datotabellen skal inkludere kolonner som hjelper deg med å aggregere dataene for et datoområde eller en gruppe datoer. Du vil for eksempel kanskje summere salgsbeløp etter måned eller kvartal, eller du kan opprette et mål som beregner veksten fra år til år. I hvert av disse tilfellene trenger datotabellen år-, måneds- eller kvartalskolonner som lar deg aggregere dataene for denne perioden.

Hvis du importerte datotabellen fra en relasjonsdatakilde, kan den allerede inneholde de ulike typene datokolonner du vil bruke. I noen tilfeller vil du kanskje endre noen av disse kolonnene eller opprette flere datokolonner. Dette gjelder spesielt hvis du oppretter din egen datotabell i Excel og kopierer den til datamodellen. Heldigvis er det ganske enkelt å opprette nye datokolonner i Power Pivot med dato- og klokkeslettfunksjoner i DAX.

Tips!: Hvis du ennå ikke har arbeidet med DAX, er et flott sted å begynne å lære med Hurtigstart: Lær GRUNNLEGGENDE OM DAX på 30 minutter på Office.com.

DAX-dato- og klokkeslettfunksjoner

Hvis du noen gang har arbeidet med dato- og klokkeslettfunksjoner i Excel formler, vil du sannsynligvis bli kjent med dato- og klokkeslettfunksjonene. Selv om disse funksjonene ligner på motpartene i Excel, er det noen viktige forskjeller:

  • DAX-dato- og klokkeslettfunksjoner bruker en dato/klokkeslett-datatype.

  • De kan ta verdier fra en kolonne som et argument.

  • De kan brukes til å returnere og/eller manipulere datoverdier.

Disse funksjonene brukes ofte når du oppretter egendefinerte datokolonner i en datotabell, så de er viktige å forstå. Vi bruker en rekke av disse funksjonene til å opprette kolonner for År, Kvartal, Regnskapsår og så videre.

Obs!: Dato- og klokkeslettfunksjoner i DAX er ikke det samme som tidsintelligensfunksjoner. Lær mer om Tidsintelligens i Power Pivot i Excel 2013.

DAX inneholder følgende dato- og klokkeslettfunksjoner:

Det finnes også mange andre DAX-funksjoner du kan bruke i formlene. Mange av formlene som er beskrevet her, bruker for eksempel matematiske og trigonometriske funksjoner som REST og AVKORT, Logiske funksjoner som HVISog Tekstfunksjoner som FORMAT Hvis du vil ha mer informasjon om andre DAX-funksjoner, kan du se Inndelingen Flere ressurser senere i denne artikkelen.

Eksempler på formler for et kalenderår

Eksemplene nedenfor beskriver formler som brukes til å opprette flere kolonner i en datotabell kalt Kalender. Én kolonne, kalt Dato, finnes allerede og inneholder et sammenhengende datoområde fra 01.01.2010 til 31.12.2016.

År

=ÅR([dato])

I denne formelen returnerer ÅR-funksjonen året fra verdien i Dato-kolonnen. Fordi verdien i Dato-kolonnen er av datatypen datetime, vet ÅR-funksjonen hvordan du returnerer året fra den.

Årskolonnen

Måned

=MÅNED([dato])

I denne formelen, akkurat som med ÅR-funksjonen, kan vi ganske enkelt bruke MÅNED-funksjonen til å returnere en månedsverdi fra Dato-kolonnen.

Månedskolonnen

Kvartal

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

I denne formelen bruker vi HELTALL-funksjonen til å returnere en datoverdi som et heltall. Argumentet vi angir for INT-funksjonen, er verdien fra Måned-kolonnen, legg til 2 og del deretter dette med 3 for å få kvartalet, 1 til 4.

Kvartalskolonnen

Månedsnavn

=FORMAT([dato];"mmmm")

I denne formelen bruker vi FORMAT-funksjonen til å konvertere en numerisk verdi fra Dato-kolonnen til tekst for å få månedsnavnet. Vi angir Dato-kolonnen som det første argumentet, og deretter formatet. vi vil at månedsnavnet vårt skal vise alle tegn, så vi bruker «mmmm». Resultatet vårt ser slik ut:

Månedsnavnkolonnen

Hvis vi vil returnere månedsnavnet forkortet til tre bokstaver, bruker vi «mmm» i formatargumentet.

Dag i uke

=FORMAT([dato];"ddd")

I denne formelen bruker vi FORMAT-funksjonen til å få dagsnavnet. Fordi vi bare vil ha et forkortet dagnavn, angir vi «ddd» i formatargumentet.

Kolonnen for ukedag
Eksempel på pivottabell

Når du har felt for datoer som År, Kvartal, Måned og så videre, kan du bruke dem i en pivottabell eller rapport. Bildet nedenfor viser for eksempel SalesAmount-feltet fra faktatabellen Salg i VERDIER, og År og Kvartal fra dimensjonstabellen kalender i RADER. SalesAmount er aggregert for år- og kvartalskontekst.

Eksempel på pivottabell

Eksempler på formler for et regnskapsår

Regnskapsår

=HVIS([Måned]<= 6,[År],[År]+1)

I dette eksemplet begynner regnskapsåret 1. juli.

Det finnes ingen funksjon som kan trekke ut et regnskapsår fra en datoverdi fordi start- og sluttdatoene for et regnskapsår ofte er forskjellige fra de i et kalenderår. For å få regnskapsåret bruker vi først en HVIS-funksjon til å teste om verdien for Måned er mindre enn eller lik 6. Hvis verdien for Måned i det andre argumentet er mindre enn eller lik 6, returneres verdien fra År-kolonnen. Hvis ikke, returnerer du verdien fra År og legger til 1.

Kolonnen for regnskapsår

En annen måte å angi en månedsverdi for regnskapsåret på, er å opprette et mål som ganske enkelt angir måneden. Eksempel: FYE:=6. Deretter kan du referere til målnavnet i stedet for månedsnummeret. Eksempel: =HVIS([Måned]<=[FYE],[År],[År]+1). Dette gir mer fleksibilitet når du refererer til sluttdatoen for regnskapsåret i flere forskjelligeformler.

Regnskapsårsmåned

=HVIS([Måned]<= 6, 6+[Måned], [Måned]- 6)

I denne formelen angir vi om verdien for [Måned] er mindre enn eller lik 6, tar deretter 6 og legger til verdien fra Måned, ellers trekker 6 fra verdien fra [Måned].

Kolonnen for regnskapsmåned

Regnskapskvartal

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

Formelen vi bruker for FiscalQuarter er mye den samme som for Kvartal i kalenderåret vårt. Den eneste forskjellen er at vi angir [FiscalMonth] i stedet for [Month].

Kolonnen for regnskapskvartal

Helligdager eller spesielle datoer

Du vil kanskje inkludere en datokolonne som angir at bestemte datoer er helligdager eller en annen spesiell dato. Du kan for eksempel summere salgstotaler for nyttårsdag ved å legge til et Helligdag-felt i en pivottabell, som en slicer eller et filter. I andre tilfeller vil du kanskje utelate disse datoene fra andre datokolonner eller i et mål.

Det er ganske enkelt å inkludere helligdager eller spesielle dager. Du kan opprette en tabell i Excel som har datoene du vil inkludere. Du kan deretter kopiere eller bruke Legg til i datamodell for å legge den til i datamodellen som en koblet tabell. I de fleste tilfeller er det ikke nødvendig å opprette en relasjon mellom tabellen og kalendertabellen. Alle formler som refererer til den, kan bruke FUNKSJONEN SLÅ.OPPVERDI til å returnere verdier.

Nedenfor finner du et eksempel på en tabell som er Excel som inkluderer helligdager som skal legges til i datotabellen:

Dato

Helligdag

1/1/2010

Nytt år

11/25/2010

Takkefest

12/25/2010

Jul

01.01.2011

Nytt år

11/24/2011

Takkefest

12/25/2011

Jul

01.01.2012

Nytt år

22.11.2012

Takkefest

12/25/2012

Jul

1/1/2013

Nytt år

11/28/2013

Takkefest

12/25/2013

Jul

11/27/2014

Takkefest

12/25/2014

Jul

01.01.2014

Nytt år

11/27/2014

Takkefest

12/25/2014

Jul

1/1/2015

Nytt år

11/26/2014

Takkefest

12/25/2015

Jul

01.01.2016

Nytt år

11/24/2016

Takkefest

12/25/2016

Jul

I datotabellen oppretter vi en kolonne kalt Ferie og bruker en formel som dette:

=SLÅ.OPPVERDI(Helligdager[Ferie],Helligdager[dato];Kalender[dato])

La oss se nærmere på denne formelen.

Vi bruker FUNKSJONEN SLÅ.OPPVERDI til å hente verdier fra Helligdag-kolonnen i Helligdager-tabellen. I det første argumentet angir vi kolonnen der resultatverdien skal være. Vi angir Helligdag-kolonnen i Helligdager-tabellen fordi det er verdien vi vil returnere.

=SLÅ.OPPVERDI(Helligdager[Ferie],Helligdager[dato];Kalender[dato])

Deretter angir vi det andre argumentet, søkekolonnen som har datoene vi vil søke etter. Vi angir Dato-kolonnen i Helligdager-tabellen, slik som dette:

=SLÅ.OPPVERDI(Helligdager[Ferie],Helligdager[dato],Kalender[dato])

Til slutt angir vi kolonnen i Kalender-tabellen som har datoene vi vil søke etter i ferietabellen. Dette er selvfølgelig Dato-kolonnen i Kalender-tabellen.

=SLÅ.OPPVERDI(Helligdager[Ferie],Helligdager[dato],Kalender[dato])

Helligdag-kolonnen returnerer ferienavnet for hver rad som har en datoverdi som samsvarer med en dato i Helligdager-tabellen.

Helligdagstabellen

Egendefinert kalender – tretten fire ukers perioder

Noen organisasjoner, for eksempel detaljhandel eller mattjeneste, rapporterer ofte om ulike perioder, for eksempel tretten fire ukers perioder. Med en tretten fire ukers periodekalender er hver periode 28 dager. Hver periode inneholder derfor fire mandager, fire tirsdager, fire onsdager og så videre. Hver periode inneholder samme antall dager, og vanligvis faller helligdagene innenfor samme periode hvert år. Du kan velge å starte en periode på en hvilken som helst dag i uken. Akkurat som med datoer i en kalender eller et regnskapsår, kan du bruke DAX til å opprette flere kolonner med egendefinerte datoer.

I eksemplene nedenfor starter den første hele perioden den første søndagen i regnskapsåret. I dette tilfellet starter regnskapsåret 01.07.2017.

Uke

Denne verdien gir oss ukenummeret fra og med den første hele uken i regnskapsåret. I dette eksemplet begynner den første hele uken på søndag, slik at den første hele uken i det første regnskapsåret i kalendertabellen faktisk starter 04.07.2010 og fortsetter gjennom den siste hele uken i Kalender-tabellen. Selv om denne verdien i seg selv ikke er så nyttig i analyser, er det nødvendig å beregne for bruk i andre 28-dagers periodeformler.

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

La oss se nærmere på denne formelen.

Først oppretter vi en formel som returnerer verdier fra Dato-kolonnen som et heltall, slik som dette:

=INT([date])

Vi vil deretter se etter den første søndagen i det første regnskapsåret. Vi ser at det er 04.07.2010.

Ukekolonnen

Trekk nå 40356 (som er heltallet for 27.06.2010, den siste søndagen fra forrige regnskapsår) fra denne verdien for å få antall dager siden starten på dagene i kalendertabellen, slik som dette:

=INT([date]-40356)

Del deretter resultatet med 7 (dager i en uke), slik som dette:

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

Resultatet ser slik ut:

Ukekolonnen

Punktum

Perioden i denne egendefinerte kalenderen inneholder 28 dager, og den vil alltid begynne på en søndag. Denne kolonnen returnerer nummeret på perioden som begynner med den første søndagen i det første regnskapsåret.

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

La oss se nærmere på denne formelen.

Først oppretter vi en formel som returnerer en verdi fra Uke-kolonnen som et heltall, som dette:

=INT([Uke])

Legg deretter til 3 i denne verdien, slik som dette:

=INT([Uke]+3)

Del deretter resultatet med 4, slik som dette:

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

Resultatet ser slik ut:

Kolonnen for periode

Regnskapsår for periode

Denne verdien returnerer regnskapsåret for en periode.

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

La oss se nærmere på denne formelen.

Først oppretter vi en formel som returnerer en verdi fra Periode og legger til 12:

= ([Punktum]+12)

Vi deler resultatet med 13, fordi det er tretten 28 dagers perioder i regnskapsåret:

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

Vi legger til 2010, fordi det er det første året i tabellen:

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

Til slutt bruker vi HELTALL-funksjonen til å fjerne en brøkdel av resultatet og returnere et helt tall, når det deles på 13, slik som dette:

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

Resultatet ser slik ut:

Kolonnen for regnskapsår for periode

Periode i Regnskapsår

Denne verdien returnerer periodenummeret, 1– 13, og starter med den første hele perioden (fra og med søndag) i hvert regnskapsår.

=HVIS(REST([Periode];13), REST([Periode];13);13)

Denne formelen er litt mer kompleks, så vi beskriver den først på et språk vi bedre forstår. Denne formelen sier, divider verdien fra [Periode] med 13 for å få et periodenummer (1-13) i året. Hvis dette tallet er 0, returnerer du 13.

Først oppretter vi en formel som returnerer resten av verdien fra periode med 13. Vi kan bruke FUNKSJONENE REST (matematiske og trigonometriske funksjoner) slik:

=REST([Punktum];13)

Dette gir oss for det meste resultatet vi ønsker, bortsett fra der verdien for perioden er 0 fordi disse datoene ikke faller innenfor det første regnskapsåret, for eksempel i de første fem dagene i kalenderdatotabellen. Vi kan ta oss av dette med en HVIS-funksjon. I tilfelle resultatet er 0, returnerer vi 13, som dette:

=HVIS(REST([Periode];13);REST([Periode];13);13)

Resultatet ser slik ut:

Kolonnen for periode i regnskapsår

Eksempel på pivottabell

Bildet nedenfor viser en pivottabell med SalesAmount-feltet fra faktatabellen Salg i VERDIER, og PeriodFiscalYear- og PeriodInFiscalYear-feltene fra tabellen For kalenderdatodimensjon i RADER. SalesAmount aggregeres for konteksten etter regnskapsår og 28-dagers periode i regnskapsåret.

Eksempel på pivottabell for regnskapsår

Relasjoner

Når du har opprettet en datotabell i datamodellen, må du opprette en relasjon mellom faktatabellen og transaksjonsdataene og datotabellen for å begynne å bla gjennom dataene i pivottabeller og rapporter, og for å aggregere data basert på kolonnene i datodimensjonstabellen.

Fordi du må opprette en relasjon basert på datoer, må du passe på at du oppretter denne relasjonen mellom kolonner med verdier av datatypen datetime (Date).

For hver datoverdi i faktatabellen må den relaterte oppslagskolonnen i datotabellen inneholde samsvarende verdier. En rad (transaksjonspost) i salgsfellestabellen med en verdi på 15.08.2012 12:00 i DateKey-kolonnen må ha en tilsvarende verdi i den relaterte Dato-kolonnen i datotabellen (kalt Kalender). Dette er en av de viktigste grunnene til at du vil at datokolonnen i datotabellen skal inneholde et sammenhengende datoområde som inneholder alle mulige datoer i faktatabellen.

Relasjoner i diagramvisning

Obs!: Datokolonnen i hver tabell må være av samme datatype (dato), men formatet på hver kolonne spiller ingen rolle.

Obs!: Hvis Du ikke kan opprette relasjoner mellom de to tabellene i Power Pivot, kan det hende at datofeltene ikke lagrer datoen og klokkeslettet til samme presisjonsnivå. Avhengig av kolonneformateringen kan verdiene se like ut, men lagres på en annen måte. Les mer om hvordan du arbeider med tid.

Obs!: Unngå å bruke heltalls surrogatnøkler i relasjoner. Når du importerer data fra en relasjonsdatakilde, representeres ofte dato- og klokkeslettkolonner av en surrogatnøkkel, som er en heltallskolonne som brukes til å representere en unik dato. I Power Pivot bør du unngå å opprette relasjoner ved hjelp av dato/klokkeslett-nøkler for heltall, og i stedet kan du bruke kolonner som inneholder unike verdier med en datodatatype. Selv om bruken av surrogatnøkler regnes som beste fremgangsmåte i tradisjonelle datalagre, er ikke heltallstastene nødvendige i Power Pivot, og kan gjøre det vanskelig å gruppere verdier i pivottabeller etter ulike datoperioder.

Hvis du får en typekonfliktsfeil når du prøver å opprette en relasjon, er det sannsynligvis fordi kolonnen i faktatabellen ikke er av datatypen Dato. Dette kan skje når Power Pivot ikke automatisk kan konvertere en ikke-dato (vanligvis en tekstdatatype) til en datodatatype. Du kan fortsatt bruke kolonnen i faktatabellen, men du må konvertere dataene med en DAX-formel i en ny beregnet kolonne. Se Konvertere datatypedatoer for tekst til en datodatatype senere i tillegget.

Flere relasjoner

I noen tilfeller kan det være nødvendig å opprette flere relasjoner eller opprette flere datotabeller. Hvis det for eksempel er flere datofelt i faktatabellen Salg, for eksempel DateKey, ShipDate og ReturnDate, kan alle ha relasjoner til Dato-feltet i kalenderdatotabellen, men bare én av disse kan være en aktiv relasjon. I dette tilfellet, fordi DateKey representerer datoen for transaksjonen, og derfor den viktigste datoen, vil dette best fungere som den aktive relasjonen. De andre har inaktive relasjoner.

Følgende pivottabell beregner totalt salg etter regnskapsår og regnskapsår. Et mål kalt Totalt salg, med formelen Totalt salg:=SUMMER([SalesAmount]), plasseres i VERDIER, og FiscalYear- og FiscalQuarter-feltene fra kalenderdatotabellen plasseres i RADER.

Pivottabell for totalt salg etter regnskapskvartal Feltlisten for pivottabell

Denne direkte pivottabellen fungerer på riktig måte fordi vi ønsker å summere det totale salget etter transaksjonsdatoeni DateKey. Totalsalgsmålet bruker datoene i DateKey og summeres etter regnskapsår og regnskapskvartal fordi det er en relasjon mellom DateKey i Salg-tabellen og Dato-kolonnen i kalenderdatotabellen.

Inaktive relasjoner

Men hva om vi ønsket å summere det totale salget ikke etter transaksjonsdato, men etter forsendelsesdato? Vi trenger en relasjon mellom ShipDate-kolonnen i Salg-tabellen og Dato-kolonnen i Kalender-tabellen. Hvis vi ikke oppretter denne relasjonen, er aggregasjonene våre alltid basert på transaksjonsdatoen. Vi kan imidlertid ha flere relasjoner, selv om bare én kan være aktiv, og fordi transaksjonsdato er den viktigste, får den den aktive relasjonen med Kalender-tabellen.

I dette tilfellet har ShipDate en inaktiv relasjon, så alle målformler som er opprettet for å aggregere data basert på forsendelsesdatoer, må angi den inaktive relasjonen ved hjelp av USERELATIONSHIP-funksjonen.

Fordi det for eksempel er en inaktiv relasjon mellom Forsendelsesdato-kolonnen i Salg-tabellen og Dato-kolonnen i Kalender-tabellen, kan vi opprette et mål som summerer totalt salg etter forsendelsesdato. Vi bruker en formel som dette til å angi relasjonen som skal brukes:

Totalt salg etter forsendelsesdato:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))

Denne formelen sier ganske enkelt: Beregn en sum for SalesAmount, men filtrer ved hjelp av relasjonen mellom Forsendelsesdato-kolonnen i Salg-tabellen og Dato-kolonnen i Kalender-tabellen.

Hvis vi nå oppretter en pivottabell og setter mål for totalt salg etter forsendelsesdato i VERDIER og Regnskapsår på RADER, ser vi den samme totalsummen, men alle andre summeringsbeløp for regnskapsåret og regnskapsåret er forskjellige fordi de er basert på forsendelsesdatoen og ikke transaksjonsdatoen.

Pivottabell for totalt salg etter forsendelsesdato Feltlisten for pivottabell

Ved hjelp av inaktive relasjoner kan du bare bruke én datotabell, men den krever at eventuelle mål (for eksempel Totalt salg etter forsendelsesdato) refererer til den inaktive relasjonen i formelen. Det finnes et annet alternativ, det vil si bruke flere datotabeller.

Flere datotabeller

En annen måte å arbeide med flere datokolonner på i faktatabellen, er å opprette flere datotabeller og opprette separate aktive relasjoner mellom dem. La oss se på salgstabelleksempelet vårt på nytt. Vi har tre kolonner med datoer vi kanskje vil aggregere data på:

  • En DateKey med salgsdatoen for hver transaksjon.

  • En Forsendelsesdato – med datoen og klokkeslettet da de solgte varene ble sendt til kunden.

  • En ReturnDate – med datoen og klokkeslettet da ett eller flere elementer som ble returnert, ble mottatt.

Husk at DateKey-feltet med transaksjonsdatoen er viktigst. Vi vil gjøre de fleste av aggregasjonene basert på disse datoene, så vi vil helt sikkert ha en relasjon mellom den og Dato-kolonnen i Kalender-tabellen. Hvis vi ikke vil opprette inaktive relasjoner mellom ShipDate og ReturnDate og Dato-feltet i Kalender-tabellen, og dermed krever spesialmålformler, kan vi opprette flere datotabeller for forsendelsesdato og returdato. Vi kan deretter opprette aktive relasjoner mellom dem.

Relasjoner med flere datotabeller i diagramvisning

I dette eksemplet har vi opprettet en annen datotabell kalt ShipCalendar. Dette betyr selvfølgelig også å opprette flere datokolonner, og fordi disse datokolonnene er i en annen datotabell, vil vi gi dem et navn som skiller dem fra de samme kolonnene i Kalender-tabellen. Vi har for eksempel opprettet kolonner kalt ShipYear, ShipMonth, ShipQuarter og så videre.

Hvis vi oppretter pivottabellen og setter totalsalgsmålet i VERDIER og ShipFiscalYear og ShipFiscalQuarter på RADER, ser vi de samme resultatene som vi så da vi opprettet en inaktiv relasjon og et spesielt beregnet felt for totalt salg etter forsendelsesdato.

Pivottabell for totalt salg etter forsendelsesdato med forsendelseskalender Feltliste for pivottabell

Hver av disse fremgangsmåtene krever nøye vurdering. Når du bruker flere relasjoner med én enkelt datotabell, må du kanskje opprette spesielle mål som transitt-inaktive relasjoner ved hjelp av USERELATIONSHIP-funksjonen. På den annen side kan det være forvirrende å opprette flere datotabeller i en feltliste, og fordi du har flere tabeller i datamodellen, vil det kreve mer minne. Eksperimenter med det som fungerer best for deg.

Datotabell, egenskap

Egenskapen Date Table angir metadata som er nødvendige for Time-Intelligence funksjoner som TOTALYTD, PREVIOUSMONTH og DATESBETWEEN til å fungere riktig. Når en beregning kjøres ved hjelp av en av disse funksjonene, vet Power Pivots formelmotor hvor du skal gå for å få datoene den trenger.

Advarsel!: Hvis denne egenskapen ikke er angitt, kan det hende at mål som bruker DAX-Time-Intelligence ikke returnerer riktige resultater.

Når du angir egenskapen Datotabell, angir du en datotabell og en datokolonne av datatypen Date (datetime) i den.

Dialogboksen Merk som datotabell

Slik angir du egenskapen Datotabell

  1. Velg kalendertabellen PowerPivot vinduet.

  2. Klikk Merk som datotabell på Utforming-fanen.

  3. Velg en kolonne med unike verdier og datatypen Dato i dialogboksen Merk som datotabell.

Arbeide med tid

Alle datoverdier med datatypen Dato i Excel eller SQL Server er faktisk et tall. Tall som er inkludert i dette tallet, er sifre som refererer til et klokkeslett. I mange tilfeller er dette tidspunktet for hver rad midnatt. Hvis for eksempel et DateTimeKey-felt i en faktatabell for salg har verdier som 19.10.2010 kl. 12:00:00, betyr dette at verdiene er til dagsnivået for presisjon. Hvis DateTimeKey-feltverdiene har et klokkeslett inkludert, for eksempel 19.10.2010 kl. 08:44:00, betyr dette at verdiene er på presisjonsnivået i minuttet. Verdier kan også være til presisjonen på timenivå eller til og med sekunders presisjonsnivå. Presisjonsnivået i tidsverdien vil ha en betydelig innvirkning på hvordan du oppretter datotabellen og relasjonene mellom den og faktatabellen.

Du må finne ut om du vil aggregere dataene til et dagsnivå med presisjon eller et tidsnivå med presisjon. Det kan med andre ord være lurt å bruke kolonner i datotabellen, for eksempel Morgen, Ettermiddag eller Time, som klokkeslettdatofelt i rad-, kolonne- eller filterområdene i en pivottabell.

Obs!: Dager er den minste tidsenheten som DAX-tidsintelligensfunksjonene kan arbeide med. Hvis du ikke trenger å arbeide med tidsverdier, bør du redusere presisjonen til dataene for å bruke dager som minimumsenhet.

Hvis du har tenkt å aggregere dataene til tidsnivået, trenger datotabellen en datokolonne med klokkeslettet som er inkludert. Den trenger faktisk en datokolonne med én rad for hver time, eller kanskje hvert minutt, hver dag, for hvert år i datoområdet. Dette er fordi du må ha samsvarende verdier for å opprette en relasjon mellom DateTimeKey-kolonnen i faktatabellen og datokolonnen i datotabellen. Som du kan forestille deg, hvis du inkluderer mange år, kan dette gi en svært stor datotabell.

I de fleste tilfeller vil du imidlertid bare aggregere dataene til dagen. Du vil med andre ord bruke kolonner som År, Måned, Uke eller Ukedag som felt i rad-, kolonne- eller filterområdene i en pivottabell. I dette tilfellet trenger datokolonnen i datotabellen bare inneholde én rad for hver dag i et år, som vi har beskrevet tidligere.

Hvis datokolonnen inneholder et presisjonsnivå, men du bare aggregerer til et dagsnivå, kan det hende du må endre faktatabellen ved å opprette en ny kolonne som avkorter verdiene i datokolonnen til en dagsverdi. Konverter med andre ord en verdi som 19.10.2010 08:44:00 til 19.10.2010 kl. 12:00:00. Du kan deretter opprette relasjonen mellom denne nye kolonnen og datokolonnen i datotabellen fordi verdiene samsvarer.

La oss se på et eksempel. Dette bildet viser en DateTimeKey-kolonne i faktatabellen salg. Alle aggregasjonene for dataene i denne tabellen trenger bare være på dagsnivå, ved å bruke kolonner i kalenderdatotabellen, for eksempel År, Måned, Kvartal og så videre. Klokkeslettet som er inkludert i verdien, er ikke relevant, bare den faktiske datoen.

DateTimeKey-kolonnen

Fordi vi ikke trenger å analysere disse dataene til tidsnivået, trenger vi ikke Dato-kolonnen i kalenderdatotabellen for å inkludere én rad for hver time og hvert minutt av hvert dag i hvert år. Så datokolonnen i datotabellen ser slik ut:

Datokolonne i Power Pivot

Hvis du vil opprette en relasjon mellom DateTimeKey-kolonnen i Salg-tabellen og Dato-kolonnen i Kalender-tabellen, kan vi opprette en ny beregnet kolonne i Salgsfysikk-tabellen og bruke AVKORT-funksjonen til å avkorte dato- og klokkeslettverdien i DateTimeKey-kolonnen til en datoverdi som samsvarer med verdiene i Dato-kolonnen i Kalender-tabellen. Formelen vår ser slik ut:

=AVKORT([DateTimeKey];0)

Dette gir oss en ny kolonne (vi kalt DateKey) med datoen fra DateTimeKey-kolonnen og et klokkeslett på 12:00:00 for hver rad:

DateKey-kolonnen

Nå kan vi opprette en relasjon mellom denne nye (DateKey)-kolonnen og Dato-kolonnen i Kalender-tabellen.

På samme måte kan vi opprette en beregnet kolonne i Salg-tabellen som reduserer tidspresisjonen i DateTimeKey-kolonnen til det timevise presisjonsnivået. I dette tilfellet vil ikke AVKORT-funksjonen fungere, men vi kan fortsatt bruke andre DAX-dato- og klokkeslettfunksjoner til å trekke ut og kjede sammen en ny verdi til et timenivå med presisjon. Vi kan bruke en formel som dette:

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

Den nye kolonnen ser slik ut:

DateTimeKey-kolonnen

Forutsatt at Dato-kolonnen i datotabellen har verdier til presisjonsnivået for time, kan vi deretter opprette en relasjon mellom dem.

Gjøre datoer mer brukervennlige

Mange av datokolonnene du oppretter i datotabellen, er nødvendige for andre felt, men det er ikke så nyttig i analysen. DateKey-feltet i Salg-tabellen vi har referert til og vist i hele denne artikkelen, er for eksempel viktig fordi for hver transaksjon registreres transaksjonen som skjer på en bestemt dato og et bestemt klokkeslett. Men fra et analyse- og rapporteringspunkt er det ikke så nyttig fordi vi ikke kan bruke det som et rad-, kolonne- eller filterfelt i en pivottabell eller rapport.

I vårt eksempel er Dato-kolonnen i Kalender-tabellen svært nyttig, faktisk viktig, men du kan ikke bruke den som en dimensjon i en pivottabell.

Hvis du vil at tabeller og kolonnene i dem skal være så nyttige som mulig, og gjøre det enklere å navigere i feltlister for pivottabeller eller Power View rapporter, er det viktig å skjule unødvendige kolonner fra klientverktøy. Det kan også være lurt å skjule enkelte tabeller også. Helligdager-tabellen som vises tidligere, inneholder helligdagsdatoer som er viktige for bestemte kolonner i Kalender-tabellen, men du kan ikke bruke kolonnene Dato og Helligdag i helligdager-tabellen som felt i en pivottabell. Hvis du vil gjøre det enklere å navigere i feltlister, kan du skjule hele Helligdager-tabellen.

Et annet viktig aspekt ved å arbeide med datoer er navngivning av konvensjoner. Du kan gi navn til tabeller og kolonner i Power Pivot hva du vil. Men husk, spesielt hvis du skal dele arbeidsboken med andre brukere, gjør en god navngivningskonvensjon det enklere å identifisere tabeller og datoer, ikke bare i feltlister, men også i Power Pivot og i DAX-formler.

Når du har en datotabell i datamodellen, kan du begynne å opprette mål som vil hjelpe deg med å få mest mulig ut av dataene. Noen kan være så enkle som å summere salgstotaler for gjeldende år, og andre kan være mer komplekse, der du må filtrere etter et bestemt område med unike datoer. Finn ut mer i Mål i Funksjoner for Power Pivot og Tidsintelligens.

Vedlegg

Konvertere datatypedatoer for tekst til en datodatatype

I noen tilfeller kan en faktatabell med transaksjonsdata inneholde datoer for tekstdatatypen. Det vil si at en dato som vises som 2012-12-04T11:47:09, faktisk ikke er en dato i det hele tatt, eller i det minste ikke den typen dato Power Pivot kan forstå. Det er egentlig bare tekst som leser som en dato. Hvis du vil opprette en relasjon mellom en datokolonne i faktatabellen og en datokolonne i en datotabell, må begge kolonnene være av datatypen Dato.

Når du prøver å endre datatypen for en kolonne med datoer som er datatypen tekst til en datodatatype, kan Power Pivot vanligvis tolke datoene og konvertere den til en sann datodatatype automatisk. Hvis Power Pivot ikke kan gjøre en datatypekonvertering, får du en typekonfliktsfeil.

Du kan imidlertid fortsatt konvertere datoene til en sann datodatatype. Du kan opprette en ny beregnet kolonne og bruke en DAX-formel til å analysere år, måned, dag, klokkeslett og så videre fra tekststrengene og deretter kjede den sammen på en måte som Power Pivot kan lese som en sann dato.

I dette eksemplet har vi importert en faktatabell kalt Salg til Power Pivot. Den inneholder en kolonne kalt DateTime. Verdier vises slik:

DateTime-kolonnen i en faktatabell.

Hvis vi ser på Datatype i Formatering-gruppen På Hjem-fanen i Power Pivot, ser vi at det er datatypen Tekst.

Datatype på båndet

Vi kan ikke opprette en relasjon mellom DateTime-kolonnen og Dato-kolonnen i datotabellen fordi datatypene ikke samsvarer. Hvis vi prøver å endre datatypen til Dato, får vi en typekonfliktsfeil:

Samsvarsfeil

I dette tilfellet kunne ikke Power Pivot konvertere datatypen fra tekst til dato. Vi kan fortsatt bruke denne kolonnen, men for å få den til en sann datodatatype, må vi opprette en ny kolonne som analyserer teksten og oppretter den på nytt til en verdi Som Power Pivot kan gjøre til datatypen Dato.

Husk at i delen Arbeide med tid tidligere i denne artikkelen; med mindre det er nødvendig, må analysen være på et tids-på-dag-nivå med presisjon, bør du konvertere datoer i faktatabellen til et dagsnivå med presisjon. Med dette i tankene ønsker vi at verdiene i den nye kolonnen skal være på dagnivå med presisjon (unntatt tid). Vi kan både konvertere verdiene i DateTime-kolonnen til en datodatatype og fjerne presisjonsnivået med følgende formel:

=DATO(VENSTRE([DateTime];4), DELTEKST([DateTime];6;2), DELTEKST([DateTime];9;2))

Dette gir oss en ny kolonne (i dette tilfellet kalt Dato). Power Pivot oppdager til og med verdiene som skal være datoer, og angir datatypen automatisk til Dato.

Datokolonne i faktatabell

Hvis vi vil bevare presisjonsnivået, utvider vi ganske enkelt formelen slik at den inkluderer timer, minutter og sekunder.

=DATO(VENSTRE([DateTime];4), DELTEKST([DateTime];6;2), DELTEKST([DateTime];9;2)) +

TID(DELTEKST([DateTime];12;2), DELTEKST([DateTime];15;2), DELTEKST([DateTime];18;2))

Nå som vi har en Dato-kolonne av datatypen Dato, kan vi opprette en relasjon mellom den og en datokolonne i en dato.

Flere ressurser

Datoer i Power Pivot

Beregninger i PowerPivot

Hurtigveiledning: Lær det grunnleggende om DAX på 30 minutter

Referanse for dataanalyseuttrykk

DAX-ressurssenter

Trenger du mer hjelp?

Vil du ha flere alternativer?

Utforsk abonnementsfordeler, bla gjennom opplæringskurs, finn ut hvordan du sikrer enheten og mer.

Fellesskap hjelper deg med å stille og svare på spørsmål, gi tilbakemelding og høre fra eksperter med stor kunnskap.

Var denne informasjonen nyttig?

Hvor fornøyd er du med språkkvaliteten?
Hva påvirket opplevelsen din?
Når du trykker på Send inn, blir tilbakemeldingen brukt til å forbedre Microsoft-produkter og -tjenester. IT-administratoren kan samle inn disse dataene. Personvernerklæring.

Takk for tilbakemeldingen!

×