Forstå og oprette datotabeller i PowerPivot i Excel

Opret en datotabel i Excel

Du kan oprette en datotabel i Excel og derefter kopiere den til en ny tabel i datamodellen. Dette er virkelig ganske nemt at gøre, og det giver dig en masse fleksibilitet.

Når du opretter en datotabel i Excel, starter du med en enkelt kolonne med et sammenhængende datoområde. Du kan derefter oprette flere kolonner, f.eks. År, Kvartal, Måned, Regnskabsår, Periode osv. i Excel-regnearket ved hjælp af Excel-formler, eller når du har kopieret tabellen til datamodellen, kan du oprette dem som beregnede kolonner. Oprettelse af flere datokolonner i Power Pivot er beskrevet i afsnittet Føje nye datokolonner til sektionen Datotabel senere i denne artikel.

Tilføjelse af nye datokolonner i datotabellen

En datotabel med en enkelt datokolonne, der har én række for hver dag for hvert år, er vigtig for at definere alle datoerne i et datointerval. Det er også nødvendigt for at oprette en relation mellem faktatabellen og datotabellen. Men den enkelte datokolonne med én række for hver dag er ikke nyttig, når du analyserer efter datoer i en pivottabel eller Power View-rapport. Du ønsker, at datotabellen skal indeholde kolonner, der hjælper dig med at aggregere dine data for et område eller en gruppe af datoer. Det kan f.eks. være, at du vil summere salgsbeløb efter måned eller kvartal, eller du kan oprette en måling, der beregner væksten år-for-år. I hvert af disse tilfælde skal datotabellen have år-, måneds- eller kvartalskolonner, så du kan aggregere dine data for den pågældende periode.

Hvis du har importeret datotabellen fra en relationel datakilde, indeholder den muligvis allerede de forskellige typer datokolonner, du ønsker. I nogle tilfælde kan det være en god ide at ændre nogle af disse kolonner eller oprette flere datokolonner. Dette gælder især, hvis du opretter din egen datotabel i Excel og kopierer den til datamodellen. Heldigvis er det ret nemt at oprette nye datokolonner i Power Pivot med dato- og klokkeslætsfunktioner i DAX.

Dato- og klokkeslætsfunktioner i DAX

Hvis du nogensinde har arbejdet med dato- og klokkeslætsfunktioner i Excel-formler, vil du sandsynligvis være fortrolig med dato- og klokkeslætsfunktionerne. Selvom disse funktioner ligner deres modstykker i Excel, er der nogle vigtige forskelle:

• DaX-funktionerne Dato og klokkeslæt bruger en datetime-datatype.

• De kan tage værdier fra en kolonne som argument.

• De kan bruges til at returnere og/eller manipulere datoværdier.

Disse funktioner bruges ofte, når du opretter brugerdefinerede datokolonner i en datotabel, så de er vigtige at forstå. Vi bruger en række af disse funktioner til at oprette kolonner for År, Kvartal, Regnskabsmåned osv.

DAX indeholder følgende dato- og klokkeslætsfunktioner:

• DATO

• DATOVÆRDI

• NÆSTE DAG

• EDATE

• SLUT.

• TIME

• MINUT

• MÅNED

• NU

• ANDEN

• TIDSPUNKT

• TIDSVÆRDI

• I DAG

• HVERDAG

• WEEKNUM

• ÅR

• ÅR.BRÆD

Der er mange andre DAX-funktioner, du også kan bruge i dine formler. Mange af de formler, der er beskrevet her, bruger f.eks . matematiske og trigonometriske funktioner som REST og AFKORT, Logiske funktioner som HVIS og Tekstfunktioner som FORMAT Hvis du vil have mere at vide om andre DAX-funktioner, skal du se afsnittet Yderligere ressourcer senere i denne artikel.

Formeleksempler for et kalenderår

I følgende eksempler beskrives formler, der bruges til at oprette flere kolonner i en datotabel med navnet Kalender. En kolonne med navnet Dato findes allerede og indeholder et sammenhængende område med datoer fra 1-1-2010 til og med 31-12-2016.

Månedsnavn

=FORMAT([dato],"mmmm")

I denne formel bruger vi funktionen FORMAT til at konvertere en numerisk værdi fra kolonnen Dato til tekst for at få månedsnavnet. Vi angiver kolonnen Dato som det første argument og derefter formatet. vi ønsker, at vores månedsnavn skal vise alle tegn, så vi bruger "mmmm". Vores resultat ser sådan ud:

Hvis vi vil returnere månedsnavnet forkortet til tre bogstaver, bruger vi "mmm" i argumentet format.

Ugedag

=FORMAT([dato],"ddd")

I denne formel bruger vi funktionen FORMAT til at hente dagsnavnet. Da vi blot ønsker et forkortet dagsnavn, angiver vi "ddd" i argumentet format.

Eksempel på pivottabel

Når du har felter til datoer som f.eks. År, Kvartal, Måned osv., kan du bruge dem i en pivottabel eller rapport. Følgende billede viser f.eks. feltet Salgsbeløb fra faktatabellen Salg i VÆRDIER og År og Kvartal fra dimensionstabellen Kalender i RÆKKER. Salgsbeløb aggregeres for konteksten for år og kvartal.

Formeleksempler for et regnskabsår

Helligdage eller særlige datoer

Du kan medtage en datokolonne, der angiver, at bestemte datoer er helligdage eller en anden særlig dato. Det kan f.eks. være, at du vil summere salgstotalerne for nytårsdage ved at føje et helligdagsfelt til en pivottabel, som et udsnit eller filter. I andre tilfælde kan det være en god ide at udelade disse datoer fra andre datokolonner eller i en måling.

Det er ganske enkelt at inkludere helligdage eller særlige dage. Du kan oprette en tabel i Excel med de datoer, du vil medtage. Du kan derefter kopiere eller bruge Føj til datamodel for at føje den til datamodellen som en sammenkædet tabel. I de fleste tilfælde er det ikke nødvendigt at oprette en relation mellem tabellen og kalendertabellen. Alle formler, der refererer til den, kan bruge funktionen SLÅ.OP.VÆRDI til at returnere værdier.

Nedenfor er et eksempel på en tabel, der er oprettet i Excel, og som indeholder helligdage, der skal føjes til datotabellen:

I datotabellen opretter vi en kolonne med navnet Ferie og bruger en formel som denne:

=SLÅ.OPVÆRDI(Helligdage[Ferie],Helligdage[dato],Kalender[dato])

Lad os se nærmere på denne formel.

Vi bruger funktionen SLÅ.OPVÆRDI til at hente værdier fra kolonnen Ferie i tabellen Helligdage. I det første argument angiver vi den kolonne, hvor vores resultatværdi skal være. Vi angiver kolonnen Ferie i tabellen Helligdage , fordi det er den værdi, vi vil returnere.

=SLÅ.OPVÆRDI(Helligdage[Ferie],Helligdage[dato],Kalender[dato])

Vi angiver derefter det andet argument, den søgekolonne, der indeholder de datoer, vi vil søge efter. Vi angiver kolonnen Dato i tabellen Helligdage på følgende måde:

=SLÅ.OPVÆRDI(Helligdage[Ferie],Helligdage[dato],Kalender[dato])

Til sidst angiver vi kolonnen i tabellen Kalender , der indeholder de datoer, vi vil søge efter, i tabellen Ferie . Dette er selvfølgelig kolonnen Dato i tabellen Kalender .

=SLÅ.OPVÆRDI(Helligdage[Ferie],Helligdage[dato],Kalender[dato])

Kolonnen Ferie returnerer helligdagsnavnet for hver række, der har en datoværdi, der svarer til en dato i tabellen Helligdage.

Brugerdefineret kalender – 13 fire ugers perioder

Nogle organisationer, f.eks. detail- eller fødevareservice, rapporterer ofte om forskellige perioder, f.eks. 13 perioder med fire uger. Med en 13 kalender med fire uger er hver periode 28 dage. derfor indeholder hver periode fire mandage, fire tirsdage, fire onsdage osv. Hver periode indeholder det samme antal dage, og helligdage falder typisk inden for den samme periode hvert år. Du kan vælge at starte en periode på en hvilken som helst dag i ugen. På samme måde som med datoer i en kalender eller et regnskabsår kan du bruge DAX til at oprette flere kolonner med brugerdefinerede datoer.

I eksemplerne nedenfor starter den første hele periode den første søndag i regnskabsåret. I dette tilfælde begynder regnskabsåret den 7/1.

Period

Perioden i denne brugerdefinerede kalender indeholder 28 dage, og den starter altid på en søndag. Denne kolonne returnerer nummeret på perioden, der begynder med den første søndag i det første regnskabsår.

=HELT. (([Uge]+3)/4)

Lad os se nærmere på denne formel.

Først opretter vi en formel, der returnerer en værdi fra kolonnen Uge som et heltal, sådan her:

=HELT. ([Uge])

Føj derefter 3 til denne værdi, sådan her:

=HELT. ([Uge]+3)

Divider derefter resultatet med 4, sådan her:

=HELT. (([Uge]+3)/4)

Resultatet ser sådan ud:

Periode regnskabsår

Denne værdi returnerer regnskabsåret for en periode.

=HELT. (([Punktum]+12)/13)+2008

Lad os se nærmere på denne formel.

Først opretter vi en formel, der returnerer en værdi fra Punktum og lægger 12 sammen:

= ([Punktum]+12)

Vi dividerer resultatet med 13, fordi der er 13 perioder på 28 dage i regnskabsåret:

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

Vi tilføjer 2010, fordi det er det første år i tabellen:

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

Til sidst bruger vi funktionen HELTAL til at fjerne en hvilken som helst brøkdel af resultatet og returnere et helt tal, når det divideres med 13, sådan her:

=HELT. (([Punktum]+12)/13)+2010

Resultatet ser sådan ud:

Periode i Regnskabsår

Denne værdi returnerer periodenummeret 1-13 startende med den første fulde periode (begyndende søndag) i hvert regnskabsår.

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

Denne formel er lidt mere kompleks, så vi vil først beskrive den på et sprog, vi bedre forstår. Denne formel angiver, at værdien fra [Periode] skal divideres med 13 for at få et periodetal (1-13) i året. Hvis tallet er 0, returneres 13.

Først opretter vi en formel, der returnerer resten af værdien fra Periode med 13. Vi kan bruge FUNKTIONERNE REST (Matematiske og Trigonometriske funktioner) sådan her:

=REST([Punktum],13)

Dette giver os for det meste det ønskede resultat, undtagen hvor værdien for Periode er 0, fordi disse datoer ikke falder inden for det første regnskabsår, f.eks. i de første fem dage af vores eksempeltabel med kalenderdatoer. Vi kan tage os af dette med en HVIS-funktion. Hvis vores resultat er 0, returnerer vi 13, som dette:

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

Resultatet ser sådan ud:

Flere relationer

I nogle tilfælde kan det være nødvendigt at oprette flere relationer eller oprette flere datotabeller. Hvis der f.eks. er flere datofelter i faktatabellen Salg, f.eks. DateKey, ShipDate og ReturnDate, kan de alle have relationer til feltet Dato i datotabellen Kalender, men kun én af disse kan være en aktiv relation. Da DateKey i dette tilfælde repræsenterer datoen for transaktionen og derfor den vigtigste dato, vil dette bedst fungere som den aktive relation. De andre har inaktive relationer.

Følgende pivottabel beregner det samlede salg efter regnskabsår og regnskabskvartal. En måling med navnet Samlet salg med formlen Samlet salg:=SUM([Salgsbeløb]) placeres i FELTERNE VÆRDIER, og Felterne Regnskabsår og Regnskabskvartal fra datotabellen Kalender placeres i RÆKKER.

Denne pivottabel med ligetil fungerer korrekt, fordi vi vil summere vores samlede salg efter transaktionsdatoeni DateKey. Vores måling Samlet salg bruger datoerne i DateKey og summeres efter regnskabsår og regnskabskvartal, fordi der er en relation mellem DateKey i tabellen Salg og kolonnen Dato i datotabellen Kalender.

Inaktive relationer

Men hvad nu, hvis vi vil summere vores samlede salg ikke efter transaktionsdato, men efter forsendelsesdato? Vi har brug for en relation mellem kolonnen Forsendelsesdato i tabellen Salg og kolonnen Dato i tabellen Kalender. Hvis vi ikke opretter denne relation, er vores sammenlægninger altid baseret på transaktionsdatoen. Vi kan dog have flere relationer, selvom kun én kan være aktiv, og fordi transaktionsdatoen er den vigtigste, får den aktive relation til tabellen Kalender.

I dette tilfælde har Forsendelsesdato en inaktiv relation, så alle måleformler, der oprettes for at sammenlægge data baseret på forsendelsesdatoer, skal angive den inaktive relation ved hjælp af funktionen USERELATIONSHIP .

Da der f.eks. er en inaktiv relation mellem kolonnen Forsendelsesdato i tabellen Salg og kolonnen Dato i tabellen Kalender, kan vi oprette en måling, der summerer samlet salg efter forsendelsesdato. Vi bruger en formel som denne til at angive den relation, der skal bruges:

Samlet salg efter forsendelsesdato:=BEREGN(SUM(Salg[Salgsbeløb]), USERELATIONSHIP(Salg[Forsendelsesdato], Kalender[Dato]))

Denne formel angiver blot: Beregn en sum for SalesAmount, men filtrer ved hjælp af relationen mellem kolonnen Forsendelsesdato i tabellen Salg og kolonnen Dato i tabellen Kalender.

Hvis vi opretter en pivottabel og placerer Samlet salg efter forsendelsesdato i VÆRDIER og Regnskabsår og Regnskabsår på RÆKKER, ser vi den samme hovedtotal, men alle andre sumbeløb for regnskabsår og regnskabsår er anderledes, fordi de er baseret på afsendelsesdatoen og ikke transaktionsdatoen.

Ved hjælp af inaktive relationer kan du kun bruge én datotabel, men det kræver, at alle målinger (f.eks. Samlet salg efter leveringsdato) refererer til den inaktive relation i formlen. Der er et andet alternativ, nemlig at bruge flere datotabeller.

Flere datotabeller

En anden måde at arbejde med flere datokolonner i faktatabellen på er at oprette flere datotabeller og oprette separate aktive relationer mellem dem. Lad os se på eksemplet med tabellen Salg igen. Vi har tre kolonner med datoer, som vi måske gerne vil aggregere data om:

• En DateKey med salgsdatoen for hver transaktion.

• En Leveringsdato – med den dato og det klokkeslæt, hvor de solgte varer blev sendt til kunden.

• En ReturnDate – med datoen og klokkeslættet for modtagelse af en eller flere returnerede varer.

Husk, at feltet DateKey med posteringsdatoen er vigtigst. Vi vil udføre de fleste af vores sammenlægninger baseret på disse datoer, så vi vil helt sikkert gerne have en relation mellem den og kolonnen Dato i tabellen Kalender. Hvis vi ikke vil oprette inaktive relationer mellem Forsendelsesdato og Returdato og feltet Dato i tabellen Kalender, hvilket kræver særlige måleformler, kan vi oprette flere datotabeller for afsendelsesdato og returdato. Vi kan derefter oprette aktive relationer mellem dem.

I dette eksempel har vi oprettet en anden datotabel med navnet ShipCalendar. Dette betyder selvfølgelig også, at du skal oprette flere datokolonner, og da disse datokolonner er i en anden datotabel, vil vi navngive dem på en måde, der adskiller dem fra de samme kolonner i tabellen Kalender. Vi har f.eks. oprettet kolonner med navnet ShipYear, ShipMonth, ShipQuarter osv.

Hvis vi opretter vores pivottabel og placerer vores måling Samlet salg i VÆRDIER og ShipFiscalYear og ShipFiscalQuarter i RÆKKER, ser vi de samme resultater, som vi så, da vi oprettede en inaktiv relation og et særligt beregnet felt samlet salg efter forsendelsesdato.

Hver af disse tilgange kræver nøje overvejelse. Når du bruger flere relationer med en enkelt datotabel, kan det være nødt til at oprette særlige målinger, der transiter inaktive relationer ved hjælp af funktionen USERELATIONSHIP. På den anden side kan det være forvirrende at oprette flere datotabeller på en feltliste, og fordi du har flere tabeller i datamodellen, kræver det mere hukommelse. Eksperimentér med, hvad der fungerer bedst for dig.

Sådan angives egenskaben Datotabel

1. Vælg tabellen Kalender i PowerPivot-vinduet.

2. Klik på Markér som datotabel under fanen Design.

3. I dialogboksen Markér som datotabel skal du vælge en kolonne med entydige værdier og datatypen Dato.

Konvertere tekstdatatypedatoer til en datodatatype

I nogle tilfælde kan en faktatabel med transaktionsdata indeholde datoer med tekstdatatype. Det betyder, at en dato, der vises som 12-12-201211:47:09, faktisk slet ikke er en dato eller i det mindste ikke den type dato, Som Power Pivot kan forstå. Det er virkelig bare tekst, der læser som en dato. Hvis du vil oprette en relation mellem en datokolonne i faktatabellen og en datokolonne i en datotabel, skal begge kolonner have datatypen Dato .

Når du forsøger at ændre datatypen for en kolonne med datoer, der er tekstdatatype, til en datodatatype, kan Power Pivot automatisk fortolke datoerne og konvertere dem til en datatype med en sand dato. Hvis Power Pivot ikke kan udføre en datatypekonvertering, får du en typeuoverensstemmelsesfejl.

Du kan dog stadig konvertere datoerne til datatypen Sand dato. Du kan oprette en ny beregnet kolonne og bruge en DAX-formel til at fortolke år, måned, dag, klokkeslæt osv. fra tekststrengene og derefter sammenkæde den igen på en måde, som Power Pivot kan læse som en sand dato.

I dette eksempel har vi importeret en faktatabel med navnet Salg i Power Pivot. Den indeholder en kolonne med navnet DateTime. Værdier ser sådan ud:

Hvis vi ser på Datatype i gruppen Formatering i Power Pivots hjem-fane, kan vi se, at det er datatypen Tekst.

Vi kan ikke oprette en relation mellem kolonnen DateTime og kolonnen Dato i vores datotabel, fordi datatyperne ikke stemmer overens. Hvis vi forsøger at ændre datatypen til Dato, får vi en typeuoverensstemmelsesfejl:

I dette tilfælde kunne Power Pivot ikke konvertere datatypen fra tekst til dato. Vi kan stadig bruge denne kolonne, men for at gøre den til en sand datodatatype, skal vi oprette en ny kolonne, der fortolker teksten og genopretter den til en værdi, som Power Pivot kan lave til datatypen Dato.

Husk, at fra afsnittet Arbejde med tid tidligere i denne artikel. medmindre det er nødvendigt, at din analyse er til et præcisionsniveau for en dag, skal du konvertere datoer i faktatabellen til et præcisionsniveau for dagen. Med det i baghovedet ønsker vi, at værdierne i vores nye kolonne skal være på dagsniveau med præcision (undtagen tid). Vi kan både konvertere værdierne i kolonnen DateTime til en datodatatype og fjerne præcisionsniveauet for klokkeslæt med følgende formel:

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

Dette giver os en ny kolonne (i dette tilfælde kaldet Dato). Power Pivot registrerer endda de værdier, der skal være datoer, og angiver automatisk datatypen til Dato.

Hvis vi vil bevare præcisionsniveauet for tid, udvider vi blot formlen til at omfatte timer, minutter og sekunder.

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

TID(MIDT([DateTime],12,2), MIDT([DateTime],15,2), MID([DateTime],18,2))

Nu hvor vi har en datokolonne af datatypen Dato, kan vi oprette en relation mellem den og en datokolonne i en dato.

Yderligere ressourcer

Datoer i Power Pivot

Beregninger i Power Pivot

Hurtig start: Lær de grundlæggende DAX-funktioner på 30 minutter

Reference til dataanalyseudtryk

DAX Ressourcecenter