Published on

Create Date Table in Microsoft Power BI

Authors

Why?

Using a custom date table in Microsoft Power BI provides more flexibility than the built-in one. For example, you can define a fiscal year that doesn’t start in January, or create helper columns (week, quarter, running month index, etc.) to simplify modeling and DAX.

Create custom date table in Microsoft Power BI

  • Go to the Model view, click Get Data
  • Choose Blank Query → Power Query Editor opens
  • Manage Parameters and add:
    • FromYear (Decimal Number), e.g. 2015
    • ToYear (Decimal Number), e.g. 2025
    • StartOfFiscalYear (Decimal Number), e.g. 1 for January or 6 for June
  • Open Advanced Editor and paste the following M code:
let
    Today=Date.From(DateTime.LocalNow()),
    firstDayofWeek=Day.Monday,
    FromDate=#date(FromYear,1,1),
    ToDate=#date(ToYear,12,31),
    Source=List.Dates(
        FromDate,
        Duration.Days(ToDate-FromDate)+1,
        #duration(1,0,0,0)
    ),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Start of Year" = Table.AddColumn(#"Inserted Year", "Start of Year", each Date.StartOfYear([Date]), type date),
    #"Inserted End of Year" = Table.AddColumn(#"Inserted Start of Year", "End of Year", each Date.EndOfYear([Date]), type date),
    #"Inserted Month" = Table.AddColumn(#"Inserted End of Year", "Month", each Date.Month([Date]), Int64.Type),
    #"Inserted Start of Month" = Table.AddColumn(#"Inserted Month", "Start of Month", each Date.StartOfMonth([Date]), type date),
    #"Inserted End of Month" = Table.AddColumn(#"Inserted Start of Month", "End of Month", each Date.EndOfMonth([Date]), type date),
    #"Inserted Days in Month" = Table.AddColumn(#"Inserted End of Month", "Days in Month", each Date.DaysInMonth([Date]), Int64.Type),
    #"Inserted Day" = Table.AddColumn(#"Inserted Days in Month", "Day", each Date.Day([Date]), Int64.Type),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day", "Day Name", each Date.DayOfWeekName([Date]), type text),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day Name", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
    #"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Week", "Day of Year", each Date.DayOfYear([Date]), Int64.Type)
    // ... continue with your own helper columns (Week, Quarter, FiscalYear, etc.)
in
    #"Inserted Day of Year"
  • Close & Apply.
  • Mark the table as Date table in Power BI and set the Date column.

That’s it — you now have a flexible date table with support for fiscal years and helpful columns.