Forstå og opprette datatabeller i Power Pivot for Excel

Opprette en datotabell i Excel

Du kan opprette en datotabell i Excel og deretter kopiere den til en ny tabell i datamodellen. Dette er 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. Du kan deretter opprette flere kolonner som år, kvartal, måned, regnskapsår, periode osv. i Excel-regnearket ved hjelp av Excel-formler, eller, etter at du har kopiert tabellen til datamodellen, kan du opprette dem som beregnede kolonner. Oppretting av flere datokolonner i Power Pivot er beskrevet i delen Legg til nye datokolonner i datotabellen senere i denne artikkelen.

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 å opprette en relasjon mellom faktatabellen og datotabellen. Men den ene datokolonnen med én rad for hver dag er ikke nyttig når du analyserer etter datoer i en pivottabell eller Power View-rapport. Du vil at datotabellen skal inkludere kolonner som hjelper deg med å aggregere dataene for et område eller en gruppe med datoer. Du kan for eksempel summere salgsbeløp etter måned eller kvartal, eller du kan opprette et mål som beregner vekst fra år til år. I hvert av disse tilfellene trenger datotabellen års-, måned- 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.

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å kolleger i Excel, finnes det noen viktige forskjeller:

• DAX-dato- og klokkeslettfunksjoner bruker en datetime-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, regnskapsmåned og så videre.

DAX inkluderer følgende dato- og klokkeslettfunksjoner:

• DADDEL

• DATOVERDI

• NESTE DAG

• DAG.ETTER

• EOMONTH

• TIME

• MINUTT

• MÅNED

• NÅ

• ANDRE

• TID

• TIDSVERDI

• I DAG

• HVERDAG

• WEEKNUM

• ÅR

• YEARFRAC

Det finnes mange andre DAX-funksjoner du også kan bruke i formlene. Mange av formlene som er beskrevet her, bruker for eksempel matematiske og trigonometriske funksjoner som MOD og TRUNC, logiske funksjoner som HVIS og tekstfunksjoner som FORMAT Hvis du vil ha mer informasjon om andre DAX-funksjoner, kan du se delen Flere ressurser senere i denne artikkelen.

Formeleksempler 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.

Månedsnavn

=FORMAT([date],"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 skal vise alle tegnene, så vi bruker «mmmm». Resultatet vårt ser slik ut:

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 å hente dagnavnet. Fordi vi bare vil ha et forkortet dagnavn, angir vi «ddd» i formatargumentet.

Eksempel på pivottabell

Når du har felt for datoer som år, kvartal, måned osv., kan du bruke dem i en pivottabell eller rapport. Bildet nedenfor viser for eksempel SalesAmount-feltet fra faktatabellen Salg i VALUES, og Year and Quarter fra dimensjonstabellen Kalender i RADER. SalesAmount aggregeres for kontekst for år og kvartal.

Formeleksempler for et regnskapsår

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 totalsummene for nye år ved å legge til et feriefelt 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.

Å inkludere helligdager eller spesielle dager er ganske enkelt. Du kan opprette en tabell i Excel som har datoene du vil inkludere. Deretter kan du 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 LOOKUPVALUE-funksjonen til å returnere verdier.

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

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

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

La oss se nærmere på denne formelen.

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

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

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

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

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

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

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

Egendefinert kalender – tretten fire ukers perioder

Noen organisasjoner, for eksempel detaljhandel eller matservice, 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 vil helligdager falle innenfor samme periode hvert år. Du kan velge å starte en periode på 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 begynner regnskapsåret 01.07.

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, slik:

=INT([Uke])

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

=INT([Uke]+3)

Del deretter resultatet med 4, slik som dette:

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

Resultatet ser slik ut:

Periodens regnskapsår

Denne verdien returnerer regnskapsåret for en periode.

=INT(([Periode]+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:

= ([Periode]+12)

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

=(([Periode]+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 returnerer et heltall, når dividert med 13, slik:

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

Resultatet ser slik ut:

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 vil beskrive den først på et språk vi forstår bedre. Denne formelen sier at du deler 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 MOD (matematiske og trigonometriske funksjoner) som dette:

=REST([Periode],13)

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

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

Resultatet ser slik ut:

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 de 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 regnskapskvartal. Et mål kalt Totalt salg, med formelen Totalt salg:=SUMMER([SalesAmount]), plasseres i VALUES, og feltene FiscalYear og FiscalQuarter fra kalenderdatotabellen plasseres i RADER.

Denne direkte pivottabellen fungerer på riktig måte fordi vi ønsker å summere det totale salget etter transaksjonsdatoeni DateKey. Målet vårt for totalt salg 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 kalendertabellen.

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 ShipDate-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: Beregne en sum for SalesAmount, men filtrer ved hjelp av relasjonen mellom ShipDate-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 VALUES, og regnskapsår og regnskapskvartal på RADER, ser vi den samme totalsummen, men alle andre sumbeløp for regnskapsåret og regnskapskvartalet er forskjellige fordi de er basert på forsendelsesdatoen og ikke transaksjonsdatoen.

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

Flere datotabeller

En annen måte å arbeide med flere datokolonner i faktatabellen på, er å opprette flere datotabeller og opprette separate aktive relasjoner mellom dem. La oss se på salgstabelleksemplet 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 solgte varer ble sendt til kunden.

• En Returdato – 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 våre basert på disse datoene, så vi vil absolutt ønske en relasjon mellom den og Dato-kolonnen i Kalender-tabellen. Hvis vi ikke vil opprette inaktive relasjoner mellom Forsendelsesdato og Returdato og Dato-feltet i Kalender-tabellen, og dermed kreve spesielle målformler, kan vi opprette flere datotabeller for forsendelsesdato og returdato. Vi kan deretter opprette aktive relasjoner mellom dem.

I dette eksemplet har vi opprettet en annen datotabell med navnet ShipCalendar. Dette betyr selvfølgelig også å opprette flere datokolonner, og fordi disse datokolonnene er i en annen datotabell, ønsker vi å gi dem navn på en måte som skiller dem fra de samme kolonnene i kalendertabellen. Vi har for eksempel opprettet kolonner kalt ShipYear, ShipMonth, ShipQuarter og så videre.

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

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 sender 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, krever det mer minne. Eksperimenter med det som fungerer best for deg.

Fremgangsmåte: Angi egenskapen Datotabell

1. Velg Kalender-tabellen i 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.

Konvertere tekstdatatypedatoer til en datatype for dato

I noen tilfeller kan en faktatabell med transaksjonsdata inneholde datoer for tekstdatatypen. Det vil se ut som en dato som vises som 2012-12-04T11:47:09 er faktisk ikke en dato i det hele tatt, eller i det minste ikke 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 .

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

Du kan imidlertid likevel 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 osv. fra tekststrengene og deretter sette den sammen igjen 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:

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

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:

I dette tilfellet kan 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 lage en datatype for dato.

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

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

Dette gir oss en ny kolonne (i dette tilfellet kalt Dato). Power Pivot oppdager til og med at verdiene er datoer og angir datatypen automatisk til Dato.

Hvis vi ønsker å bevare tidsnivået for presisjon, utvider vi ganske enkelt formelen slik at den inkluderer timer, minutter og sekunder.

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

TIME(MID([DateTime],12,2), MID([DateTime],15,2), MID([DateTime],18,2))

Nå som vi har en datokolonne med 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