Power BI Date Table

Power BI

A date table is a great way to build slicers and filters for date fields in Power BI. Creating a relationship between a date field in one of your tables and a date table allows you to create filters and axes based on basically any date-based parameter you can think of. For example, instead of just having the date on the x-axis of a column chart, maybe you would like:

  • The year (YYYY)
  • The month and the year (MM-YYYY)
  • The financial year (FY-YY)
  • The week number of the year and the year (Week WW YYYY)
  • And so on...

Please note this code is based on the example provided by Enterprise DNA in this video, however I have made some adjustment so that is better suits my needs.

The date table is a function which takes in a start date, end date, and the month in which the financial year starts. The table can then be regenerated when required.

The "Sort" columns are just helper columns for other columns which need a helper column to be sorted (for example, the full month name + year, e.g. January 2020, February 2020, etc, would end up being sorted alphabetically, which is not what you want). This can be set for a column by going to the table view, selecting a column and setting the "Sort By Column" in the "Column Tools" tab.

let fnDateTable = (StartDate as date, EndDate as date, FYStartMonth as number) as table =>
  let
    DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
    Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
    TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),   
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
    RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
    InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])),
    InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
    InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date]), type text),
    InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
    InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
    InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM"), type text),
    InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthAndYear", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year]), type text),
    InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuaterAndYear", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year]), type text),
    InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])),
    InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd"), type text),
    InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date),
    InsertWeekNumber = Table.AddColumn(InsertWeekEnding, "Week Number", each Date.WeekOfYear([Date])),
    InsertWeekAndYear = Table.AddColumn(InsertWeekNumber, "WeekAndYear", each "Wk " & Number.ToText([Week Number]) & " " & Number.ToText([Year]), type text),
    InsertMonthYearSort = Table.AddColumn(InsertWeekAndYear,"MonthYearSort", each [Year] * 10000 + [MonthOfYear] * 100),
    InsertQuarterYearSort = Table.AddColumn(InsertMonthYearSort,"QuarterYearSort", each [Year] * 10000 + [QuarterOfYear] * 100),
    InsertWeekYearSort = Table.AddColumn(InsertQuarterYearSort,"WeekYearSort", each [Year] * 10000 + [Week Number]),
    InsertShortYear = Table.AddColumn(InsertWeekYearSort, "ShortYear", each Text.End(Text.From([Year]), 2), type text),
    InsertCalendarMonthShort = Table.AddColumn(InsertShortYear, "MonthAndYearShort", each Text.Start(Text.From([MonthName]), 3) & " " & [ShortYear], type text),
    InsertFinancialYear = Table.AddColumn(InsertCalendarMonthShort, "FY", each "FY"&(if [MonthOfYear]>=FYStartMonth then Text.From(Number.From([ShortYear])+1) else [ShortYear])),
    ChangedTypes = Table.TransformColumnTypes(InsertFinancialYear, {{"Year", Int64.Type}, {"QuarterYearSort", Int64.Type}, {"Week Number", Int64.Type}, {"MonthYearSort", Int64.Type}, {"WeekYearSort", Int64.Type}, {"DateInt", Int64.Type}, {"DayOfMonth", Int64.Type}, {"MonthOfYear", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"DayInWeek", Int64.Type}})
in
    ChangedTypes
in
    fnDateTable

Example:

Example

© 2021