Datumtabellen zijn zeer belangrijk in Power BI en Excel Power Pivot. Doormiddel van datumtabel kan je datumintelligentie functies toepassen en wordt je model eenvoudiger.
Er zijn verschillende methoden om een datumtabel te maken, zo kan je een corporate-kalender importeren, kan je een Datumtabel template maken via DAX of kan je de volgende M-code gebruiken om een datumtabel te genereren via Power Query.
Importeer de datumtabel
- Kopieer de onderstaande code:
let
StartYear = 2012,
// The number of years to be added after the current year, 0 will make the calendar stop on the last day of the current year."
YearsToAdd = 1,
LastMonthOfFiscalYear = 3,
#"Create List" = List.Dates(#date( StartYear , 1 , 1 ), Number.From(Date.AddYears( Date.EndOfYear( DateTime.LocalNow()) , YearsToAdd))- Number.From( #date( StartYear , 1 , 1 ) ) ,#duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(#"Create List", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Rename Column Date" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Set as Date Column" = Table.TransformColumnTypes(#"Rename Column Date",{{"Date", type date}}),
#"Inserted Year number" = Table.AddColumn(#"Set as Date Column", "Year (nbr)", each Date.Year([Date]), Int64.Type),
#"Insert Year" = Table.AddColumn(#"Inserted Year number", "Year", each "CY " & Number.ToText(Date.Year([Date])),Text.Type ),
#"Inserted Quarter of Year" = Table.AddColumn(#"Insert Year", "Quarter", each "Q" & Number.ToText(Date.QuarterOfYear([Date])) ,Text.Type ),
#"Insert Year- Quarter" = Table.AddColumn(#"Inserted Quarter of Year", "Year Quarter", each Number.ToText(Date.Year([Date])) & "-Q" & Number.ToText(Date.QuarterOfYear([Date])), Text.Type),
#"Inserted Month" = Table.AddColumn(#"Insert Year- Quarter", "Month (nbr)", each Date.Month([Date]), Int64.Type),
#"Insert Fis Month Number" = Table.AddColumn(#"Inserted Month", "FY Month", each if [#"Month (nbr)"] > LastMonthOfFiscalYear then [#"Month (nbr)"]-LastMonthOfFiscalYear else [#"Month (nbr)"]+12-LastMonthOfFiscalYear, Int64.Type ),
#"Insert Fis Year" = Table.AddColumn(#"Insert Fis Month Number", "Fis Year", each
if [FY Month] > (12-LastMonthOfFiscalYear)
then "FY " & Date.ToText( Date.AddYears( [Date], -1 ) , "yy") & "-" & Date.ToText([Date], "yy")
else "FY " & Date.ToText([Date], "yy") & "-" & Date.ToText( Date.AddYears( [Date], +1 ) , "yy")
, Text.Type),
#"Inserted Month Name" = Table.AddColumn(#"Insert Fis Year", "Month", each Date.MonthName([Date], "en-BE"), Text.Type),
#"Insert mmm YYYY" = Table.AddColumn(#"Inserted Month Name", "Month Year (mmm YYYY)", each Text.Upper ( Date.ToText([Date], "MMM yyyy", "en") ), Text.Type),
#"Insert mmm" = Table.AddColumn(#"Insert mmm YYYY", "Month (abbr)", each Text.Upper ( Date.ToText([Date], "MMM", "en") ), Text.Type),
#"Insert m" = Table.AddColumn(#"Insert mmm", "Month (1st L)", each Text.Start([Month], 1) & Text.Repeat( Character.FromNumber(8203), [#"Month (nbr)"]), Text.Type),
#"Insert Monthkey" = Table.AddColumn(#"Insert m", "MonthKey", each Number.FromText( Date.ToText([Date], "yyyyMM")) ,Int64.Type ),
#"Insert ISO Weeknumber" = Table.AddColumn(#"Insert Monthkey", "ISO Weeknumber", each let
CurrentThursday = Date.AddDays([Date], 3 - Date.DayOfWeek([Date], Day.Monday ) ),
YearCurrThursday = Date.Year( CurrentThursday ),
FirstThursdayOfYear = Date.AddDays(#date( YearCurrThursday,1,7),- Date.DayOfWeek(#date(YearCurrThursday,1,1), Day.Friday) ),
ISO_Week = Duration.Days( CurrentThursday - FirstThursdayOfYear) / 7 + 1
in ISO_Week , Int64.Type ),
#"Insert ISO Yearnumber" = Table.AddColumn(#"Insert ISO Weeknumber", "Year (ISO Week)", each let
CurrentThursday = Date.AddDays([Date], 3 - Date.DayOfWeek([Date], Day.Monday ) ),
YearCurrThursday = Text.From( Date.Year( CurrentThursday ))
in YearCurrThursday , Text.Type),
#"Insert ISO Week" = Table.AddColumn(#"Insert ISO Yearnumber", "ISO Week", each [#"Year (ISO Week)"] & " w" & Text.PadStart( Text.From([ISO Weeknumber]) ,2 , "0") , Text.Type),
#"Insert Year Relative nbr" = Table.AddColumn(#"Insert ISO Week", "Relative Year", each Date.Year( [Date] ) - Date.Year(DateTime.LocalNow() ) , Int64.Type),
#"Insert Quarter Relative nbr" = Table.AddColumn(#"Insert Year Relative nbr", "Relative Quarter", each 4 * Date.Year( [Date] ) + Date.QuarterOfYear( [Date] )
- 4 * Date.Year( DateTime.FixedLocalNow() ) - Date.QuarterOfYear( DateTime.FixedLocalNow() ), Int64.Type),
#"Insert Month Relative nbr" = Table.AddColumn(#"Insert Quarter Relative nbr", "Relative Month", each 12 * Date.Year( [Date] ) + Date.Month( [Date] )
- 12 * Date.Year( DateTime.FixedLocalNow() ) - Date.Month( DateTime.FixedLocalNow() ), Int64.Type),
#"Insert Week Relative nbr" = Table.AddColumn(#"Insert Month Relative nbr", "Relative Week", each (Number.From(Date.StartOfWeek([Date], Day.Monday)) - Number.From(Date.StartOfWeek( DateTime.LocalNow(), Day.Monday)))/7 , Int64.Type),
#"Insert Day Relative nbr" = Table.AddColumn(#"Insert Week Relative nbr", "Relative Day", each Number.From( [Date] ) - Number.RoundDown(Number.From( DateTime.LocalNow() )), Int64.Type),
#"Inserted Day of Month" = Table.AddColumn(#"Insert Day Relative nbr", "Day", each Date.Day([Date]), Int64.Type),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Day of Month", "Day of Week", each Date.DayOfWeek([Date] , Day.Monday )+1),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Week", "Day Name", each Date.DayOfWeekName([Date], "en-BE"), type text)
in
#"Inserted Day Name"- In Power BI kies Home > Data > Get data > Blank query
- Start de Geavanceerde editor: View > Avdanced > Advanced editor
- Verwijder de bestaande tekst en plak de gekopieerde code.
- Klik op Done
- Wijzig de naam van de query “Dates” of welke naam in jullie context wordt gebruikt.
- Geef jaar in welk jaar de kalender moet beginnen in de stap StartYear.
- In de tweede stap (YearsToAdd) geef je het aantal jaar dat de tabel moet doorgaan na het huidige jaar. 1 betekent dat het hele volgende jaar wordt toegevoegd.
- In de derde stap (LastMonthOfFiscalYear) geef je aan welke maand de laatste maand is van het fiscale jaar.
- Klik op Close and Apply om de query te laden.
Markeren als Datumtabel
Nadat de datumtabel in aangemaakt, moet deze ook worden gemarkeerd als een datumtabel.
- Ga naar de Gegevens-weergave.
- Navigeer naar de eerder aangemaakte tabel “Dates”.
- Selecteer Tables tools > Calendars > Mark as data table.
- Selecteer de “Date” kolom als “unique identifier” (Datumkolom) en klik OK.
Wijzig de sortering voor “Month name” kolom(men)
De “Month” zal alfabetisch worden gesorteerd, gebruik de volgende methode om sortering aan te passen.
- Selecteer de “Month”-kolom
- Klik Column tools> Sort > Sort by column > …
- Geef vervolgens aan welke kolom gebruikt moet worden om de “Month”-kolom te sorteren in dit geval “Month nbr”
- Herhaal deze stap voor de kolom “Month (abbr)” die eveneens gesorteerd moet worden op “Month nbr” en de kolom “Month Year (mmm YYYY)” die gesorteerd moet worden op de kolom “MonthKey”
Samenvatting uitzetten van numerieke kolommen
Numerieke kolommen worden standaard geaggregeerd (∑). Dit is in een datumtabel veelal niet juist. Herhaal onderstaande stappen voor iedere kolom:
- Selecteer de betreffende kolom
- Klik Column tools > Properties > Summarization en selecteer Don’t summarize.
De datumtabel is nu klaar voor gebruik. Connecteer deze aan alle relevante datumkolommen in het model.
