Creating 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 (www.mattmasson.com) published, 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.

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

calendartable1

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

calendartable2

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.

calendartable3

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.

Download script here: date-table-query