Förstå och skapa datumtabeller i PowerPivot i Excel

Datumtabellerna i Power Pivot är nödvändiga för att bläddra bland data och beräkna data över tid. I den här artikeln får du ingående förståelse för datumtabeller och hur du kan skapa dem i Power Pivot. I den här artikeln beskrivs särskilt:

• Varför en datumtabell är viktig för att bläddra bland och beräkna data efter datum och tid.
• Så här lägger du till en datumtabell i datamodellen med Power Pivot.
• Så här skapar du nya datumkolumner som År, Månad och Period i en datumtabell.
• Lär dig hur du skapar relationer mellan datumtabeller och faktatabeller.
• Hur man arbetar med tiden.

Den här artikeln är avsedd för användare som inte använt Power Pivot tidigare. Det är dock viktigt att redan ha goda kunskaper om hur man importerar data, skapar relationer och skapar beräknade kolumner och mått.

I den här artikeln beskrivs inte hur du använder DAX Time-Intelligence-funktioner i måttformler. Mer information om hur du skapar mått med DAX-tidsinformationsfunktioner finns i Tidsinformation i Power Pivot i Excel.

Innehåll

• Förstå datumtabeller

• Lägga till en datumtabell i datamodellen

  • Importera från en relationsdatabas

  • Skapa en datumtabell i Excel

    • Så här gör du: Skapa en datumtabell i Excel och kopiera den till datamodellen

• Lägga till nya datumkolumner i datumtabellen

  • DAX-funktioner för datum och tid

  • Exempel på formler för ett kalenderår

    • År
    • Månad
    • Kvartal
    • Månadsnamn

  • Formelexempel för ett räkenskapsår

    • Räkenskapsår
    • Räkenskapsmånad
    • Räkenskapskvartal

• Helgdagar eller särskilda datum

• Anpassad kalender – tretton fyraveckorsperioder

  • Vecka
  • Period
  • Räkenskapsår för period
  • Period i räkenskapsåret

• Relationer

  • Flera relationer
  • Inaktiva relationer
  • Flera datumtabeller

• Egenskapen Datumtabell

• Arbeta med tiden

• Gör datum lättare att använda

• Bilaga

  • Konvertera datum för textdata till datatypen datum
  • Ytterligare resurser

Förstå datumtabeller

Nästan all dataanalys innefattar bläddra bland och jämför data över datum och tid. Du kanske till exempel vill summera försäljningsbeloppen för det senaste räkenskapskvartalet och sedan jämföra dessa summor med andra kvartal, eller så kanske du vill beräkna en balansgång vid årets slut för ett konto. I vart och ett av dessa fall använder du datum som ett sätt att gruppera och aggregera försäljningstransaktioner eller saldon för en viss tidsperiod.

Power View-rapport

En datumtabell kan innehålla många olika representationer av datum och tid. En datumtabell innehåller till exempel ofta kolumner som Räkenskapsår, Månad, Kvartal eller Period som du kan välja som fält i en fältlista när du delar upp och filtrerar data i pivottabeller eller Power View-rapporter.

Power View-fältlista

För att datumkolumner som År, Månad och Kvartal ska ta med alla datum inom respektive intervall måste datumtabellen ha minst en kolumn med en sammanhängande uppsättning datum. Det innebär att kolumnen måste ha en rad för varje dag och år som ingår i datumtabellen.

Om de data du vill bläddra bland är från den 1 februari 2010 till den 30 november 2012, och du rapporterar ett kalenderår, då behöver du en datumtabell med åtminstone ett datumintervall från den 1 januari 2010 till den 31 december 2012. Varje år i datumtabellen måste innehålla alla dagar för varje år. Om du regelbundet kommer att uppdatera dina data med nyare data kanske du vill förlänga slutdatumet med ett eller två år så att du inte behöver uppdatera datumtabellen allt eftersom.

Datumtabell med en sammanhängande uppsättning datum

Om du rapporterar för ett räkenskapsår kan du skapa en datumtabell med en sammanhängande uppsättning datum för varje räkenskapsår. Om räkenskapsåret till exempel börjar den 1 mars och du har data för räkenskapsåret 2010 fram till det aktuella datumet (till exempel för räkenskapsåret 2013) kan du skapa en datumtabell som börjar 2009-03-01 och innehåller minst alla dagar under varje räkenskapsår till och med det sista datumet i räkenskapsåret 2013.

Om du ska rapportera för både kalenderår och räkenskapsår behöver du inte skapa separata datumtabeller. En tabell med ett enda datum kan innehålla kolumner för ett kalenderår, ett räkenskapsår och till och med en tretton fyraveckorskalender. Det viktiga är att datumtabellen innehåller en sammanhängande uppsättning datum för alla år som ingår.

Lägga till en datumtabell i datamodellen

Du kan lägga till en datumtabell i datamodellen på flera sätt:

• Importera från en relationsdatabas eller någon annan datakälla.
• Skapa en datumtabell i Excel och kopiera sedan eller länka till en ny tabell i Power Pivot.
• Importera från Microsoft Azure Marketplace.

Låt oss titta närmare på var och en av dessa.

Importera från en relationsdatabas

Om du importerar vissa eller alla dina data från ett informationslager eller någon annan typ av relationsdatabas är sannolikheten stor att det redan finns en datumtabell och relationer mellan den och resten av informationen du importerar. Datumen och formatet kommer antagligen att matcha datumen i dina faktadata, och datumen börjar förmodligen långt tillbaka i tiden och ligger långt fram i tiden. Datumtabellen som du vill importera kan vara mycket stor och innehålla ett datumintervall utöver vad du behöver inkludera i din datamodell. Du kan använda avancerade filterfunktioner i Power Pivots guide för tabellimport för att välja endast de datum och kolumner som du verkligen behöver. Det kan avsevärt minska arbetsbokens storlek och förbättra prestanda.

Guiden Importera tabell

I de flesta fall behöver du inte skapa ytterligare kolumner som räkenskapsår, vecka, månadsnamn osv. eftersom de redan finns i den importerade tabellen. Men i vissa fall, när du har importerat datumtabellen till din datamodell, kan du behöva skapa ytterligare datumkolumner, beroende på ett visst rapporteringsbehov. Lyckligtvis är det enkelt att göra med DAX. Du kommer att lära dig mer om hur du skapar datumtabellfält senare. Alla miljöer är olika. Om du är osäker på om dina datakällor har ett relaterat datum eller en relaterad kalendertabell bör du prata med databasadministratören.

Skapa en datumtabell i Excel

Du kan skapa en datumtabell i Excel och sedan kopiera den till en ny tabell i datamodellen. Detta är egentligen ganska enkelt 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 sammanhängande datumintervall. Du kan sedan skapa ytterligare kolumner, till exempel Å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. Hur du skapar ytterligare datumkolumner i Power Pivot beskrivs i avsnittet Lägga till nya datumkolumner i datumtabellen senare i den här artikeln.

Så här gör du: Skapa en datumtabell i Excel och kopiera den till datamodellen

1. I Excel skriver du ett kolumnrubriknamn i cell A1 i ett tomt kalkylblad för att identifiera ett datumintervall. Vanligtvis blir detta något i stil med Datum, Datumtid eller DatumNyckel.

2. Skriv ett startdatum i cell A2. Till exempel 2010-01-01.

3. Klicka på fyllningshandtaget och dra det nedåt till ett radnummer som innehåller ett slutdatum. Till exempel 2016-12-31.
   

4. Markera alla rader i kolumnen Datum (inklusive rubriknamnet i cell A1).

5. Klicka på Formatera som tabell i gruppen Format och välj ett format.

6. Klicka på OK i dialogrutan Formatera som tabell.
   

7. Kopiera alla rader, inklusive rubriken.

8. Klicka på Klistra in på fliken Start i Power Pivot.

9. Skriv ett namn som Datum eller Kalender i Förhandsgranska> inklistring. Låt alternativet Använd den första raden som kolumnrubrikvara markerat och klicka på OK.
   
   Den nya datumtabellen (som heter Calendar i detta exempel) i Power Pivot ser ut så här:
   

   Obs

   Du kan också skapa en länkad tabell med hjälp av Lägg till i datamodell. Men detta gör arbetsboken onödigt stor eftersom arbetsboken har två versioner av datumtabellen; en i Excel och en i PowerPivot.

Nu har du en datumtabell i datamodellen. Du kan lägga till nya datumkolumner, t.ex. År, Månad osv., med hjälp av DAX.

Lägga till nya datumkolumner i datumtabellen

En datumtabell med en enda datumkolumn som innehåller en rad för varje dag 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 där ensamma datumkolumnen med en rad för varje dag är inte användbar vid analys efter datum i en pivottabell eller Power View-rapport. Du vill att din datumtabell ska innehålla kolumner som du kan använda för att aggregera data för ett intervall 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 aktuella perioden.

Om du har importerat en datumtabell 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 några av dessa kolumner eller skapa ytterligare datumkolumner. Det gäller särskilt om du skapar en egen datumtabell i Excel och kopierar den till datamodellen. Lyckligtvis är det ganska enkelt att skapa nya datumkolumner i Power Pivot med datum- och tidsfunktionerna i DAX.

DAX-funktioner för datum och tid

Om du någon gång har arbetat med datum- och tidsfunktioner i Excel-formler är du förmodligen bekant med datum- och tidsfunktionerna. Även om funktionerna liknar sina motsvarigheter i Excel finns det några viktiga skillnader:

• DAX-datum- och tidsfunktioner använder en datetime-datatyp.
• De kan använda 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 flera av dessa funktioner till att skapa kolumner för År, Kvartal, Räkenskapsmånad och så vidare.

I DAX ingår följande datum- och tidsfunktioner:

• DATE (datum)
• DATUMVÄRDE
• NÄSTA DAG
• EDATUM
• SLUTMÅNAD
• HOUR
• MINUTE
• MONTH
• NU
• SECOND
• TIME
• TIDVÄRDE
• I DAG
• VECKODAG
• VECKONR
• YEAR
• ÅRDEL

Det finns många andra DAX-funktioner som du kan använda i formler också. Många av de formler som beskrivs här använder till exempel matematiska och trigonometriska funktioner som REST och TRUNC, logiska funktioner som OM och textfunktioner som FORMAT Mer information om andra DAX-funktioner finns i avsnittet Ytterligare resurser senare 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 Calendar. En kolumn med namnet Datum finns redan och innehåller ett sammanhängande datumintervall från 2010-01-01 till 2016-12-31.

År

=ÅR([datum])

I den här formeln returnerar funktionen ÅR årtalet från värdet i kolumnen Datum. Eftersom värdet i datumkolumnen är av datatypen datetime vet funktionen ÅR hur årtalet ska returneras från det.

Månad

=MÅNAD([datum])

I den här formeln kan vi, precis som med funktionen ÅR, helt enkelt använda funktionen MÅNAD för att returnera ett månadsvärde från kolumnen Datum.

Kvartal

=HELTAL(([Månad]+2)/3)

I den här formeln använder vi funktionen HELTAL för att returnera ett datumvärde som ett heltal. Argumentet vi anger för funktionen HELTAL är värdet från kolumnen Månad, addera 2 och sedan dividera det med 3 för att få vårt kvartal, 1 till 4.

Månadsnamn

=FORMAT([datum],"mmmm")

För att få fram månadsnamnet använder vi funktionen FORMAT i den här formeln för 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 alla tecken ska visas i månadens namn, 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 formatargumentet.

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 dagnamn anger vi "ddd" i formatargumentet.

Exempel på pivottabell

När du har fält för datum som År, Kvartal, Månad och så vidare kan du använda dem i en pivottabell eller 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. SalesAmount aggregeras för års- och kvartalskontext.

Formelexempel för ett räkenskapsår

Räkenskapsår

=OM([Månad]<= 6;[År];[År]+1)

I det här exemplet börjar räkenskapsåret den 1 juli.

Det finns ingen funktion som kan extrahera ett räkenskapsår från ett datumvärde eftersom start- och slutdatum för ett räkenskapsår ofta skiljer sig från dem för ett kalenderår. För att få räkenskapsåret använder vi först en OM-funktion för att testa om värdet för Månad är mindre än eller lika med 6. Om värdet för Månad i det andra argumentet är mindre än eller lika med 6 returneras värdet från kolumnen År i det andra argumentet. Annars returnerar du värdet från År och adderar 1.

Ett annat sätt att ange ett värde för räkenskapsårets slutmånad är att skapa ett mått som bara anger månaden. Till exempel FYE:=6. Du kan sedan referera till måttnamnet i stället för månadsnumret. Exempel: =OM([Månad]<=[FYE];[År];[År]+1). Detta ger större flexibilitet när du refererar till räkenskapsårets slutmånad i flera olika formler.

Räkenskapsmånad

=OM([Månad]<= 6;6+[Månad];[Månad]-6)

I den här formeln anger vi om värdet för [Månad] är mindre än eller lika med 6, sedan tar vi 6 och adderar värdet från Månad, annars subtraherar vi 6 från värdet från [Månad].

Räkenskapskvartal

=HELTAL(([FiscalMonth]+2)/3)

Formeln vi använder för räkenskapskvartal är i stort sett densamma som den var för kvartal under vårt kalenderår. Den enda skillnaden är att vi anger [FiscalMonth] i stället för [Month].

Helgdagar eller särskilda datum

Du kanske vill ta med en datumkolumn som anger att vissa datum är helgdagar eller andra speciella datum. Du kanske till exempel vill summera den totala försäljningen för nyår genom att lägga till ett fält med 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 inkludera helgdagar eller speciella dagar är ganska enkelt. Du kan skapa en tabell i Excel med de datum som du vill inkludera. 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 Calendar -tabellen. Alla formler som refererar till den kan använda funktionen LOOKUPVALUE för att returnera värden.

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

Datum	Helgdag
1/1/2010	Nyår
11/25/2010	Tacksägelse
12/25/2010	Jul och jul
2011-01-01	Nyår
11/24/2011	Tacksägelse
12/25/2011	Jul och jul
1/1/2012	Nyår
2012-11-22	Tacksägelse
12/25/2012	Jul och jul
1/1/2013	Nyår
11/28/2013	Tacksägelse
12/25/2013	Jul och jul
11/27/2014	Tacksägelse
12/25/2014	Jul och jul
2014-01-01	Nyår
11/27/2014	Tacksägelse
12/25/2014	Jul och jul
1/1/2015	Nyår
11/26/2014	Tacksägelse
12/25/2015	Jul och jul
2016-01-01	Nyår
11/24/2016	Tacksägelse
12/25/2016	Jul och jul

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

=LETAUPPVÄRDE(Helgdagar[Helgdag];Helgdagar[datum];Calendar[datum])

Nu ska vi titta närmare på den här formeln.

Vi använder funktionen LETAUPPVÄRDE för att hämta värden från kolumnen Helgdagar i tabellen Helgdagar. I det första argumentet anger vi kolumnen där vårt resultatvärde kommer att vara. Vi anger kolumnen Helgdagar i tabellen Helgdagar eftersom det är det värde som ska returneras.

=LETAUPPVÄRDE(Helgdagar[Helgdag];Helgdagar[datum];Calendar[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[Helgdag];Helgdagar[datum];Calendar[datum])

Slutligen anger vi kolumnen i tabellen Calendar som innehåller de datum vi vill söka efter i tabellen Semester. Detta är naturligtvis kolumnen Datum i tabellen Calendar.

=LETAUPPVÄRDE(Helgdagar[Helgdag];Helgdagar[datum];Calendar[datum])

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

Anpassad kalender – tretton fyraveckorsperioder

Vissa organisationer, som detaljhandel eller livsmedelsservice, rapporterar ofta om olika perioder, till exempel tretton fyraveckorsperioder. Med en tretton fyraveckorskalender ä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 under samma period varje år. Du kan välja att börja mensen vilken dag som helst 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.

Vecka

Det här värdet ger oss veckonumret som börjar med den första hela veckan i räkenskapsåret. I det här exemplet börjar den första fullständiga veckan på söndag, så den första fullständiga veckan under det första räkenskapsåret i tabellen Calendar börjar faktiskt den 4/7/2010 och fortsätter genom den sista fullständiga veckan i tabellen Calendar. Även om det här värdet i sig inte är så användbart i analyser är det nödvändigt att beräkna för användning i andra formler för 28-dagarsperioder.

=HELTAL([datum]-40356)/7)

Nu ska vi titta närmare på den här formeln.

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

=HELTAL([datum])

Vi vill sedan söka efter den första söndagen i det första räkenskapsåret. Vi ser att det är 2010-07-04.

Subtrahera nu 40356 (vilket är heltalet för 2010-06-27, den sista söndagen från föregående räkenskapsår) från det värdet för att få antalet dagar sedan början av dagarna i vår Calendar -tabell, så här:

=HELTAL([datum]-40356)

Dividera sedan resultatet med 7 (dagar på en vecka), så här:

=HELTAL(([datum]-40356)/7)

Resultatet ser ut så här:

Punkt

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

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

Nu ska vi 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])

Addera sedan 3 till värdet, så här:

=HELTAL([Vecka]+3)

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

=HELTAL(([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.

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

Nu ska vi titta närmare på den här formeln.

Först skapar vi en formel som returnerar ett värde från Period och lägger till 12:

=([Period]+12)

Vi dividerar resultatet med 13 eftersom det finns tretton perioder med 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 alla bråkdelar av resultatet och returnera ett heltal när det divideras med 13, så här:

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

Resultatet ser ut så här:

Period i räkenskapsåret

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) i varje räkenskapsår.

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

Den här formeln är lite mer komplex, så vi kommer att beskriva den först på ett språk som vi bättre förstår. Den här formeln anger att du ska 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:

= REST([Punkt];13)

Detta ger oss för det mesta det resultat vi vill ha, förutom där värdet för Period är 0 eftersom dessa datum inte infaller under det första räkenskapsåret, som under de första fem dagarna i vår exempeldatumtabell i Calendar. Vi kan ta hand om detta med en OM-funktion. Om resultatet är 0 returnerar vi 13, så här:

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

Resultatet ser ut så här:

Exempel på pivottabell

Bilden nedan visar en pivottabell med fältet Försäljningsbelopp från faktatabellen Försäljning i VÄRDEN och fälten PeriodFiscalYear och PeriodInFiscalYear från datumdimensionstabellen Kalender i RADER. SalesAmount aggregeras för kontexten efter räkenskapsår och 28-dagarsperiod under räkenskapsåret.

Relationer

När du har skapat en datumtabell i datamodellen måste du skapa en relation mellan faktatabellen med dina transaktionsdata och datumtabellen för att kunna bläddra bland data i pivottabeller och rapporter och aggregera data baserat på kolumnerna i datumdimensionstabellen.

Eftersom du måste skapa en relation som baseras på datum bör du se till att du skapar relationen mellan kolumner vars värden är av datatypen datetime (Date).

För varje datumvärde i faktatabellen måste den relaterade uppslagskolumnen i datumtabellen innehålla överensstämmande värden. Till exempel måste en rad (transaktionspost) i faktatabellen Försäljning med värdet 8/15/2012 12:00 AM i kolumnen DateKey ha ett motsvarande värde i den relaterade datumkolumnen i datumtabellen (namngiven Calendar). Det här är en av de viktigaste anledningarna till att du vill att datumkolumnen i datumtabellen ska innehålla ett sammanhängande datumintervall som inkluderar alla möjliga datum i faktatabellen.

Om du får ett Type Mismatch-fel när du försöker skapa en relation beror det sannolikt på att kolumnen i faktatabellen inte är av datatypen Date. Det här kan inträffa när Power Pivot inte automatiskt kan konvertera en icke-datumdatatyp (vanligtvis en textdatatyp) till en datumdatatyp. Du kan fortfarande använda kolumnen i faktatabellen, men du måste konvertera data med en DAX-formel i en ny beräknad kolumn. Mer information finns i Konvertera datum från textdatatyper till datatyper för datum senare i bilagan.

Flera relationer

I vissa fall kan det vara nödvändigt att skapa flera relationer eller flera datumtabeller. Om det exempelvis finns flera datumfält i faktatabellen Försäljning, t.ex. DateKey, ShipDate och ReturnDate, kan de alla ha relationer till fältet Date i datumtabellen Calendar, men endast ett av dem kan vara en aktiv relation. Eftersom DateKey i det här fallet representerar datumet för transaktionen, och därmed det viktigaste datumet, fungerar detta bäst som den aktiva relationen. De andra har inaktiva relationer.

Följande pivottabell beräknar den totala försäljningen per räkenskapsår och räkenskapskvartal. Ett mått med namnet Total Sales, med formeln Total Sales:=SUM([SalesAmount]), placeras i VALUES, och fälten FiscalYear och FiscalQuarter från datumtabellen i Calendar placeras i RADER.

Den här enkla pivottabellen fungerar korrekt eftersom vi vill summera vår totala försäljning med transaktionsdatumet i DateKey. Måttet Total Sales använder datumen i DateKey och summeras efter räkenskapsår och räkenskapskvartal eftersom det finns en relation mellan DateKey i tabellen Sales och kolumnen Date i datumtabellen Calendar.

Inaktiva relationer

Men vad händer om vi vill summera vår totala försäljning efter leveransdatum och inte efter transaktionsdatum? Vi behöver en relation mellan kolumnen Leveransdatum i tabellen Försäljning och kolumnen Datum i tabellen Calendar. Om vi inte skapar den relationen baseras våra aggregeringar alltid på transaktionsdatumet. Vi kan emellertid ha flera relationer, även om bara en kan vara aktiv, och eftersom transaktionsdatumet är det viktigaste får det den aktiva relationen med Calendar -tabellen.

I det här fallet har Leveransdatum 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 Calendar, kan vi skapa ett mått som summerar den totala försäljningen per leveransdatum. Vi använder en formel som den här för att ange den relation som ska användas:

Total försäljning per 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 genom att använda relationen mellan kolumnen Leveransdatum i tabellen Försäljning och kolumnen Datum i tabellen Calendar.

Om vi nu skapar en pivottabell och placerar måttet Total försäljning per leveransdatum i VÄRDEN och Räkenskapsår och Räkenskapskvartal på RADER får vi samma totalsumma, men alla andra summor 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äver att alla mått (som Total försäljning per leveransdatum) refererar till den inaktiva relationen i formeln. Det finns ett annat alternativ, nämligen att använda flera datumtabeller.

Flera datumtabeller

Ett annat sätt att arbeta med flera datumkolumner i en faktatabell är att skapa flera datumtabeller och skapa separata aktiva relationer mellan dem. Låt oss titta på exemplet med tabellen Försäljning igen. Vi har tre kolumner med datum som vi kanske vill aggregera data för:

• En DateKey med försäljningsdatum för varje transaktion.
• Ett leveransdatum – med datum och tid när de sålda artiklarna levererades till kunden.
• Ett returdatum – med datum och tid när ett eller flera returnerade objekt togs emot.

Kom ihåg att fältet Datumnyckel med transaktionsdatum är viktigast. Vi kommer att göra de flesta av våra aggregeringar baserat på dessa datum, så vi vill absolut ha en relation mellan dem och datumkolumnen i tabellen Calendar. Om vi inte vill skapa inaktiva relationer mellan Leveransdatum och Returdatum och fältet Datum i tabellen Calendar, och därmed kräver speciella måttformler, kan vi skapa ytterligare datumtabeller för leveransdatum och returdatum. Vi kan då skapa aktiva relationer mellan dem.

I det här exemplet har vi skapat en annan datumtabell med namnet Leveranskalender. Det innebär naturligtvis också att du skapar ytterligare datumkolumner, och eftersom dessa datumkolumner finns i en annan datumtabell vill vi namnge dem på ett sätt som skiljer dem från samma kolumner i tabellen Calendar. Vi har t.ex. skapat kolumner som heter 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 får vi samma resultat som när vi skapade en inaktiv relation och ett särskilt beräknat fält med 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 överför 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äver det mer minne. Experimentera med vad som passar dig bäst.

Egenskapen Datumtabell

Datumtabellegenskapen anger metadata som krävs för att Time-Intelligence funktioner som TOTALYTD, PREVIOUSMONTH och DATESBETWEEN ska fungera korrekt. När en beräkning körs med någon av dessa funktioner vet formelmotorn i Power Pivot var den ska gå för att hämta de datum som behövs.

När du anger egenskapen Datumtabell anger du en datumtabell och en datumkolumn av datatypen Datum (datetime).

Instruktion: Ange egenskapen Datumtabell

1. Välj tabellen Calendar 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.

Arbeta med tiden

Alla datumvärden med en datumdatatyp i Excel eller SQL Server är faktiskt ett tal. I numret ingår siffror som refererar till en tid. I många fall är klockslaget midnatt för varje rad. Om till exempel ett DateTimeKey-fält i en försäljningsfaktatabell har värden som 10/19/2010 12:00:00 AM, innebär det att värdena är på dagsnivå för precision. Om värdena i DateTimeKey-fältet har en tid inkluderad, till exempel 10/19/2010 8:44:00 AM, innebär det att värdena är på minutprecisionsnivå. Värdena kan också vara precisionen på timnivå eller till och med sekundprecisionen. Precisionen i tidsvärdet har stor påverkan på hur du skapar datumtabellen och relationerna mellan den och faktatabellen.

Du måste bestämma om du ska aggregera dina data med dagsprecision eller tidsprecision. Med andra ord kanske du vill använda kolumner i datumtabellen, till exempel Morgon, Eftermiddag eller Timme, som datumfält i pivottabellens rad-, kolumn- eller filterområden.

Om du tänker aggregera data till tidsnivån måste datumtabellen innehålla en datumkolumn med tiden inkluderad. I själva verket behövs en datumkolumn med en rad för varje timme, eller kanske till och med varje minut, varje dag, för varje år i datumintervallet. Det beror på att du måste ha matchande värden för att kunna skapa en relation mellan kolumnen DateTimeKey i faktatabellen och datumkolumnen i datumtabellen. Som du kan föreställa dig, om du inkluderar många år, kan detta ge en mycket stor datumtabell.

I de flesta fall vill du dock bara aggregera data för dagen. Med andra ord använder du kolumner som År, Månad, Vecka eller Dag i veckan som fält i pivottabellens rad-, kolumn- eller filterområden. I det här fallet behöver datumkolumnen i datumtabellen bara innehålla en rad för varje dag på ett år, så som vi beskrev ovan.

Om datumkolumnen innehåller en tidsnivå för precision, men du bara ska aggregera till en dagsnivå, kan du behöva ändra din faktatabell genom att skapa en ny kolumn som trunkerar värdena i datumkolumnen till ett dagsvärde, för att skapa relationen mellan faktatabellen och datumtabellen. Med andra ord, konvertera ett värde som 10/19/2010 8:44:00AM till 10/19/2010 12:00:00 AM. Du kan sedan skapa relationen mellan den nya kolumnen och datumkolumnen i datumtabellen eftersom värdena matchar.

Låt oss titta på ett exempel. Den här bilden visar en DateTimeKey-kolumn i faktatabellen Försäljning. Alla aggregeringar för data i den här tabellen behöver bara vara på dagsnivå, med hjälp av kolumner i datumtabellen i Calendar som Year, Month, Quarter osv. Tiden som ingår i värdet är inte relevant, utan endast det faktiska datumet.

Eftersom vi inte behöver analysera dessa data på tidsnivå behöver vi inte datumkolumnen i datumtabellen Calendar för att inkludera en rad för varje timme och varje minut varje dag varje år. Datumkolumnen i vår datumtabell ser alltså ut så här:

Om du vill skapa en relation mellan kolumnen DateTimeKey i tabellen Försäljning och kolumnen Datum i tabellen Calendar kan du skapa en ny beräknad kolumn i faktatabellen Försäljning och använda funktionen TRUNC för att trunkera datum- och tidsvärdet i kolumnen DateTimeKey till ett datumvärde som matchar värdena i kolumnen Datum i tabellen Calendar. Formeln ser ut så här:

=AVKORTA([DateTimeKey];0)

Detta ger oss en ny kolumn (vi döpte till DateKey) med datumet från DateTimeKey-kolumnen och klockan 12:00:00 för varje rad:

Nu kan vi skapa en relation mellan den här nya kolumnen (DateKey) och kolumnen Date i tabellen Calendar.

På samma sätt kan vi skapa en beräknad kolumn i tabellen Försäljning som minskar tidsprecisionen i kolumnen DateTimeKey till timprecisionen. I det här fallet fungerar inte TRUNC-funktionen, men vi kan fortfarande använda andra DAX-funktioner för datum och tid för att extrahera och sammanfoga ett nytt värde till en timmes precision. Vi kan använda en formel som den här:

= DATUM (ÅR([DateTimeKey]), MONTH([DateTimeKey]), DAY([DateTimeKey]) ) + TIME (HOUR([DateTimeKey]), 0, 0)

Vår nya kolumn ser ut så här:

Förutsatt att datumkolumnen i datumtabellen har värden med timprecision kan vi sedan skapa en relation mellan dem.

Gör datum lättare att använda

Många av de datumkolumner du skapar i din datumtabell är nödvändiga för andra fält, men är egentligen inte så användbara vid analys. Till exempel är fältet Datumnyckel i tabellen Försäljning som vi har refererat till och visat i den här artikeln viktigt eftersom för varje transaktion registreras transaktionen som att den inträffade vid ett visst datum och en viss tidpunkt. Men ur analys- och rapporteringssynpunkt är det inte så användbart eftersom vi inte kan använda det som ett rad-, kolumn- eller filterfält i en pivottabell eller rapport.

På samma sätt är kolumnen Datum i tabellen Calendar i vårt exempel mycket användbar, faktiskt kritisk, men du kan inte använda den som en dimension i en pivottabell.

För att tabellerna och kolumnerna i dem ska vara så användbara som möjligt och för att göra det lättare att navigera i fältlistorna i pivottabeller och Power View-rapporter, är det viktigt att dölja onödiga kolumner för klientverktyg. Du kanske också vill dölja vissa tabeller. Tabellen helgdagar som visas tidigare innehåller helgdagar som är viktiga för vissa kolumner i tabellen Calendar, men du kan inte använda kolumnerna Datum och Helgdagar i själva tabellen Helgdagar som fält i en pivottabell. Även här kan du, för att göra det lättare att navigera i fältlistorna, dölja hela tabellen Helgdagar.

En annan viktig aspekt av att arbeta med datum är namnkonventioner. Du kan namnge tabeller och kolumner i Power Pivot vad du vill. Tänk dock på att en bra namngivningskonvention gör det lättare att identifiera tabeller och datum, inte bara i fältlistor utan även i Power Pivot och i DAX-formler, särskilt om du ska dela arbetsboken med andra användare.

När du har en datumtabell i datamodellen kan du börja skapa mått som hjälper dig att få ut mesta möjliga av dina data. Vissa kan vara så enkla som att summera försäljningssummor för det aktuella året, och andra kan vara mer komplexa, där du behöver filtrera på ett visst intervall av unika datum. Läs mer i Mått i Power Pivot - och tidsinformationsfunktioner.

Bilaga

Konvertera datum för textdata till datatypen datum

I vissa fall kan en faktatabell med transaktionsdata innehålla datum av datatypen text. Det innebär att ett datum som visas som 2012-12-04T11:47:09 i själva verket inte är 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. Om du vill skapa en relation mellan en datumkolumn i en faktatabell 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 av datatypen Datum till datatypen Datum kan Power Pivot tolka datumen och automatiskt konvertera dem till datatypen Sant. Om det inte går att konvertera datatyper i Power Pivot får du ett felmeddelandet om typmatchningsfel.

Du kan dock fortfarande konvertera datumen till datatypen Sant datum. Du kan skapa en ny beräknad kolumn och använda en DAX-formel för att parsa år, månad, dag, tid osv. från textsträngarna och sedan sammanfoga dem igen på ett sätt som Power Pivot kan läsa av 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ärdena ser ut så här:

Om vi tittar på Datatyp 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 kolumnen DateTime och kolumnen Date i vår datumtabell eftersom datatyperna inte matchar. Om vi försöker ändra datatypen till Datum får vi ett felmeddelande om typmatchning:

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 datatypen sant datum måste vi skapa en ny kolumn som parsar texten och återskapar den till ett värde som Power Pivot kan göra till datatypen Datum.

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

=DATUM(VÄNSTER([Datumtid];4), EXTEXT([Datumtid];6;2), EXTEXT([Datumtid];9;2))

Det ger oss en ny kolumn (i det här fallet med namnet Datum). Power Pivot identifierar till och med värdena som datum och anger automatiskt datatypen till Datum.

Om vi vill behålla tidsnivån för precision utökar vi helt enkelt formeln så att den omfattar timmar, minuter och sekunder.

=DATUM(VÄNSTER([Datumtid];4), EXTEXT([Datumtid];6;2), EXTEXT([Datumtid];9;2)) +

TID(EXTEXT([DateTime];12;2), EXTEXT([DateTime];15;2), EXTEXT([DateTime];18;2))

Nu när vi har en datumkolumn av 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-resurscenter