Creating Dynamic Calendar Table in Power BI

Calendar table is pretty useful as it can link different data sources to produce a consolidated reports or dashboards. For example, in the image below, the calendar table is linked to both transaction data and return data; therefore, both transaction and return for certain product for particular period can be seen in a visual for comparison.

Creating dynamic calendar table where the calendar can be automatically updated based on the date information in the raw data source when the "Refresh" button is clicked can save a bunch of times as the table expand over time. So you don't have to manually update the calendar table every time the date in raw data exceed your calendar table.

Here are 2 ways of how I create a dynamic calendar table in Power BI: in Power Query and DAX.

Power Query:

Import the raw data where my calendar table would follow or synchronize with.

Go to Home tab > Get Data or New Source > Blank Query.

or

A blank query would appear like image below:

At the blank query, key in the below code:

= {Number.From(List.Min(Table_Name [Column_Name]))..Number.From(List.Max(Table_Name [Column_Name]))}

Replace the Table_Name and Column_Name with the raw data which the calendar would synchronize with.

Note:

• Make sure the raw data table name has no space in it.

• Make sure the date column in the raw data ONLY consists of Date, no Time or other data in it.

After entering the query, a list would appear like the image below:

Convert the list to table by clicking on the To Table in Transform tab.

The list will then be converted in to a table.

Right click on the column and change the Data Type to be Date.

Voilà, a calendar table is created.

Continue to rename the column name and ad additional information such as month, year and so on if needed.

DAX:

Import the raw data where the calendar table would follow or synchronize with.

Go to Modeling > New table

At the blank editor, key in the below code:

Calendar = CALENDAR(MIN(Table_Name[Column_Name]),MAX(Table_Name[Column_Name]))

Replace the Table_Name and Column_Name with the raw data which the calendar would synchronize with.

Go to Table View to check on the created calendar table.

Click on the Date column, it will be highlighted in dark green and Column tools tab will appear, the Date format can be changed in the Format section. Change to (Short Date) format to remove the time from the Date column.

These are the two ways to create dynamic calendar table in Power BI, using Power Query or DAX. Whenever there is any changes in the date in the raw data source, the created calendar table would be updated automatically together with the date in the raw data source.

Hope this article gives you an idea in creating a dynamic calendar table and have a nice day!