Förstå och skapa datumtabeller i PowerPivot i Excel

Obs!: Vi vill kunna erbjuda dig bästa möjliga supportinnehåll så fort som möjligt och på ditt språk. Den här sidan har översatts med hjälp av automatiserad översättning och kan innehålla grammatiska fel eller andra felaktigheter. Vår avsikt är att den här informationen ska vara användbar för dig. Vill du berätta för oss om informationen är till hjälp längst ned på sidan? Här är artikeln på engelska som referens.

Datum tabeller i Power Pivot är nödvändiga för att bläddra och beräkna data över tid. Den här artikeln innehåller en grundlig förståelse av datum tabeller och hur du kan skapa dem i Power Pivot. I den här artikeln beskrivs bland annat:

  • Varför en datum tabell är viktig för bläddring och beräkning av data efter datum och tid.

  • Använda Power Pivot för att lägga till en datum tabell i data modellen.

  • Så här skapar du nya datum kolumner, till exempel år, månad och period i en datum tabell.

  • Så här skapar du relationer mellan datum tabeller och fakta tabeller.

  • Så här arbetar du med tid.

Den här artikeln är avsedd för användare som är nya i Power Pivot. Men det är viktigt att veta hur du kan importera data, skapa relationer och skapa beräknade kolumner och mått.

I den här artikeln beskrivs inte hur du använder DAX Time-Intelligence-funktioner i Mät formler. Mer information om hur du skapar mått i tidsInformation-funktioner för DAX finns i Värdejämföring i Power Pivot i Excel.

Obs!: I Power Pivot är namnen "Measure" och "beräknat fält" är synonyma. Vi använder namn åtgärderna i den här artikeln. Mer information finns i mått i Power Pivot.

Innehåll

Förstå datum tabeller

Lägga till en datum tabell i data modellen

Importera från en Relations databas

Skapa en datum tabell i Excel

Så här skapar du en datum tabell i Excel och kopierar den till data modellen

Lägga till nya datum kolumner i datum tabellen

DAX-funktioner för datum och tid

Formel exempel för ett kalender år

År

Månader

Kvartal

Månadens namn

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

Räkenskapsår

Räkenskaps månad

Räkenskaps kvartal

Helgdagar eller särskilda datum

Anpassad kalender – 13 4-vecko perioder

Vecka

Period

Räkenskapsåret period

Period i räkenskapsåret

Relationer

Flera relationer

InAktiva relationer

Flera datum tabeller

Egenskapen datum tabell

Arbeta med tid

Göra datum mer användbara

Bilaga

Konvertera text data typs datum till data typen Datum

Ytterligare resurser

Förstå datum tabeller

Nästan alla data analyser innebär att bläddra och jämföra data med datum och tid. Du kanske till exempel vill summera försäljnings belopp för det förflutna räkenskaps kvartalet och sedan jämföra summorna med andra kvartal, eller så kanske du vill beräkna en års sluts räkning för ett konto. I de här fallen använder du datum som ett sätt att gruppera och aggregera försäljnings transaktioner eller-saldon för en viss tids period.

Power View-rapport

Pivottabell med total försäljning per räkenskapskvartal

En datum tabell kan innehålla flera olika representationer av datum och tid. En datum tabell har till exempel ofta kolumner som räkenskapsår, månad, kvartal eller period som du kan välja som fält från en fält lista om du vill dela och filtrera data i pivottabeller och Power View-rapporter.

Fält lista i Power View

Power View-fältlista

För datum kolumner som år, månad och kvartal för att inkludera alla datum inom deras respektive område måste datum tabellen innehålla minst en kolumn med en sammanhängande datum uppsättning. Det vill säga att kolumnen måste innehålla en rad för varje dag för varje år i datum tabellen.

Om de data som du vill söka efter har datum från den 1 februari 2010 till och med 30 november 2012 och du rapporterar om ett kalender år, måste du ha en datum tabell med minst ett datum intervall från den 1 januari 2010 till 31 december 2012. Varje år i din datum tabell måste innehålla alla dagar för varje år. Om du regelbundet uppdaterar dina data med nyare data kanske du vill köra slutdatumet med ett år eller två, så du behöver inte uppdatera din datum tabell när tiden går ut.

Datum tabell med en sammanhängande datum uppsättning

Datumtabell med sammanhängande datum

Om du rapporterar ett räkenskapsår kan du skapa en datum tabell med en sammanhängande datum uppsättning för varje räkenskapsår. Om ditt räkenskapsår börjar med 1 mars och du har data för räkenskapsår 2010 upp till det aktuella datumet (till exempel i RÄKENSKAPSÅR) kan du skapa en datum tabell som börjar på 3/1/2009 och inkluderar minst varje dag för varje räkenskapsår fram till det sista datumet under räkenskapsår 2013.

Om du ska rapportera både för kalender år och räkenskapsår behöver du inte skapa separata datum tabeller. En tabell med ett datum kan innehålla kolumner för ett kalender år, räkenskapsår och till och med en kalender för 13 4 veckor. Det viktiga är att din datum tabell innehåller en sammanhängande uppsättning datum för alla år.

Lägga till en datum tabell i data modellen

Det finns flera sätt att lägga till en datum tabell i data modellen:

  • Importera från en Relations databas eller annan data källa.

  • Skapa en datum tabell i Excel och kopiera eller länka till en ny tabell i Power Pivot.

  • Importera från Microsoft Azure Marketplace.

Låt oss titta på de här mer noggrant.

Importera från en Relations databas

Om du importerar vissa eller alla dina data från ett data lager eller någon annan typ av Relations databas är det troligt att det redan finns en datum tabell och relationer mellan det och resten av data som du importerar. Datum och format stämmer antagligen överens med datumen i dina fakta uppgifter och datumen är antagligen snart klart och förr. Den datum tabell som du vill importera kan vara väldigt stor och innehålla ett datum intervall utöver vad du behöver ta med i din data modell. Du kan använda de avancerade filter funktionerna i Power Pivot-konfigurationsguiden för att selektivt välja endast datum och särskilda kolumner som du verkligen behöver. Det här kan minska arbets bokens storlek och förbättra prestandan avsevärt.

Guiden Importera tabell

Dialogruta i guiden Importera tabell

I de flesta fall behöver du inte skapa ytterligare kolumner som räkenskapsår, vecka, månads namn etc. eftersom de redan kommer att finnas i den importerade tabellen. I vissa fall kan du behöva skapa ytterligare datum kolumner efter att du har importerat datum tabellen till data modellen, beroende på ett visst rapporterings behov. Som tur är kan det vara lätt att göra med DAX. Du får mer information om hur du skapar tabell fält senare. Alla miljöer är olika. Om du är osäker på om data källorna har ett relaterat datum eller en kalender tabell kan du kontakta databas administratören.

Skapa en datum tabell i Excel

Du kan skapa en datum tabell i Excel och sedan kopiera den till en ny tabell i data modellen. Det är väldigt enkelt och ger dig mycket flexibilitet.

När du skapar en datum tabell i Excel börjar du med en enda kolumn med ett sammanhängande datum intervall. Du kan sedan skapa fler kolumner, till exempel år, kvartal, månad, räkenskapsår, period etc. i Excel-kalkylbladet genom att använda Excel-formler eller efter att du kopierar tabellen till data modellen kan du skapa dem som beräknade kolumner. Om du skapar ytterligare datum kolumner i Power Pivot beskrivs i avsnittet lägga till kolumner för nya datum i tabellen datum i den här artikeln.

Så här skapar du en datum tabell i Excel och kopierar den till data modellen

  1. I ett tomt kalkyl blad i Excel skriver du ett kolumn rubrik namn i cell a1för att identifiera ett datum intervall. Vanligt vis ärdetta något som datum, datum/tid eller datum nyckel.

  2. Skriv ett start datum i cell a2. Till exempel 1/1/2010.

  3. Klicka på fyllnings handtaget och dra det till ett rad nummer som innehåller ett slutdatum. Till exempel 12/31/2016.

    Datumkolumn i Excel

  4. Markera alla rader i kolumnen datum (inklusive rubrik namnet i cell a1).

  5. Klicka på Formatera som tabelli gruppen format och välj sedan ett format.

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

    Datumkolumn i Power Pivot

  7. Kopiera alla rader, inklusive sidhuvudet.

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

  9. I Förhandsgranska _GT_ tabell namn skriver du ett namn som datum eller kalender. Låt Använd den första raden som kolumn rubrikermarkerad och klicka sedan på OK.

    Förhandsgranska inklistring

    Tabellen ny datum (den namngivna kalendern i det här exemplet) i Power Pivot ser ut så här:

    Datumtabell i Power Pivot

    Obs!: Du kan också skapa en länkad tabell med hjälp av Lägg till i data modell. Detta gör att arbets boken blir onödigt stor eftersom arbets boken har två versioner av datum tabellen. en i Excel och en i Power Pivot..

Obs!: Namn datum är ett nyckelord i Power Pivot. Om du namnger den tabell som du skapar i Power Pivot-datum måste du omge tabell namnet med enkla citat tecken i alla DAX-formler som refererar till dem i ett argument. Alla exempel bilder och formler i den här artikeln refererar till en datum tabell som skapats i Power Pivot med namnet kalender.

Du har nu en datum tabell i data modellen. Du kan lägga till nya datum kolumner, till exempel år, månad o.s.v., med hjälp av DAX.

Lägga till nya datum kolumner i datum tabellen

En datum tabell med en enda datum kolumn som innehåller en rad för varje dag för varje år är viktig för att definiera alla datum i ett datum intervall. Det är också nödvändigt att skapa en relation mellan fakta tabellen och datum tabellen. Men den enskilda datum kolumnen med en rad för varje dag är inte användbar när du analyserar datum i en pivottabell-eller Power View-rapport. Du vill att din datum tabell ska innehålla kolumner som hjälper dig att summera data för ett område eller en grupp med datum. Du kanske till exempel vill summera försäljnings belopp per månad eller kvartal, eller så kan du skapa ett mått som beräknar årets tillväxt. I de här fallen måste du använda kolumnerna år, månad och kvartal för att summera data för den perioden i datum tabellen.

Om du har importerat datum tabellen från en Relations data källa kan den redan innehålla de olika typer av datum kolumner du vill använda. I vissa fall kanske du vill ändra några av de kolumnerna eller skapa ytterligare datum kolumner. Det är speciellt sant om du skapar en egen datum tabell i Excel och kopierar den till data modellen. Att skapa nya datum kolumner i Power Pivot är ganska enkelt med datum-och tids funktioner i DAX.

Tips: Om du inte ännu har arbetat med DAX kan du börja lära dig med snabb start: Lär dig grunderna i DAX på 30 minuter på Office.com.

DAX-funktioner för datum och tid

Om du har arbetat med datum-och tids funktioner i Excel-formler är det troligt att du är bekant med datum-och tids funktionerna. De här funktionerna liknar deras motsvarigheter i Excel, men det finns några viktiga skillnader:

  • DAX-funktioner för datum och tid använder data typen datetime.

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

  • De kan användas för att returnera och/eller hantera datum värden.

De här funktionerna används ofta när du skapar anpassade datum kolumner i en datum tabell, så att de är viktiga för att förstå. Vi använder ett antal funktioner för att skapa kolumner för år, kvartal, FiscalMonth och så vidare.

Obs!: Datum-och tids funktioner i DAX är inte samma sak som Värdejämföring. Läs mer om tidsinformation i Power Pivot i Excel 2013.

DAX inkluderar följande datum-och tids funktioner:

Det finns många andra DAX-funktioner som du kan använda i formler. Många av de formler som beskrivs här använder till exempel matematiska och trigonometriska funktioner som mod och AVKORTA, logiska funktioner som om-och text funktioner som format för att få mer information om andra DAX-funktioner finns i avsnittet ytterligare resurser längre ned i den här artikeln.

Formel exempel för ett kalender år

Följande exempel beskriver formler som används för att skapa ytterligare kolumner i en datum tabell med namnet kalender. En kolumn, med namnet date, finns redan och innehåller ett sammanhängande intervall med datum från 1/1/2010 till 12/31/2016.

År

= YEAR ([Date])

I den här formeln returnerar funktionen år värdet årtalet i kolumnen datum. Eftersom värdet i kolumnen datum är av data typen Datum/tid vet du hur man returnerar året från det.

Kolumnen År

Månader

= MÅNAD ([Date])

I den här formeln är det bra att använda funktionen månad för att returnera ett månads värde från kolumnen datum.

Kolumnen Månad

Kvartal

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

I den här formeln använder vi funktionen heltal för att returnera ett heltal. Argumentet som du anger för funktionen heltal är värdet från kolumnen månad, Lägg till 2 och dividera sedan värdet med 3 för att få vårt kvartal, 1 till 4.

Kolumnen Kvartal

Månadens namn

= FORMAT ([Date], "mmmm")

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

Kolumnen Månad Namn

Om vi vill returnera månads namnet för kortas till tre bokstäver skulle vi använda "MMM" i argumentet format.

Veckodag

= FORMAT ([Date], "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 dag namn anger vi "ddd" i argumentet format.

Kolumnen Veckodag
Exempel på pivottabell

När du har fält för datum som år, kvartal, månad etc. kan du använda dem i en pivottabell eller rapport. Följande bild visar till exempel fältet försäljnings belopp från tabellen försäljnings fakta i värden och år och kvartal från tabellen kalender dimension i rader. Försäljnings belopp är sammanslaget för årets och kvartals sammanhang.

Exempel på pivottabell

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

Räkenskapsår

= OM ([month] < = 6; [Year]; [Year] + 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 datum värde eftersom start-och slutdatumen för ett räkenskapsår ofta skiljer sig från de som gäller för ett kalender år. För att få räkenskapsåret kan vi först använda funktionen om för att testa om värdet för month är mindre än eller lika med 6. I det andra argumentet, om värdet för månad är mindre än eller lika med 6, returnerar du värdet från kolumnen Year. Annars returnerar du värdet från år och lägger till 1.

Kolumnen Räkenskapsår

Ett annat sätt att ange ett slutmånads värde för räkenskapsåret är att skapa ett mått som bara anger månaden. Till exempel FYE: = 6. Du kan sedan referera till Mät namnet i stället för månads numret. Till exempel = om ([månad] < = [FYE]; [Year]; [Year] + 1). Då får du mer flexibilitet när du refererar till räkenskapsårets slut månad i flera olika formler.

Räkenskaps må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 och tar 6 och lägger till värdet från månad, annars subtraherar 6 från värdet från [månad].

Kolumnen Räkenskapsmånad

Räkenskaps kvartal

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

Formeln som används för FiscalQuarter är mycket likadan som den hade för kvartal under vårt kalender år. Den enda skillnaden är att [FiscalMonth] anges i stället för [månad].

Kolumnen Räkenskapskvartal

Helgdagar eller särskilda datum

Du kanske vill lägga till en datum kolumn som visar att vissa datum är helgdagar eller något annat speciellt datum. Du kanske till exempel vill summera total summorna för nya år i dag genom att lägga till ett semester fält i en pivottabell, som ett utsnitt eller ett filter. I andra fall kanske du vill utesluta dessa datum från andra datum kolumner eller i ett mått.

Det är ganska enkelt att ta med 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 data modell för att lägga till den i data modellen 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 för att returnera värden.

Nedan följer ett exempel på en tabell som skapats i Excel och som innehåller helgdagar som ska läggas till i tabellen datum:

Datum

Helgdag

1/1/2010

Nya år

11/25/2010

Dag

12/25/2010

Gran

2011-01-01

Nya år

11/24/2011

Dag

12/25/2011

Gran

1/1/2012

Nya år

2012-11-22

Dag

12/25/2012

Gran

1/1/2013

Nya år

11/28/2013

Dag

12/25/2013

Gran

11/27/2014

Dag

12/25/2014

Gran

2014-01-01

Nya år

11/27/2014

Dag

12/25/2014

Gran

1/1/2015

Nya år

11/26/2014

Dag

12/25/2015

Gran

2016-01-01

Nya år

11/24/2016

Dag

12/25/2016

Gran

I datum tabellen skapar vi en kolumn med namnet semester och använder en formel som den här:

= Sökvärde (helgdagar [semester]; semestrar [datum], kalender [datum])

Låt oss titta på den här formeln mer noggrant.

Vi använder funktionen för att hämta värden från kolumnen semester i tabellen helgdagar. I det första argumentet anger vi kolumnen där resultatet blir. Kolumnen semester i tabellen semestrar anges eftersom det är det värde som vi vill returnera.

= Sökvärde (helgdagar [semester]; semestrar [datum], kalender [datum])

Därefter anger vi det andra argumentet, den Sök kolumn som har de datum som vi vill söka efter. Du anger kolumnen datum i tabellen helgdagar , så här:

= Sökvärde (helgdagar [semester];semestrar [datum], kalender [datum])

Slutligen anger vi kolumnen i kalendern som innehåller de datum som vi vill söka efter i tabellen helgdag . Det här är kursens datum kolumn i tabellen kalender .

= Sökvärde (helgdagar [semester]; semestrar [datum],Kalender [datum])

I kolumnen semester returneras jul namnet för varje rad som har ett datum värde som motsvarar ett datum i tabellen helgdagar.

Tabellen Helgdagar

Anpassad kalender – 13 4-vecko perioder

Vissa organisationer, till exempel detalj handel eller mat tjänst, rapporterar ofta till olika perioder, till exempel 13 4-vecko perioder. Med en period kalender för 13 4 vecka är varje period 28 dagar. Därför innehåller varje period fyra måndagar, fyra tisdagar, fyra Wednesdays och så vidare. Varje period innehåller samma antal dagar, och vanligt vis kommer semestern att falla under samma period varje år. Du kan välja att påbörja en period på vilken dag som helst. 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 hela perioden med räkenskapsårets första söndag. I det här fallet börjar räkenskapsåret på 7/1.

Vecka

Det här värdet anger vecko numret från den första hela veckan i räkenskapsåret. I det här exemplet börjar den första hela veckan på söndag, så den första hela veckan i det första räkenskapsåret i kalender tabellen börjar i så fall med 7/4/2010 och fortsätter till den senaste hela veckan i tabellen kalender. Det här värdet är inte allt som är användbart i analyser, men det är nödvändigt att beräkna för användning i andra 28 dagar med period formler.

= INT ([Date]-40356)/7)

Låt oss titta på den här formeln mer noggrant.

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

= INT ([Date])

Vi vill sedan leta efter den första söndagen under det första räkenskapsåret. Vi ser att det är 7/4/2010.

Kolumnen Vecka

Subtrahera nu 40356 (vilket är heltalet för 6/27/2010, sista söndagen från det föregående räkenskapsåret) från det värdet för att få antalet dagar sedan början av dagar i kalender tabellen, till exempel:

= HELTAL ([Date]-40356)

Dividera sedan resultatet med 7 (dagar i veckan), så här:

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

Resultatet ser ut så här:

Kolumnen Vecka

Period

Perioden i den här anpassade kalendern innehåller 28 dagar och den kommer alltid att börja på en söndag. Den här kolumnen Returnerar numret för den period som inleds med den första söndagen under det första räkenskapsåret.

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

Låt oss titta på den här formeln mer noggrant.

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

=Heltal ([vecka])

Addera sedan 3 till det 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:

Kolumnen Period

Räkenskapsåret period

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

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

Låt oss titta på den här formeln mer noggrant.

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

= ([Period] + 12)

Vi delar resultatet med 13 eftersom det finns 13 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åktal och returnera ett heltal när det divideras med 13, så här:

=Heltal(([period] + 12)/13)+2010

Resultatet ser ut så här:

Kolumnen Räkenskapsår för period

Period i räkenskapsåret

Det här värdet returnerar period siffran, 1 – 13, från första hela perioden (med början på söndag) för varje räkenskapsår.

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

Den här formeln är lite mer komplicerad och beskriver den först på ett språk som vi förstår bättre. Den här formeln anger du värdet från [period] av 13 för att få ett period nummer (1-13) på året. Om det numret är 0, returnerar 13.

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

=Mod ([period]; 13)

För det mesta ger vi det resultat du 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årt exempel kalender datum tabell. Vi kan åtgärda detta med en om-funktion. Om resultatet är 0 returnerar vi 13, så här:

=Om(mod ([period]; 13); mod ([period]; 13); 13)

Resultatet ser ut så här:

Period i räkenskapsåret

Exempel på pivottabell

Bilden nedan visar en pivottabell med fältet försäljnings belopp från tabellen försäljnings fakta i fälten värden och PeriodFiscalYear och PeriodInFiscalYear from tabellen kalender datum dimension i rader. Försäljnings belopp är sammankopplad efter räkenskapsår och 28 dagars period under räkenskapsåret.

Exempel på pivottabell för räkenskapsår

Relationer

När du har skapat en datum tabell i data modellen, för att börja söka i data i pivottabeller och rapporter, och för att samla data baserat på kolumnerna i tabellen datum dimension, måste du skapa en relation mellan fakta tabellen med dina transaktions data och datum tabellen.

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 data typen Datum/tid.

För varje datum värde i fakta tabellen måste den relaterade uppslags kolumnen i datum tabellen innehålla matchande värden. En rad (transaktions post) i tabellen försäljnings fakta med värdet 8/15/2012 12:00 AM i kolumnen datum nyckel måste ha ett motsvarande värde i kolumnen relaterat datum i tabellen datum (namngiven kalender). Det här är en av de viktigaste anledningarna till att din datum kolumn i datum tabellen ska innehålla ett sammanhängande datum intervall som innehåller ett möjligt datum i fakta tabellen.

Relationer i diagramvyn

Obs!: Datum kolumnen i varje tabell måste vara av samma datatyp (datum), och format för varje kolumn spelar ingen roll.

Obs!: Om Power Pivot inte tillåter att du skapar relationer mellan de två tabellerna kan datum-och tids fälten inte lagra datum och tid på samma nivå. Beroende på kolumnens formatering kan värdena se likadant ut men lagras annorlunda. Läs mer om att arbeta med tid.

Obs!: Undvik att använda heltals surrogat nycklar i relationer. När du importerar data från en Relations data källa representeras ofta datum-och tids kolumner av ett surrogat tecken, vilket är en heltals kolumn som används för att representera ett unikt datum. I Power Pivot bör du undvika att skapa relationer genom att använda heltal för datum/tid och i stället använda kolumner som innehåller unika värden med data typen date. Även om användning av surrogat nycklar är en lämplig metod i traditionella data lager behöver inte heltals nycklar i Power pivot och kan göra det svårt att gruppera värden i pivottabeller med olika datum perioder.

Om du får ett typ matchnings fel när du försöker skapa en relation är det troligt att kolumnen i fakta tabellen inte är av data typen datum. Det här kan inträffa när Power Pivot inte automatiskt kan konvertera ett icke-datum (vanligt vis en text data typ) till en datum data typ. Du kan fortfarande använda kolumnen i din fakta tabell, men du måste konvertera data med en DAX-formel i en ny beräknad kolumn. Se konvertera text data typs datum till en datum data typ längre ned i tillägget.

Flera relationer

I vissa fall kan det vara nödvändigt att skapa flera relationer eller skapa flera datum tabeller. Om det till exempel finns flera datum fält i tabellen försäljning, till exempel datum nyckel, leverans datum och ReturnDate, kan de alla ha relationer till datum fältet i tabellen kalender datum, men bara ett av dem kan vara en aktiv relation. I det här fallet, eftersom datum nyckel representerar datumet för transaktionen, och därför att det viktigaste datumet är det som är den aktiva relationen. De andra har inaktiva relationer.

I följande pivottabell beräknas total försäljning per räkenskapsår och räkenskaps kvartal. Ett mätbart resultat med total försäljning, med formeln Total Sales: = Summa ([försäljnings belopp]), placeras i värden och räkenskapsåret och FiscalQuarter från tabellen kalender datum placeras i rader.

Pivottabell med total försäljning per räkenskapskvartal Fältlista för pivottabell

Den här pivottabellen i rak arbete fungerar korrekt eftersom vi vill summera vår totala försäljning med transaktionsdatumet i datum nyckel. I vårt totala försäljnings mått används datumen i datum nyckel och beräknas av räkenskapsåret och räkenskaps kvartalet eftersom det finns en relation mellan datum nyckel i tabellen försäljning och kolumnen datum i tabellen kalender datum.

InAktiva relationer

Men vad händer om vi vill summera vår totala försäljning till och med transaktions datum men per leverans datum? Vi behöver en relation mellan kolumnen leverans datum i tabellen försäljning och kolumnen datum i tabellen kalender. Om vi inte skapar den relationen baseras våra agg regeringar alltid på transaktions datumet. Vi kan dock ha flera relationer, även om bara en kan vara aktiv, och eftersom transaktions datumet är det viktigaste är det den aktiva relationen med tabellen kalender.

I det här fallet har leverans datum ett inaktivt förhållande, så alla mått formler som skapas för att samla in data baserat på speditions datum måste ange den inaktiva relationen med funktionen USERELATIONSHIP .

Eftersom det finns inaktiva relationer mellan kolumnen leverans datum i tabellen försäljning och kolumnen datum i tabellen kalender kan vi skapa ett mått som summerar total försäljning per leverans datum. Vi använder en formel som den här för att ange vilken relation som ska användas:

Total försäljning per frakt datum: = beräkna (summa (försäljning [försäljnings belopp]), USERELATIONSHIP (försäljning [leverans datum], kalender [Date]))

Den här formeln visar bara: beräkna en summa för försäljnings belopp, men filtrerar med hjälp av relationen mellan kolumnen leverans datum i tabellen försäljning och kolumnen datum i tabellen kalender.

Om vi till exempel skapar en pivottabell och anger Total Sales per transport datum mått i värden och räkenskapsår och räkenskaps kvartal på rader ser vi samma total summa, men alla andra belopp för räkenskapsår och räkenskaps kvartal är olika eftersom de baseras på transport datum och inte transaktions datum.

Pivottabell med Total försäljning per leveransdatum Fältlista för pivottabell

Om du använder inaktiva relationer kan du bara använda en datum tabell, men det kräver att alla mått (som total försäljning per leverans datum) refererar till den inaktiva relationen i dess formel. Det finns ett annat alternativ, det vill säga Använd flera datum tabeller.

Flera datum tabeller

Ett annat sätt att arbeta med flera datum kolumner i fakta tabellen är att skapa flera datum tabeller och skapa separata aktiva relationer mellan dem. Låt oss titta på vårt exempel på försäljnings tabellen. Vi har tre kolumner med datum som vi kan behöva aggregera data på:

  • En datum nyckel med datumet för försäljning för varje transaktion.

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

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

Kom ihåg att fältet datum nyckel med transaktions datumet är viktigast. Vi kommer att göra de flesta av våra agg regeringar baserade på dessa datum, så vi vill verkligen att det ska vara en relation mellan den och kolumnen datum i tabellen kalender. Om vi inte vill skapa inaktiva relationer mellan leverans datum och ReturnDate och datum fältet i tabellen kalender, och därför behöver särskilda mått formler, kan vi skapa ytterligare datum tabeller för leverans datum och retur datum. Nu kan vi skapa aktiva relationer mellan dem.

Relationer med flera datumtabeller i diagramvyn

I det här exemplet har vi skapat en ny datum tabell med namnet ShipCalendar. Det innebär naturligtvis också att du skapar ytterligare datum kolumner och att de här datum kolumnerna finns i en annan datum tabell, så att de kan ge dem en åtskillnad mellan kolumnerna i tabellen kalender. Vi har till exempel skapat kolumnerna ShipYear, ShipMonth, ShipQuarter och så vidare.

Om vi skapar en pivottabell och sätter in vårt totalt försäljnings mått 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 en särskild total försäljning per transport datum beräknat fält.

Pivottabell med Total försäljning per leveransdatum med leveranskalender Fältlista för pivottabellen

Var och en av de här metoderna kräver noga övervägande. Om du använder flera relationer med en enda datum tabell kan du behöva skapa särskilda mått som inaktiva relationer med hjälp av funktionen USERELATIONSHIP. Å andra sidan kan det vara förvirrande att skapa flera datum tabeller i en fält lista och eftersom du har fler tabeller i data modellen krävs mer minne. Experimentera med vad som passar bäst för dig.

Egenskapen datum tabell

Egenskapen datum tabell anger metadata som behövs för att tids-intelligens-funktioner som TOTALYTD, PREVIOUSMONTH, och DATESBETWEEN ska fungera korrekt. När en beräkning körs med hjälp av någon av de här funktionerna vet PowerPivot-formel motorn var du kan gå för att få de datum som behövs.

Varning!: Om den här egenskapen inte är angiven kanske måtten för DAX Time-Intelligence-funktioner inte returnerar rätt resultat.

När du ställer in egenskapen datum tabell anger du en datum tabell och en datum kolumn för data typen Datum (datetime).

Dialogrutan Markera som datumtabell

Så här ställer du in egenskapen datum tabell

  1. I PowerPivot-fönstret väljer du tabellen kalender .

  2. Klicka på Markera som datum tabellpå fliken design .

  3. I dialog rutan Markera som datum tabell väljer du en kolumn med unika värden och data typen date.

Arbeta med tid

Alla datum värden med data typen datum i Excel eller SQL Server är egentligen ett tal. I det numret anges siffror som refererar till en tid. I många fall är den tiden för varje rad midnatt. Om till exempel ett fältet datum tid nyckel i en försäljnings fakta tabell innehåller värden som 10/19/2010 12:00:00 AM innebär detta att värdena ligger i precisions nivån. Om värdena i fältet datum tid nyckel har en tid som är inkluderad, till exempel 10/19/2010 8:44:00 AM, innebär det att värdena ligger på minut nivån för precision. Värden kan också vara till precision för Tim nivå eller till och med-sekunders nivå. Precisions nivån i tids värden påverkar hur du skapar en datum tabell och relationerna mellan dem och fakta tabellen.

Du måste avgöra om du ska samla in dina data till en dags nivå eller till en tids nivå. Med andra ord kanske du vill använda kolumner i din datum tabell som morgon, eftermiddag eller timme som datum i en pivottabells rad-, kolumn-eller filter område.

Obs!: Dagar är den minsta tidsenhet som funktioner för DAX-tidsInformation kan fungera med. Om du inte behöver arbeta med tids värden bör du minska precisionen för dina data för att använda dagar som minimilängd.

Om du tänker samla in data till tids nivån måste din datum tabell ha en datum kolumn med tiden inkluderad. Faktum är att det krävs en datum kolumn med en rad för varje timme, eller till och med varje minut, varje dag för varje år i datum intervallet. Det beror på att om du vill skapa en relation mellan kolumnen datum tid nyckel i fakta tabellen och datum kolumnen i datum tabellen måste du ha matchande värden. Om du tar med många år kan det vara en bra tabell med ett stort datum.

I de flesta fall vill du bara samla in dina data för dagen. Med andra ord använder du kolumner som år, månad, vecka eller veckodag som fält i en pivottabells rad-, kolumn-eller filter område. I det här fallet behöver kolumnen datum i datum tabellen bara en rad för varje dag på ett år, enligt beskrivningen ovan.

Om din datum kolumn innehåller tids nivån för precision, men du bara ska aggregera en dag, för att skapa relationen mellan fakta tabellen och datum tabellen, kanske du måste ändra fakta tabellen genom att skapa en ny kolumn som trunkerar värdena i datum c olumn till ett dags värde. Med andra ord konverterar du 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 här nya kolumnen och datum kolumnen i datum tabellen eftersom värdena matchar.

Låt oss titta på ett exempel. Den här bilden visar en datum tid nyckel-kolumn i tabellen Order. Alla agg regeringar för informationen i den här tabellen behöver bara vara till dags nivå, genom att använda kolumner i tabellen kalender datum som år, månad, kvartal etc. Tiden som ingår i värdet är inte relevant, bara det faktiska datumet.

Datumtidnyckel-kolumn

Eftersom vi inte behöver analysera dessa data på tids nivån behöver vi inte datum kolumnen i tabellen kalender datum för att lägga till en rad för varje timme och varje minut varje dag. Så här ser kolumnen datum i tabellen date ut följande:

Datumkolumn i Power Pivot

Om du vill skapa en relation mellan kolumnen datum tid nyckel i tabellen försäljning och kolumnen datum i tabellen kalender kan du skapa en ny beräknad kolumn i tabellen försäljnings faktum och sedan använda funktionen AVKORTA för att trunkera datum-och tids värden i datum tid nyckel kolumn till ett datum värde som matchar värdena i kolumnen datum i tabellen kalender. Vår formel ser ut så här:

= AVKORTA ([datum tid nyckel]; 0)

Då får vi en ny kolumn (med namnet datum nyckel) med datumet från kolumnen datum tid nyckel och en tid på 12:00:00 AM för varje rad:

Kolumnen Datumnyckel

Nu kan vi skapa en relation mellan den här nya (datum nyckel) kolumnen och datum kolumnen i tabellen kalender.

På samma sätt kan vi skapa en beräknad kolumn i tabellen försäljning som minskar tids precisionen i kolumnen datum tid nyckel till timkostnad. I det här fallet fungerar inte AVKORTA-funktionen, men vi kan fortfarande använda andra DAX-datum och tids funktioner för att extrahera och återigen sammanfoga ett nytt värde till en Tim nivå av precision. Vi kan använda en formel så här:

= DATUM (år ([datum tid nyckel]); månad ([datum tid nyckel]); dag ([datum tid nyckel])) + TIME (timme ([datum tid nyckel]); 0; 0)

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

Datumtidnyckel-kolumn

Förutsatt att vår datum kolumn i datum tabellen har värden för precisions nivån timme kan vi skapa en relation mellan dem.

Göra datum mer användbara

Många av datum kolumnerna som du skapar i datum tabellen är nödvändiga för andra fält, men egentligen är det inte allt som är användbart för analys. Fältet datum nyckel i tabellen försäljning som vi hänvisar till och som visas i den här artikeln är viktigt eftersom den transaktionen registreras som ett visst datum och en viss tidpunkt för varje transaktion. Men från en analys och rapporterings punkt är det inte allt som är användbart eftersom det inte går att använda det som ett rad-, kolumn-eller filter fält i en pivottabell eller rapport.

I vårt exempel är datum kolumnen i tabellen kalender väldigt användbar, men du kan inte använda den som en dimension i en pivottabell.

Om du vill hålla tabellerna och kolumnerna i dem så användbara som möjligt och för att göra det enklare att navigera i en lista över rapport fält i pivottabell eller Power View är det viktigt att dölja onödiga kolumner från klient verktyg. Du kanske också vill dölja vissa tabeller. Tabellen helgdagar som visas ovan innehåller helg datum som är viktiga för vissa kolumner i tabellen kalender, men du kan inte använda kolumnerna datum och semester i tabellen helgdagar som fält i en pivottabell. Här för att göra fält listor lättare att navigera i kan du dölja hela helgdags tabellen.

En annan viktig aspekt med att arbeta med datum är att namnge regler. Du kan namnge tabeller och kolumner i Power Pivot när du vill. Men kom ihåg, särskilt om du delar din arbets bok med andra användare blir det enklare att identifiera tabeller och datum, inte bara i fält listor, utan även i Power pivot och i DAX-formler.

När du har en datum tabell i data modellen kan du börja skapa mått som hjälper dig att få ut mesta möjliga av dina data. Vissa kan vara så enkelt som att summera försäljnings summor för det aktuella året, och andra kan vara mer komplexa, där du måste filtrera på ett visst intervall med unika datum. Läs mer i mått i Power Pivot -och Time Intelligence-funktioner.

Bilaga

Konvertera text data typs datum till data typen Datum

I vissa fall kan en fakta tabell med transaktions data innehålla datum med data typen text. Det vill säga ett datum som visas som 2012-12-04T11:47:09 är faktiskt inte ett datum alls, eller så är åtminstone inte den typ av datum Power Pivot som kan förstås. Det är bara text som läses som ett datum. För att skapa en relation mellan en datum kolumn i fakta tabellen och en datum kolumn i en datum tabell måste båda kolumnerna ha data typen date .

När du försöker ändra data typen för en kolumn med datum som är text data typ till en datum data typ kan Power Pivot tolka datumen och omvandla den till data typen sant datum automatiskt. Om Power Pivot inte kan göra en data typs konvertering får du ett typ matchnings fel.

Du kan dock fortfarande konvertera datumen till data typen sant datum. Du kan skapa en ny beräknad kolumn och använda en DAX-formel för att analysera år, månad, dag, tid, etc. från text strängarna och sedan sammanfoga den tillsammans med en Power Pivot-funktion som ett sant datum.

I det här exemplet har vi importerat en fakta tabell med namnet försäljning till Power Pivot. Den innehåller en kolumn med namnet DateTime. Värdena ser ut så här:

Kolumnen Datumtid i en faktatabell.

Om vi tittar på data typen på fliken Start i gruppen grupp Power Pivot ser vi att det är text data typen.

Datatyp i menyflik

Det går inte att skapa en relation mellan kolumnen DateTime och kolumnen date i en datum tabell eftersom data typerna inte stämmer överens. Om vi försöker ändra data typen till datumfår du ett typ matchnings fel:

Matchningsfel

I det här fallet gick det inte att konvertera data typen från text till datum i Power Pivot. Vi kan fortfarande använda den här kolumnen, men för att få den i data typen sant datum måste vi skapa en ny kolumn som tolkar texten och återskapar den i ett värde Power Pivot kan göra data typen datum.

Kom ihåg att du kommer till avsnittet arbeta med tid tidigare i den här artikeln. såvida det inte är nödvändigt att använda en tids period på alla dagar bör du konvertera datum i din fakta tabell till en dags nivå. Med det här vill vi att värdena i vår nya kolumn ska vara på dags nivå (exklusive tid). Vi kan båda omvandla värdena i kolumnen DateTime till data typen Datum och ta bort tids nivån för precision med följande formel:

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

Då får vi en ny kolumn (i det här fallet namnet). Power Pivot identifierar också värdena som datum och anger data typen automatiskt till datum.

Kolumnen Datum i en faktatabell

Om vi vill bevara tids nivån för precisionen får vi bara en formel som innehåller antalet timmar, minuter och sekunder.

= DATE (LEFT ([DateTime]; 4); mitt ([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 datum kolumn med data typen datum kan vi skapa en relation mellan den och en datum kolumn i ett datum.

Ytterligare resurser

Datum i PowerPivot

Beräkningar i Power Pivot

Snabbstart: Grunderna i DAX på 30 minuter

Referens för data analys uttryck

DAX Resource Center

Utöka dina Office-kunskaper
Utforska utbildning
Få nya funktioner först
Anslut till Office Insiders

Hade du nytta av den här informationen?

Tack för din feedback!

Tack för din feedback! Det låter som att det kan vara bra att koppla dig till en av våra Office-supportrepresentanter.

×