Forstå og oprette datotabeller i PowerPivot i Excel

Opret en datotabel i Excel

Du kan oprette en datotabel i Excel og derefter kopiere den over i en ny tabel i datamodellen. Det er meget nemt, og det giver dig stor fleksibilitet.

Når du opretter en datotabel i Excel, starter du med en enkelt kolonne med et sammenhængende datointerval. 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 Tilføje nye datokolonner i 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 datoområde. 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 vil have din datotabel til at medtage 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ækst år for år. I hvert af disse tilfælde skal din datotabel have år-, måneds- eller kvartalskolonner, som gør det muligt at aggregere dine data for den pågældende periode.

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

DAX-dato- og klokkeslætsfunktioner

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

• DAX-funktionerne Dato og Klokkeslæt bruger datatypen datetime.

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

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

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

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

• DATO

• DATOVÆRDI

• NEXTDAY

• EATE

• EOMONTH

• HOUR

• MINUTE

• MÅNED

• NOW

• SECOND

• TID

• TIDSVÆRDI

• I DAG

• UGEDAG

• WEEKNUM

• YEAR

• YEARFRAC

Der er mange andre DAX-funktioner, du kan bruge i dine formler. Mange af de formler, der er beskrevet her, bruger f.eks. matematiske og trigonometriske funktioner som MOD og AFKORT, logiske funktioner som HVIS og Tekstfunktioner som FORMAT Du kan finde flere oplysninger om andre DAX-funktioner i afsnittet Yderligere ressourcer senere i denne artikel.

Formelekse eksempler på et kalenderår

Følgende eksempler beskriver formler, der bruges til at oprette flere kolonner i en datotabel med navnet Kalender. Der findes allerede en kolonne med navnet Dato, og den indeholder et sammenhængende datointerval fra 01-01-2010 til 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ånedens navn. Vi angiver kolonnen Dato som det første argument og derefter formatet. vi vil have vores månedsnavn til at vise alle tegn, så vi bruger "mmmm". Vores resultat ser sådan ud:

Hvis vi vil returnere månedens navn forkortet til tre bogstaver, skal vi bruge "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 SalesAmount fra faktatabellen Salg i VÆRDIER og År og Kvartal fra dimensionstabellen Kalender i RÆKKER. Salgbeløb aggregeres for år- og kvartalskontekst.

Formelekse eksempler på et regnskabsår

Helligdage eller særlige datoer

Du kan medtage en datokolonne, der angiver, at visse datoer er helligdage eller en anden særlig dato. Det kan f.eks. være, at du vil lægge salgstotalerne for nytårsdag sammen ved at føje et felt til en pivottabel, som et udsnit eller et 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 ret nemt at inkludere helligdage eller særlige dage. Du kan oprette en tabel i Excel, der indeholder 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 tabellen Kalender. Alle formler, der refererer til den, kan bruge funktionen SLÅ.OPVÆRDI til at returnere værdier.

Nedenfor er et eksempel på en tabel, der er oprettet Excel, der 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 kolonnen, hvor vores resultatværdi skal være. Vi angiver kolonnen Helligdag i tabellen Helligdage, fordi det er den værdi, vi ønsker returneret.

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

Vi angiver derefter det andet argument, søgekolonnen, der indeholder de datoer, vi vil søge efter. Vi angiver kolonnen Dato i tabellen Helligdage, sådan her:

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

Endelig angiver vi den kolonne i tabellen Kalender, der indeholder de datoer, vi vil søge efter, i tabellen Ferie. Dette er naturligvis kolonnen Dato i tabellen Kalender.

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

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

Brugerdefineret kalender – tretten fire-ugers perioder

Nogle organisationer, som f.eks. detail- eller madtjeneste, rapporterer ofte om forskellige perioder, f.eks. tretten fire ugers perioder. Med en tretten fire-ugers kalenderperiode 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 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 en søndag. Denne kolonne returnerer tallet for den periode, der begynder med den første søndag i det første regnskabsår.

=HELTA(([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, som dette:

=HELTA([Uge])

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

=HELTA([Uge]+3)

Divider derefter resultatet med 4, sådan her:

=HELTA(([Uge]+3)/4)

Resultatet ser sådan ud:

Periode Regnskabsår

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

=HELTA(([Periode]+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 tretten 28 dagsperioder i regnskabsåret:

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

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

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

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

=HELTA(([Periode]+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 på søndag) for hvert regnskabsår.

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

Denne formel er lidt mere kompleks, så vi beskriver den først på et sprog, vi bedre forstår. Denne formel siger, at dividere værdien fra [periode] med 13 for at få et periodenummer (1-13) i året. Hvis tallet er 0, returneres 13.

Først opretter vi en formel, der returnerer restværdien fra Periode med 13. Vi kan bruge funktionerne MOD (Matematisk og trigonometrisk) på følgende måde:

=REST([periode],13)

Dette giver os for det meste det ønskede resultat, bortset fra hvor værdien for Periode er 0, fordi disse datoer ikke falder inden for det første regnskabsår, som i de første fem dage i vores eksempel på kalenderdatotabel. Det kan vi gøre med en HVIS-funktion. Hvis vores resultat er 0, returnerer vi 13, som dette:

=HVIS(REST([periode],13),REST([periode],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 tabellen Kalenderdato, men kun én af dem kan være en aktiv relation. I dette tilfælde, fordi DateKey repræsenterer datoen for transaktionen, og derfor den vigtigste dato, ville 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 VÆRDIER, og felterne FiscalYear og FiscalQuarter fra tabellen Med kalenderdato er placeret i RÆKKER.

Denne pivottabel med lige linje 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 tabellen Kalenderdato.

Inaktive relationer

Men hvad nu, hvis vi gerne vil summere vores samlede salg ikke efter transaktionsdato, men efter afsendelsesdato? 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 relation kan være aktiv, og da transaktionsdatoen er den vigtigste, bliver den aktive relation til tabellen Kalender.

I dette tilfælde har ShipDate en inaktiv relation, så enhver målingsformel, der er oprettet for at aggregere 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 det samlede salg efter forsendelsesdato. Vi bruger en formel som denne til at angive relationen, der skal bruges:

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

Denne formel siger 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 nu opretter en pivottabel og sætter målingen Samlet salg efter afsendelsesdato i VÆRDIER og Regnskabsår og Regnskabskvartal på RÆKKER, kan vi se den samme Hovedtotal, men alle andre sumbeløb for regnskabsår og regnskabskvartal er forskellige, fordi de er baseret på afsendelsesdatoen og ikke transaktionsdatoen.

Når du bruger inaktive relationer, kan du kun bruge én datotabel, men det kræver, at alle målinger (f.eks. Samlet salg efter afsendelsesdato) henviser til den inaktive relation i formlen. Der er et andet alternativ, dvs. brug flere datotabeller.

Flere datotabeller

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

• En DateKey med datoen for salget for hver transaktion.

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

• En Returneringsdato – med dato og klokkeslæt, hvor et eller flere returnerede elementer blev modtaget.

Husk, at feltet DateKey med transaktionsdatoen er det vigtigste. Vi vil udføre de fleste sammenlægninger baseret på disse datoer, så der vil selvfølgelig være en relation mellem den og kolonnen Dato i tabellen Kalender. Hvis vi ikke vil oprette inaktive relationer mellem Forsendelsesdato og Returneringsdato og feltet Dato i tabellen Kalender, hvilket kræver særlige måleformler, kan vi oprette flere datotabeller for forsendelsesdato og returdato. Vi kan derefter oprette aktive relationer mellem dem.

I dette eksempel har vi oprettet en anden datotabel med navnet ShipCalendar. Det betyder selvfølgelig også, at du skal oprette flere datokolonner, og da disse datokolonner findes 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 angiver vores måling Samlet salg i VÆRDIER og ShipFiscalYear og ShipFiscalQuarter på RÆKKER, ser vi de samme resultater, som vi så, da vi oprettede en inaktiv relation, og et specielt beregnet felt Samlet salg efter afsendelsesdato.

Hver af disse fremgangsmåder kræver nøje overvejelser. Når du bruger flere relationer med en enkelt datotabel, kan det være nødvendigt 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 i en feltliste, og fordi du har flere tabeller i datamodellen, kræver det mere hukommelse. Eksperimentér med det, der fungerer bedst for dig.

Sådan angives egenskaben Datotabel

1. Vælg PowerPivot kalender i vinduet Kalender.

2. På fanen Design skal du klikke på Markér som datotabel.

3. Vælg en kolonne med entydige værdier og datatypen Dato i dialogboksen Markér som datotabel.

Konvertere tekstdatatypedatoer til en datodatatype

I nogle tilfælde kan en faktatabel med transaktionsdata indeholde datoer med tekstdatatype. Det vil sige, at en dato, der vises som 12-12-04T11:47:09, faktisk slet ikke er en dato, eller i hvert fald ikke den type dato, som Power Pivot kan forstå. Det er faktisk 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 tekstdatatyper, til en datodatatype, kan Power Pivot fortolke datoerne og konvertere den automatisk til en sand datodatatype. Hvis Power Pivot ikke kan udføre konvertering af datatype, får du vist en fejl om uoverensstemmelse mellem datatyper.

Du kan dog stadig konvertere datoerne til en ægte datodatatype. 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 korrekt dato.

I dette eksempel har vi importeret en faktatabel med navnet Salg i Power Pivot. Den indeholder en kolonne med navnet DateTime. Værdier vises på følgende måde:

Hvis vi ser på datatypen i gruppen Formatering under fanen Hjem i Power Pivot, 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årvi en fejl om uoverensstemmelse mellem typer:

I dette tilfælde kunne Power Pivot ikke konvertere datatypen fra tekst til dato. Vi kan stadig bruge denne kolonne, men for at få den ind i en ægte datodatatype, skal vi oprette en ny kolonne, der fortolker teksten og opretter den igen til en værdi i Power Pivot, så den kan oprette en Dato-datatype.

Husk, at du fra sektionen Arbejder med tid tidligere i denne artikel; Medmindre det er nødvendigt, at analysen skal have præcisionsniveau for tid på dagen, skal du konvertere datoer i faktatabellen til et præcisionsniveau for dagen. Med det i baghovedet ønsker vi, at værdierne i den nye kolonne skal være på præcisionsniveauet for dagen (ekskl. tid). Vi kan både konvertere værdierne i kolonnen DateTime til en datodatatype og fjerne præcisionsniveauet for tid 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 værdierne som datoer og angiver automatisk datatypen til Dato.

Hvis vi vil bevare præcisionsniveauet for tid, skal vi blot udvide formlen for at medtage timer, minutter og sekunder.

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

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

Nu hvor vi har en Dato-kolonne 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