Förstå och skapa datumtabeller i PowerPivot i Excel

Skapa en datumtabell i Excel

Du kan skapa en datumtabell i Excel och sedan kopiera den till en ny tabell i datamodellen. Det här är riktigt enkelt och ger dig stor flexibilitet.

När du skapar en datumtabell i Excel börjar du med en enda kolumn med ett angränsande datumintervall. Du kan sedan skapa ytterligare kolumner, till exempel År, Kvartal, Månad, Räkenskapsår, Period och så vidare i kalkylbladet Excel med hjälp av Excel-formler, eller så kan du skapa dem som beräknade kolumner när du har kopierat tabellen till datamodellen. Att skapa ytterligare datumkolumner i Power Pivot beskrivs i avsnittet Lägga till nya datumkolumner i datumtabellen längre fram i den här artikeln.

Lägga till nya datumkolumner i datumtabellen

En datumtabell med en enda datumkolumn med en rad för varje dag för varje år är viktigt för att definiera alla datum i ett datumintervall. Du måste också skapa en relation mellan faktatabellen och datumtabellen. Men den enstaka datumkolumnen med en rad för varje dag är inte användbar när du analyserar efter datum i en pivottabell eller Power View-rapport. Du vill att datumtabellen ska innehålla kolumner som hjälper dig att sammanställa data för ett intervall eller en grupp datum. Du kanske till exempel vill summera försäljningsbelopp per månad eller kvartal, eller så kan du skapa ett mått som beräknar årstillväxt över år. I vart och ett av dessa fall behöver datumtabellen kolumner för år, månad eller kvartal så att du kan aggregera data för den perioden.

Om du har importerat datumtabellen från en relationsdatakälla kanske den redan innehåller de olika typerna av datumkolumner som du vill använda. I vissa fall kanske du vill ändra några av dessa kolumner eller skapa ytterligare datumkolumner. Det gäller särskilt om du skapar en egen datumtabell i Excel kopierar den till datamodellen. Som tur är är det ganska enkelt att skapa nya datumkolumner i Power Pivot med datum- och tidsfunktioner i DAX.

Datum- och tidsfunktioner i DAX

Om du någon gång har arbetat med datum- och tidsfunktioner i Excel-formler är du antagligen bekant med datum- och tidsfunktionerna. Även om dessa funktioner liknar deras motsvarigheter i Excel finns det några viktiga skillnader:

• Datum- och tidsfunktioner i DAX använder datatypen datetime.

• De kan använda värden från en kolumn som argument.

• De kan användas för att returnera och/eller ändra datumvärden.

De här funktionerna används ofta när du skapar anpassade datumkolumner i en datumtabell, så att de är viktiga att förstå. Vi kommer att använda flera av de här funktionerna för att skapa kolumner för År, Kvartal, Räkenskapsmonth och så vidare.

DAX innehåller följande datum- och tidsfunktioner:

• DATE

• DATUMVÄRDE

• NEXTDAY

• EDATUM

• EOMONTH

• HOUR

• MINUTE

• MONTH

• NU

• SECOND

• TIME

• TIDVÄRDE

• I DAG

• VECKODAG

• WEEKNUM

• YEAR

• ÅRDEFRAC

Det finns många andra DAX-funktioner som du kan använda i dina formler också. Många av de formler som beskrivs här använder till exempel matematiska och trigonometriska funktioner som MOD och AVKORTA,Logiska funktioner som OMoch Textfunktioner som FORMAT Mer information om andra DAX-funktioner finns i avsnittet Ytterligare resurser längre fram i den här artikeln.

Formelexempel för ett kalenderår

I följande exempel beskrivs formler som används för att skapa ytterligare kolumner i en datumtabell med namnet Kalender. En kolumn med namnet Datum finns redan och innehåller ett angränsande datumintervall från 2010-01-01 till och med 2016-12-31.

Månadsnamn

=FORMAT([datum];"mmmm")

I den här formeln använder vi funktionen FORMAT för att hämta månadsnamnet till att konvertera ett numeriskt värde från kolumnen Datum till text. Vi anger kolumnen Datum som det första argumentet och sedan formatet. vi vill att månadsnamnet ska visa alla tecken, så vi använder "mmmm". Resultatet ser ut så här:

Om vi vill returnera månadsnamnet förkortat till tre bokstäver använder vi "mmm" i argumentet format.

Dag i vecka

=FORMAT([datum];"ddd")

I den här formeln använder vi funktionen FORMAT för att hämta dagens namn. Eftersom vi bara vill ha ett förkortat dagsnamn anger vi "ddd" i argumentet format.

Exempel på pivottabell

När du har fält för datum som År, Kvartal, Månad osv. kan du använda dem i en pivottabell eller rapport. I följande bild visas till exempel fältet Försäljningsbelopp från faktatabellen Försäljning i VÄRDEN och År och Kvartal från dimensionstabellen Kalender i RADER. Försäljningsbelopp aggregeras för år och kvartalssammanhang.

Formelexempel för ett räkenskapsår

Helgdagar eller specialdatum

Du kanske vill ta med en datumkolumn som anger att vissa datum är helgdagar eller något annat särskilt datum. Du kanske till exempel vill summera totalförsäljningssummorna för Nya år-dagen genom att lägga till fältet Helgdag i en pivottabell, som ett utsnitt eller filter. I andra fall kanske du vill utesluta dessa datum från andra datumkolumner eller i ett mått.

Att ta med helgdagar eller särskilda dagar är ganska enkelt. Du kan skapa en tabell i Excel som innehåller de datum som du vill ta med. Du kan sedan kopiera eller använda Lägg till i datamodell för att lägga till den i datamodellen som en länkad tabell. I de flesta fall behöver du inte skapa en relation mellan tabellen och tabellen Kalender. Alla formler som refererar till den kan använda funktionen LETAUPPVÄRDE för att returnera värden.

Här är ett exempel på en tabell som skapats i Excel som innehåller helgdagar som ska läggas till i datumtabellen:

I datumtabellen skapar vi en kolumn med namnet Helgdag och använder en formel som den här:

=LETAUPPVÄRDE(Helgdagar[Semester],Helgdagar[datum],Kalender[datum])

Låt oss titta närmare på den här formeln.

Vi använder funktionen LETAUPPVÄRDE för att hämta värden från kolumnen Helgdag i tabellen Helgdagar. I det första argumentet anger vi den kolumn där resultatet ska vara. Vi anger kolumnen Helgdagar i tabellen Helgdagar eftersom det är det värde vi vill returnera.

=LETAUPPVÄRDE(Helgdagar[Helgdag],Helgdagar[datum],Kalender[datum])

Sedan anger vi det andra argumentet, sökkolumnen som innehåller de datum vi vill söka efter. Vi anger kolumnen Datum i tabellen Helgdagar, så här:

=LETAUPPVÄRDE(Helgdagar[Semester],Helgdagar[datum],Kalender[datum])

Slutligen anger vi den kolumn i tabellen Kalender som innehåller de datum vi vill söka efter i tabellen Semester. Det här är naturligtvis kolumnen Datum i tabellen Kalender.

=LETAUPPVÄRDE(Helgdagar[Semester],Helgdagar[datum],Kalender[datum])

Kolumnen Helgdag returnerar helgdagens namn för varje rad som har ett datumvärde som matchar ett datum i tabellen Helgdagar.

Anpassad kalender – tretton fyraveckorsperioder

Vissa organisationer, som detaljhandel och livsmedelstjänster, rapporterar ofta olika perioder, t.ex. tretton fyraveckorsperioder. Med en trettond fyraveckorsperiod är varje period 28 dagar. Därför innehåller varje period fyra måndagar, fyra tisdagar, fyra onsdagar och så vidare. Varje period innehåller samma antal dagar och vanligtvis faller helgdagarna inom samma period varje år. Du kan välja att börja perioden på valfri veckodag. Precis som med datum i en kalender eller ett räkenskapsår kan du använda DAX för att skapa ytterligare kolumner med anpassade datum.

I exemplen nedan börjar den första fullständiga perioden på den första söndagen i räkenskapsåret. I det här fallet börjar räkenskapsåret 2011-07-01.

Punkt

Perioden i den här anpassade kalendern innehåller 28 dagar och börjar alltid på en söndag. Den här kolumnen returnerar numret på perioden som börjar med den första söndagen i det första räkenskapsåret.

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

Låt oss titta närmare på den här formeln.

Först skapar vi en formel som returnerar ett värde från kolumnen Vecka som ett heltal, så här:

=INT([Vecka])

Lägg sedan till 3 till det värdet, så här:

=INT([Vecka]+3)

Dividera sedan resultatet med 4, så här:

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

Så här ser resultatet ut:

Period – räkenskapsår

Det här värdet returnerar räkenskapsåret för en period.

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

Låt oss titta närmare på den här formeln.

Först skapar vi en formel som returnerar ett värde från punkt och adderar 12:

= ([Punkt]+12)

Resultatet delas upp med 13 eftersom det finns tretton 28 dagar under räkenskapsåret:

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

Vi lägger till 2010 eftersom det är det första året i tabellen:

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

Slutligen använder vi funktionen HELTAL för att ta bort en del av resultatet och returnera ett heltal, om det divideras med 13, så här:

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

Så här ser resultatet ut:

Period i räkenskapsår

Det här värdet returnerar periodnumret 1–13, med början från den första fullständiga perioden (som börjar på söndag) för varje räkenskapsår.

=OM(MOD([Punkt];13);MOD([Punkt];13);13)

Den här formeln är lite mer komplex, så vi beskriver den först på ett språk vi bättre förstår. Den här formeln säger att dividera värdet från [Period] med 13 för att få ett periodnummer (1-13) på året. Om talet är 0 returneras 13.

Först skapar vi en formel som returnerar resten av värdet från period med 13. Vi kan använda MOD (matematiska och trigonometriska funktioner) så här:

=MOD([Punkt],13)

Det ger i de flesta fall det resultat vi vill ha, förutom där värdet för Period är 0 eftersom datumen inte faller inom det första räkenskapsåret, som i de första fem dagarna i vår exempeltabell för kalenderdatum. Vi kan sköta det här med en OM-funktion. Om resultatet är 0 returnerar vi 13, så här:

=OM(MOD([Punkt],13),MOD([Punkt],13),13)

Så här ser resultatet ut:

Flera relationer

I vissa fall kan det vara nödvändigt att skapa flera relationer eller skapa flera datumtabeller. Om det till exempel finns flera datumfält i faktatabellen Försäljning, till exempel DatumNyckel, Leveransdatum och Returdatum, kan alla ha relationer till fältet Datum i datumtabellen Kalender, men bara ett av dem kan vara en aktiv relation. Eftersom DateKey i det här fallet representerar datumet för transaktionen, och därför det viktigaste datumet, bör det här fungera bäst som den aktiva relationen. De andra har inaktiva relationer.

I följande pivottabell beräknas den totala försäljningen per räkenskapsår och räkenskapsk kvartal. Ett mått med namnet Total försäljning, med formeln Total försäljning:=SUMMA([Försäljningsbelopp])placeras i VÄRDEN och fälten Räkenskapsår och Räkenskapskvartalt från tabellen Kalenderdatum placeras i RADER.

Den här pivottabellen fungerar korrekt eftersom vi vill summera den totala försäljningen senasttransaktionsdatumet i DateKey. Totalförsäljningsmåttet använder datumen i Datumnyckel och summeras med räkenskapsår och räkenskapsk kvartal eftersom det finns ett samband mellan DatumNyckel i tabellen Försäljning och kolumnen Datum i datumtabellen Kalender.

Inaktiva relationer

Men tänk om vi vill summera totalförsäljningen inte efter transaktionsdatum utan efter leveransdatum? Vi behöver en relation mellan kolumnen Leveransdatum i tabellen Försäljning och kolumnen Datum i tabellen Kalender. Om vi inte skapar relationen är våra aggregeringar alltid baserade på transaktionsdatumet. Men vi kan ha flera relationer, även om bara en kan vara aktiv, och eftersom transaktionsdatumet är det viktigaste får den aktiva relationen med tabellen Kalender.

I det här fallet har Leveransdatum en inaktiv relation, så alla måttformel som skapas för att aggregera data baserade på leveransdatum måste ange den inaktiva relationen med hjälp av funktionen USERELATIONSHIP.

Eftersom det till exempel finns en inaktiv relation mellan kolumnen Leveransdatum i tabellen Försäljning och kolumnen Datum i tabellen Kalender kan vi skapa ett mått som summerar den totala försäljningen per leveransdatum. Vi använder en sådan formel för att ange vilken relation som ska användas:

Total försäljning per leveransdatum:=CALCULATE(SUMMA(Försäljning[Försäljningsbelopp]), USERELATIONSHIP(Försäljning[Leveransdatum], Kalender[Datum]))

Den här formeln visar helt enkelt: Beräkna en summa för Försäljningsbelopp, men filtrera genom att använda relationen mellan kolumnen Leveransdatum i tabellen Försäljning och kolumnen Datum i tabellen Kalender.

Om vi skapar en pivottabell och lägger måttet Total försäljning per leveransdatum i VÄRDEN och Räkenskapsår och Räkenskapsk kvartal i RADER ser vi samma totalsumma men alla andra summabelopp för räkenskapsår och räkenskapsk kvartal skiljer sig åt eftersom de är baserade på leveransdatumet och inte transaktionsdatumet.

Om du använder inaktiva relationer kan du bara använda en datumtabell, men det krävs att alla mått (som Total Sales by Ship Date) refererar till den inaktiva relationen i formeln. Det finns ett annat alternativ, det vill säga använda flera datumtabeller.

Flera datumtabeller

Ett annat sätt att arbeta med flera datumkolumner i faktatabellen är att skapa flera datumtabeller och skapa separata aktiva relationer mellan dem. Nu ska vi titta på tabellexempel för Försäljning igen. Vi har tre kolumner med datum som vi kanske vill aggregera data för:

• En Datumnyckel med försäljningsdatumet för varje transaktion.

• Ett Leveransdatum – med datum och tid då de sålda artiklarna levererades till kunden.

• Ett Returdatum – med datum och tid då ett eller flera objekt togs emot.

Kom ihåg att fältet Datumnyckel med transaktionsdatumet är viktigast. Vi kommer att göra de flesta av våra aggregeringar baserat på dessa datum, så vi kommer definitivt att vilja ha en relation mellan den och kolumnen Datum i tabellen Kalender. Om vi inte vill skapa inaktiva relationer mellan Leveransdatum och Returdatum och fältet Datum i tabellen Kalender, vilket kräver speciella måttformlar, kan vi skapa ytterligare datumtabeller för leveransdatum och returdatum. Vi kan sedan skapa aktiva relationer mellan dem.

I det här exemplet har vi skapat en annan datumtabell med namnet ShipCalendar. Det innebär förstås också att skapa ytterligare datumkolumner, och eftersom datumkolumnerna finns i en annan datumtabell vill vi namnge dem på ett sätt som skiljer dem från samma kolumner i tabellen Kalender. Vi har till exempel skapat kolumnerna ShipYear, ShipMonth, ShipQuarter och så vidare.

Om vi skapar pivottabellen och visar Total Sales-måttet i VÄRDEN och ShipFiscalYear och ShipFiscalQuarter på ROWS, ser vi samma resultat som när vi skapade en inaktiv relation och ett särskilt beräknat fält för Total försäljning per leveransdatum.

Var och en av dessa metoder kräver noggranna överväganden. När du använder flera relationer med en enda datumtabell kan du behöva skapa speciella mått som transiteras inaktiva relationer med hjälp av funktionen USERELATIONSHIP. Att skapa flera datumtabeller kan å andra sidan vara förvirrande i en fältlista, och eftersom du har fler tabeller i datamodellen krävs mer minne. Experimentera med det som passar dig bäst.

Så här gör du: Ange egenskapen Datumtabell

1. I PowerPivot väljer du tabellen Kalender.

2. Klicka på Markera som datumtabell på flikenDesign.

3. I dialogrutan Markera som datumtabell väljer du en kolumn med unika värden och datatypen Datum.

Konvertera datum av textdatatyp till en datumdatatyp

I vissa fall kan en faktatabell med transaktionsdata innehålla datum av datatypen text. Det vill säga ett datum som visas som 2012-12-04T11:47:09 är faktiskt inte ett datum alls, eller åtminstone inte den typ av datum som Power Pivot kan förstå. Det är i egentligen bara text som läser ett datum. För att skapa en relation mellan en datumkolumn i faktatabellen och en datumkolumn i en datumtabell måste båda kolumnerna vara av datatypen Datum.

När du försöker ändra datatypen för en kolumn med datum som är textdatatyp till en datumdatatyp, kan Power Pivot tolka datumen och konvertera dem automatiskt till en sant datumdatatyp. Om Power Pivot inte kan konvertera datatyper visas ett typfel.

Du kan dock fortfarande konvertera datum till datatypen sant datum. Du kan skapa en ny beräknad kolumn och använda en DAX-formel för att tolka år, månad, dag, tid osv. från textsträngarna och sedan sammanfoga tillbaka det på ett sätt som Power Pivot kan läsa som ett sant datum.

I det här exemplet har vi importerat en faktatabell med namnet Försäljning till Power Pivot. Den innehåller en kolumn med namnet DateTime. Värden visas så här:

Om vi tittar på datatypen i gruppen Formatering på fliken Start i Power Pivot ser vi att det är datatypen Text.

Det går inte att skapa en relation mellan DateTime-kolumnen och datumkolumnen i datumtabellen eftersom datatyperna inte stämmer överens. Om vi försöker ändra datatypen till Datumvisas ett typfel:

I det här fallet kunde Power Pivot inte konvertera datatypen från text till datum. Vi kan fortfarande använda den här kolumnen, men för att få till en sann datumdatatyp måste vi skapa en ny kolumn som parsar texten och skapar den på nytt till ett värde som Power Pivot kan göra till datatypen Datum.

Kom ihåg från avsnittet Arbeta med tid tidigare i den här artikeln. Om det inte är nödvändigt att analysen ska gå till en tidsprecisionsnivå, bör du konvertera datum i faktatabellen till en dagsprecision. Med detta i åtanke vill vi att värdena i den nya kolumnen ska vara på dagprecisionsnivån (exklusive tid). Vi kan båda konvertera värdena i kolumnen DateTime till en datumdatatyp och ta bort tidsnivån för precision med följande formel:

=DATUM(VÄNSTER([Datumtid],4), EXVÄRDE([Datumtid],6,2), EXK([Datumtid],9,2))

Då får vi en ny kolumn (i det här fallet Datum). Power Pivot identifierar till och med datumvärdena och ställer automatiskt in datatypen till Datum.

Om vi vill bevara tidsnivån för precision utökar vi bara formeln så att den inkluderar timmar, minuter och sekunder.

=DATUM(VÄNSTER([Datumtid],4), EXVÄRDE([Datumtid],6,2), EXK([Datumtid],9,2)) +

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

Nu när vi har en kolumn av datatypen Datum kan vi skapa en relation mellan den och en datumkolumn i ett datum.

Fler resurser

Datum i PowerPivot

Beräkningar i Power Pivot

Snabbstart: Grunderna i DAX på 30 minuter

Referens för dataanalysuttryck

DAX Resource Center