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. Detta är verkligen ganska lätt att göra och det ger dig mycket 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 som År, Kvartal, Månad, Räkenskapsår, Period osv. i Excel-kalkylbladet med hjälp av Excel-formler, eller så kan du skapa dem som beräknade kolumner när du har kopierat tabellen till datamodellen. Skapa ytterligare datumkolumner i Power Pivot beskrivs i avsnittet Lägga till nya datumkolumner i avsnittet Datumtabell längre fram i den här artikeln.

Lägga till nya datumkolumner i datumtabellen

En datumtabell med en enda datumkolumn som har en rad för varje dag för varje år är viktig för att definiera alla datum i ett datumintervall. Det är också nödvändigt för att 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 aggregera data för ett område eller en grupp med 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 tillväxten från år till år. I vart och ett av dessa fall behöver datumtabellen kolumner för år, månad eller kvartal som gör 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 typer av datumkolumner som du vill använda. I vissa fall kanske du vill ändra vissa av dessa kolumner eller skapa ytterligare datumkolumner. Detta gäller särskilt om du skapar en egen datumtabell i Excel och kopierar den till datamodellen. Som tur är är det 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 känner du förmodligen till funktionerna Datum och tid. Även om dessa funktioner liknar sina motsvarigheter i Excel finns det några viktiga skillnader:

• Datum- och tidsfunktionerna DAX använder datatypen datetime.

• De kan ta värden från en kolumn som ett 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å de är viktiga att förstå. Vi kommer att använda ett antal av de här funktionerna för att skapa kolumner för År, Kvartal, Räkenskapsmånad och så vidare.

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

• DATUM

• DATUMVÄRDE

• NÄSTA DAG

• EDATUM

• SLUTMÅNAD

• TIMME

• MINUT

• MÅNAD

• NU

• ANDRA

• TID

• TIDVÄRDE

• I DAG

• VARDAG

• WEEKNUM

• ÅR

• ÅRDEL

Det finns många andra DAX-funktioner som du kan använda i formlerna också. Många av formlerna som beskrivs här använder till exempel matematiska och trigonometriska funktioner som MOD och TRUNC, logiska funktioner som OM och textfunktioner som FORMAT Mer information om andra DAX-funktioner finns i avsnittet Ytterligare resurser längre fram i den här artikeln.

Exempel på formler 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 sammanhängande datumintervall mellan 2010-01-01 till 2016-12-31.

Månadsnamn

=FORMAT([datum];"mmmm")

För att få månadsnamnet i den här formeln använder vi funktionen FORMAT för att konvertera ett numeriskt värde från kolumnen Datum till text. Vi anger datumkolumnen som det första argumentet och sedan formatet. vi vill att vårt månadsnamn 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 dagnamnet. Eftersom vi bara vill ha ett förkortat dagnamn 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 en rapport. Följande bild visar 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 års- och kvartalskontext.

Exempel på formler för ett räkenskapsår

Helgdagar eller särskilda datum

Du kanske vill ta med en datumkolumn som anger att vissa datum är helgdagar eller något annat specialdatum. Du kanske till exempel vill summera försäljningssummor för nyårsdagen genom att lägga till fältet Semester i en pivottabell, som ett utsnitt eller ett filter. I andra fall kanske du vill utesluta dessa datum från andra datumkolumner eller i ett mått.

Det är ganska enkelt att inkludera helgdagar eller speciella dagar. 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 är det inte nödvändigt att 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.

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

I datumtabellen skapar vi en kolumn med namnet Högtid 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 Semester i tabellen Helgdagar. I det första argumentet anger vi den kolumn där vårt resultatvärde ska vara. Vi anger kolumnen Högtid i tabellen Helgdagar eftersom det är det värde vi vill returnera.

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

Vi anger sedan 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 Högtid returnerar högtidsnamnet för varje rad som har ett datumvärde som matchar ett datum i tabellen Helgdagar.

Anpassad kalender – tretton fyra veckors perioder

Vissa organisationer, till exempel återförsäljare eller mat, rapporterar ofta om olika perioder, som tretton fyra veckors perioder. Med en tretton kalender med fyra veckor ä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 infaller helgdagar inom samma period varje år. Du kan välja att påbörja en period på valfri dag i veckan. 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 den första söndagen i räkenskapsåret. I det här fallet börjar räkenskapsåret den 1/7.

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.

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

=HELTA([Vecka])

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

=HELTA([Vecka]+3)

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

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

Resultatet ser ut så här:

Räkenskapsår för period

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

=HELTO(([Period]+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 Period och adderar 12:

= ([Punkt]+12)

Vi delar resultatet med 13, eftersom det finns tretton perioder på 28 dagar under räkenskapsåret:

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

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

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

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

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

Resultatet ser ut så här:

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 (med början på söndag) för varje räkenskapsår.

=OM(REST([Period],13), REST([Period],13);13)

Den här formeln är lite mer komplex, så vi beskriver den först på ett språk som vi bättre förstår. Den här formeln anger att värdet ska divideras från [Period] med 13 för att få ett periodnummer (1–13) under året. Om talet är 0 returnerar du 13.

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

=REST([Period],13)

Det här ger oss oftast det resultat vi vill ha, förutom när värdet för period är 0 eftersom dessa datum inte faller inom det första räkenskapsåret, som under de första fem dagarna i vår exempeltabell med kalenderdatum. Vi kan ta hand om detta med en OM-funktion. Om vårt resultat är 0 returnerar vi 13, så här:

=OM(REST([Period],13);REST([Period],13);13)

Resultatet ser ut så här:

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 tabellen Försäljningsfaktura, till exempel DateKey, ShipDate och ReturnDate, kan de alla ha relationer till fältet Datum i tabellen Kalenderdatum, men endast en av dem kan vara en aktiv relation. I det här fallet, eftersom DateKey representerar datumet för transaktionen, och därför det viktigaste datumet, fungerar detta bäst som den aktiva relationen. De andra har inaktiva relationer.

I följande pivottabell beräknas den totala försäljningen efter räkenskapsår och räkenskapskvartal. Ett mått med namnet Total Sales med formeln Total Sales:=SUM([SalesAmount])) placeras i VÄRDEN och fälten Räkenskapsår och Räkenskapskvartr från tabellen Kalenderdatum placeras i RADER.

Den här pivottabellen fungerar korrekt eftersom vi vill summera vår totala försäljning med transaktionsdatumeti DateKey. Måttet Total Sales använder datumen i DateKey och summeras efter räkenskapsår och räkenskapkvartal eftersom det finns en relation mellan DateKey i tabellen Försäljning och kolumnen Datum i tabellen Kalenderdatum.

Inaktiva relationer

Men tänk om vi vill summera vår totala försäljning, 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 den relationen baseras våra aggregeringar alltid på transaktionsdatumet. Men vi kan ha flera relationer, även om bara en kan vara aktiv, och eftersom transaktionsdatum är det viktigaste får det den aktiva relationen med tabellen Kalender.

I det här fallet har ShipDate en inaktiv relation, så alla måttformler som skapas för att aggregera data baserat 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 efter leveransdatum. Vi använder en formel som den här för att ange vilken relation som ska användas:

Total försäljning efter leveransdatum:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))

Den här formeln anger helt enkelt: Beräkna en summa för Försäljningsbelopp, men filtrera med hjälp av relationen mellan kolumnen Leveransdatum i tabellen Försäljning och kolumnen Datum i tabellen Kalender.

Om vi skapar en pivottabell och placerar måttet Total försäljning efter leveransdatum i VÄRDEN och Räkenskapsår och Räkenskapskvartal på RADER ser vi samma totalsumma, men alla andra summabelopp för räkenskapsår och räkenskapskvartal är olika eftersom de baseras på leveransdatum och inte transaktionsdatum.

Om du använder inaktiva relationer kan du bara använda en datumtabell, men det krävs att alla mått (t.ex. Total sales by Ship Date) refererar till den inaktiva relationen i formeln. Det finns ett annat alternativ, d.v.s. 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. Låt oss titta på tabellexemplet Försäljning igen. Vi har tre kolumner med datum som vi kanske vill sammanställa data på:

• En DateKey med försäljningsdatum för varje transaktion.

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

• Ett ReturnDate – med datum och tid då en eller flera returnerade artiklar togs emot.

Kom ihåg att fältet DateKey med transaktionsdatumet är viktigast. Vi kommer att göra de flesta av våra aggregeringar baserat på dessa datum, så vi vill säkert 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 särskilda måttformler, kan vi skapa ytterligare datumtabeller för leveransdatum och returdatum. Sedan kan vi skapa aktiva relationer mellan dem.

I det här exemplet har vi skapat en annan datumtabell med namnet ShipCalendar. Det innebär naturligtvis 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 kolumner med namnet ShipYear, ShipMonth, ShipQuarter och så vidare.

Om vi skapar pivottabellen och placerar måttet Total Sales i VÄRDEN och ShipFiscalYear och ShipFiscalQuarter på RADER, ser vi samma resultat som vi såg när vi skapade en inaktiv relation och ett särskilt beräknat fält för Total Sales by Ship Date.

Var och en av dessa metoder kräver noggrant övervägande. När du använder flera relationer med en enda datumtabell kan du behöva skapa särskilda mått som transiterar inaktiva relationer med hjälp av funktionen USERELATIONSHIP. Å andra sidan kan det vara förvirrande att skapa flera datumtabeller 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: Ange egenskapen Datumtabell

1. Välj tabellen Kalender i PowerPivot-fönstret.

2. Klicka på Markera som datumtabell på fliken Design.

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

Konvertera datatypsdatum för text till en datumdatatyp

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

Vanligtvis 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 sann datumdatatyp. Om Power Pivot inte kan utföra en datatypskonvertering får du ett typfel.

Du kan dock fortfarande konvertera datumen till en sann datumdatatyp. 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 den 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å Datatyp i gruppen Formatering på fliken Start i Power Pivot ser vi att det är datatypen Text.

Vi kan inte skapa en relation mellan kolumnen DateTime och datumkolumnen i vår datumtabell eftersom datatyperna inte matchar. Om vi försöker ändra datatypen till Datum får vi ett typfel:

I det här fallet gick det inte att konvertera datatypen från text till datum i Power Pivot. Vi kan fortfarande använda den här kolumnen, men för att få den till en sann datumdatatyp måste vi skapa en ny kolumn som tolkar texten och återskapar den 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 är en precisionsnivå för tid på dagen bör du konvertera datum i faktatabellen till en dagsprecisionsnivå. Med det i åtanke vill vi att värdena i vår nya kolumn ska vara på dagsprecisionsnivån (exklusive tid). Vi kan både konvertera värdena i kolumnen DateTime till en datumdatatyp och ta bort tidsnivån för precision med följande formel:

=DATUM(VÄNSTER([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2))

Då får vi en ny kolumn (i det här fallet datum). Power Pivot identifierar även värden som datum och anger datatypen automatiskt till Datum.

Om vi vill behålla tidsnivån för precision utökar vi formeln till att omfatta timmar, minuter och sekunder.

=DATUM(VÄNSTER([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2)) +

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

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

Ytterligare resurser

Datum i PowerPivot

Beräkningar i Power Pivot

Snabbstart: Grunderna i DAX på 30 minuter

Referens för dataanalysuttryck

DAX Resource Center