How To Create a Calendar Table in Power BI

How To Create a Calendar Table in Power BI

Calendar dimensions are necessary to enable time intelligence functions in Power BI to work. However, they are not always available from the source system you are using for the model. There have been many blog posts and articles about how to get around this issue by creating calendar dimensions using M for Power BI but the majority of them have only included calendar year information…until now. 

Adapted from the query Matt Masson ( published on his website, this script extends the query to include week and Financial year attributes. It also allows you the flexibility to select the first month of the financial year.

 View Script Here.

To use in Power BI Desktop select New Source -> Blank Query from the ribbon.


Then click 'Advanced Editor' on the ribbon and replace the existing script with the CreateDateTable script and click Done.


You will then need to enter the Start Date and End Date parameters as well as the first month of the financial year (where July is 7) and click Invoke.


Select Close & Apply and the calendar dimension will be added to your model.

Best of all, if you need to add multiple calendar dimensions you can simply invoke the function for as many as you need. 

The following fields are available in the table:

·        CalendarDate

·        CalendarYear

·        DayShort

·        DayLong

·        DayInMonth

·        MonthShort

·        MonthLong

·        MonthShortCaption

·        MonthLongCaption

·        CalendarMonthNo

·        CalendarMonth

·        CalendarQuarterNo

·        CalendarQuarter

·        CalendarQuarterShort

·        CalendarQuarterLong

·        CalendarQuarterShortCaption

·        CalendarQuarterLongCaption

·        FinancialYearNo

·        FinancialMonthNo

·        FinancialMonth

·        FinancialQuarterNo

·        FinancialQuarter

·        FinancialQuarterLongCaption

·        FinancialYear

·        FinancialYearShortCaption

·        FinancialYearLongCaption

·        DayInWeekMonday

·        DayInWeekSaturday

·        DayInWeekSunday

·        CalendarWeek

·        CalendarWeekCaption

·        CalendarWeekKey