Skapa en datumtabell
Under rapportskapandet i Power BI är ett vanligt affärskrav att göra beräkningar utifrån datum och tid. Organisationer vill veta hur det går för verksamheten över månader, kvartal, räkenskapsår och så vidare. Av den anledningen är det viktigt att dessa tidsorienterade värden formateras på rätt sätt. Power BI identifierar automatiskt för datumkolumner och tabeller, men situationer kan uppstå där du behöver vidta extra åtgärder för att få datumen i det format som organisationen kräver.
Anta till exempel att du utvecklar rapporter åt säljteamet i din organisation. Databasen innehåller tabeller för försäljningar, beställningar, produkter med mera. Du märker att många av dessa tabeller, bland annat Sales och Orders, innehåller egna datumkolumner, enligt kolumnerna ShipDate och OrderDate i Sales och Orders. Du får uppgiften att utveckla en tabell över totalförsäljningen och beställningarna per år och månad. Hur kan du skapa ett visuellt objekt med flera tabeller, där varje objekt refererar till egna datumkolumner?
För att lösa problemet kan du skapa en gemensam datatabell som kan användas av flera tabeller. I följande avsnitt förklaras hur du kan utföra uppgiften i Power BI.
Skapa en gemensam datumtabell
Olika sätt som du kan skapa en gemensam datumtabell är följande:
Källdata
DAX
Power Query
Källdata
Ibland har källdatabaser och informationslager redan egna datumtabeller. Om administratören som har utformat databasen har gjort ett grundligt jobb kan dessa tabeller användas till att utföra följande uppgifter:
Identifiera företagets semestrar
Olika kalender- och räkenskapsår
Identifiera helger kontra vardagar
Källdatatabeller är mogna och redo att användas direkt. Om du har en tabell som sådan kan du ta med den i din semantiska modell och inte använda andra metoder som beskrivs i det här avsnittet. Vi rekommenderar att du använder en källdatatabell eftersom den sannolikt delas med andra verktyg som du kanske använder utöver Power BI.
Om du inte har en källdatatabeller kan du använda andra sätt för att skapa en gemensam datumtabell.
DAX
Du kan använda DAX-funktionerna (Data Analysis Expression) CALENDARAUTO() eller CALENDAR() för att skapa den gemensamma datumtabellen. Funktionen CALENDAR() returnerar ett kontinuerligt intervall av datum utifrån ett start- och slutdatum som anges som argument i funktionen. Alternativt returnerar funktionen CALENDARAUTO() ett sammanhängande, komplett datumintervall som automatiskt bestäms från din semantiska modell. Startdatumet väljs som det tidigaste datumet som finns i din semantiska modell, och slutdatumet är det senaste datumet som finns i din semantiska modell plus data som har fyllts i till den räkenskapsmånad som du kan välja att inkludera som ett argument i funktionen CALENDARAUTO(). För det här exemplet används funktionen CALENDAR() eftersom du bara vill se data från den 31 maj 2011 (den första dagen som Sales började spåra sina data) och framåt de närmaste 10 åren.
I Power BI Desktop väljer du Ny tabell och anger sedan följande DAX-formel:
Dates = CALENDAR(DATE(2011, 5, 31), DATE(2022, 12, 31))
Nu har du kolumn med datum som du kan använda. Men den här kolumnen är något gles. Du vill även se kolumner för bara året, månadsnumret, veckan på året och veckodagen. Du kan utföra uppgiften genom att välja Ny kolumn i menyfliksområdet och ange följande DAX-ekvation, som hämtar året från datumtabellen.
Year = YEAR(Dates[Date])
Du kan utföra samma process för att hämta månadsnummer, veckonummer och veckodag:
MonthNum = MONTH(Dates[Date])
WeekNum = WEEKNUM(Dates[Date])
DayoftheWeek = FORMAT(Dates[Date], "DDDD")
När du är klar innehåller tabellen kolumnerna som visas i följande bild.
Nu har du skapat en gemensam datumtabell med DAX. Den här processen lägger bara till din nya tabell i semantikmodellen. Du måste fortfarande upprätta relationer mellan datumtabellen och tabellerna Försäljning och Order och sedan markera tabellen som den officiella datumtabellen för din semantiska modell. Men innan du slutför de uppgifterna ska du se till att överväga andra sätt att bygga en gemensam datatabell: med hjälp av Power Query.
Power Query
Du kan använda M-språket, utvecklingsspråket som används för att skapa frågor i Power Query, för att definiera en gemensam datumtabell.
Välj Transformera data i Power BI Desktop, vilket leder dig till Power Query. I det tomma utrymmet i det vänstra fönstret Frågor högerklickar du för att öppna följande nedrullningsbara meny, där du väljer Ny tom fråga>.
I den resulterande Ny fråga-vyn anger du följande M-formel för att skapa en kalendertabell:
= List.Dates(#date(2011,05,31), 365*10, #duration(1,0,0,0))
För säljdata vill du att startdatumet speglar det tidigaste datumet som finns i dina data: 31 maj 2011. Dessutom vill du se datum för de kommande 10 åren, inklusive datum i framtiden. Med den här metoden ser du till att nya säljdata flödar in – du behöver inte återskapa den här tabellen. Du kan också ändra varaktigheten. I det här fallet vill du ha en datapunkt för varje dag, men du kan också öka med timmar, minuter och sekunder. Följande bild visar resultatet.
När du har utfört processen med lyckat resultat märker du att du har en lista med datum istället för en tabell med datum. Åtgärda det här felet genom att gå till fliken Transformera i menyfliksområdet och välja Konvertera > till tabell. Som namnet antyder konverterar funktionen listan till en tabell. Du kan också byta namn på kolumnen till DateCol.
Som nästa steg ska du lägga till den nya tabellen för att se datum i år, månad, vecka och dag, så att du kan bygga en hierarki i det visuella objektet. Den första uppgiften är att ändra kolumntypen genom att välja ikonen bredvid namnet på kolumnen och, i den resulterande menyn, välja typen Datum.
När du har valt typen Date kan du lägga till kolumner för år, månader, veckor och dagar. Gå till Lägg till kolumn, välj den nedrullningsbara menyn under Datum och välj sedan År, som du ser i följande bild.
Observera att Power BI har lagt till en kolumn med alla år som hämtas från DateCol.
Slutför samma process för månader, veckor och dagar. När du är klar med processen innehåller tabellen kolumnerna som visas i följande bild.
Nu har du skapat en gemensam datumtabell med Power Query.
Föregående steg visar hur du hämtar tabellen till den semantiska modellen. Nu behöver du markera tabellen som officiell datumtabell så att Power BI kan känna igen den för alla framtida värden och se till att formateringen är korrekt.
Markera det officiella datumtabellen
Den första uppgiften i att markera tabellen som officiell datumtabell är att hitta den nya tabellen i fönstret Fält. Högerklicka på namnet på tabellen och välj sedan Markera som datumtabell, som visas i följande bild.
Genom att markera tabellen som datumtabell utför Power BI valideringar för att se till att data innehåller noll nullvärden, är unika och innehåller datumvärden över en period. Du kan också välja specifika kolumner i tabellen för att markera som datumet, vilken kan vara användbart när du har många kolumner i tabellen. Högerklicka på tabellen, välj Markera som datumtabell och välj sedan Datumtabellinställningar. Följande fönster visas, där du kan välja vilken kolumn som ska markeras som Datum.
När du väljer Markera som datumtabell tas automatiskt genererade hierarkier bort från fältet Datum i den tabell du har markerats som datumtabell. För andra datumfält finns den automatiska hierarkin fortfarande tills du upprättar en relation mellan det fältet och datumtabellen eller tills du inaktiverar funktionen Automatiskt datum/tid. Du kan lägga till en hierarki manuellt i din gemensamma datumtabell genom att högerklicka på kolumnerna år, månad, vecka eller dag i fönstret Fält och sedan välja Ny hierarki. Den här processen beskrivs ytterligare senare i den här modulen.
Skapa det visuella objektet
För att skapa set visuella objektet mellan tabellerna Sales och Orders behöver du upprätta en relation mellan den nya gemensamma datumtabellen och tabellerna Sales och Orders. Då kan du bygga visuella objekt med hjälp av den nya datumtabellen. För att slutföra uppgiften går du till fliken Modell>Hantera relationer, där du kan skapa relationer mellan den gemensamma datumtabellen och tabellerna Orders och Sales med hjälp av kolumnen OrderDate. Följande skärmbild visar ett exempel på en sådan relation.
När du har skapat relationer kan du bygga det visuella objektet Total Sales and Order Quantity by Time (Försäljning totalt och orderkvantitet per tid) med den gemensamma datumtabellen du har utvecklat med DAX- eller Power Query-metoden.
För att kunna fastställa den totala försäljningen måste lägga till all försäljning eftersom kolumnen Amount i tabellen Sales bara ser till intäkten för varje försäljning, inte de totala säljintäkterna. Du kan slutföra den här uppgiften med hjälp av följande måttberäkning, som förklaras i senare diskussioner. Beräkningen du använder när du skapar måttet är följande:
#Total Sales = SUM(Sales[‘Amount’])
När du är klar kan du skapa en tabell genom att gå tillbaka till fliken Visualiseringar och välja det visuella objektet Tabell. Du vill se beställningar och försäljning totalt per år och månad, så du vill bara inkludera kolumnerna Year och Month från datumtabellen, kolumnen OrderQty och måttet #TotalSales. När du lär dig mer om hierarkier kan du också skapa en hierarki som gör att du kan öka detaljnivån från år till månader. I det här exemplet kan du visa dem sida vid sida. Nu har du skapat ett visuellt objekt med en gemensam datumtabell.
