Power BI Calendar Tables

It is a best practice to have a calendar table in Power BI models, it facilitates calculations relying on time-intelligence.

When using a database or SSAS as the data source, the calendar table should be part of the underlying model already. But when using folders/files as data sources, it is necessary to build it within Power BI.

There are two ways to add this calendar table to your model:

• Power query M custom query
• DAX table

The Power Query solution is recommended as it is slightly more efficient and less cumbersome to use.

There are 2 steps:

1. Write a M query to get the Date column with the required values
2. Insert additional columns using Power BI UI (year, month, week, month names…)

For fixed start date, or set with a parameter:

let
    Source = List.Dates(StartDate, Length, #duration(1, 0, 0, 0)),
    StartDate = #date(2016, 1, 1),
    Today = DateTime.Date(DateTime.LocalNow()),
    Length = Duration.Days(Today - StartDate)+1,
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns - Date" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type - Date" = Table.TransformColumnTypes(#"Renamed Columns - Date",{{"Date", type date}})
in
    #"Changed Type - Date"

The start date can also be extracted from another query (taking the min date):

let
    Source = List.Dates(StartDate, Length, #duration(1, 0, 0, 0)),
    StartDate = DateTime.Date(List.Min(OtherQuery[DateColumn])),
    Today = DateTime.Date(DateTime.LocalNow()),
    Length = Duration.Days(Today - StartDate)+1,
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns - Date" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type - Date" = Table.TransformColumnTypes(#"Renamed Columns - Date",{{"Date", type date}})
in
    #"Changed Type - Date"

In Power Query editor, tab “Add column”, there is a “Date” menu with presets for adding the year, date at start of year, month, month name…

In the “Data” section of Power BI, create a new table with the following DAX expression:

Date = 
ADDCOLUMNS (
  CALENDAR (DATE(2016;1;1); MAX(FactTable[Date]));
  "DateAsInteger"; FORMAT ( [Date]; "YYYYMMDD" );
  "Year"; YEAR ( [Date] );
  "Monthnumber"; FORMAT ( [Date]; "MM" );
  "YearMonthnumber"; FORMAT ( [Date]; "YYYY/MM" );
  "YearMonthShort"; FORMAT ( [Date]; "YYYY/mmm" );
  "MonthNameShort"; FORMAT ( [Date]; "mmm" );
  "MonthNameLong"; FORMAT ( [Date]; "mmmm" );
  "DayOfWeekNumber"; WEEKDAY ( [Date] );
  "DayOfWeek"; FORMAT ( [Date]; "dddd" );
  "DayOfWeekShort"; FORMAT ( [Date]; "ddd" );
  "Quarter"; "Q" & FORMAT ( [Date]; "Q" );
  "YearQuarter"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" )
)