In this article we’ll cover how to create a dynamic calendar in Power BI, using the Power Query Editor.

Calendar table is the most important table in Power BI model. We use it to connect to big fact tables and filter or aggregate our data. It enables us the use of time intelligence functions that only work on continuous time range like TOTALYTD() TOTALMTD(), DATESBETWEEN(), etc. You can read more on calendar table in the article Calendar in Power BI.

Power BI will generate calendar table for each date field in our data. If we have three date fields in our tables, for example Date, OrderDate and PaymentDate, we would get three separate hidden calendar tables, each for one field. These would slow down our model. Good practice is to turn automatic generation of calendar tables off and create a calendar table ourselves. We can turn off this setting by following File > Options and settings > Options > Data Load > Global > Auto date/time for new files.

How To Create a Dynamic Calendar Table in Power Query

Let’s create a dynamic calendar table through an example. We will open the following business data in Power Query.

Fact table with Date field.

Appending Queries

We have date fields in queries Actual and Plan. We reference both queries by right-clicking and selecting Reference. We name the new references Calendar and hCalendar. We then select Date field in table Calendar, right-click and select Remove Other Columns. We do the same for hCalendar. We now only have Date field in both tables. We merge tables: we first select Calendar and then Append Queries. We select to append table hCalendar.

We append hCalendar to Calendar.

We now have all the dates from our model in one date field of table Calendar.

Finding Start and End Date

From all the dates we really need just two: the earliest and the latest. From these two we’ll make our calendar table that will consist of the first day of earliest year and end with the last date of latest year. Example: if earliest and latest date in our data model are 7/3/2010 and 10/15/2019, we want a calendar table of all the dates between 1/1/2010 and 12/31/2019.

To find the two dates we need, we start by calculating the first day of the year in our new Calendar table: we right-click and select Transform > Year > StartOfYear. This transforms all the dates to the first day of each year. We then remove duplicated with right-click, Remove Duplicates. This step isn’t necessary, but it speeds up further transformations. We’re left with unique dates. We duplicate the column by right-clicking and selecting Duplicate Column. In new column we right-click and select Transform > Year > EndOfYear. We now have columns with beginnings and ends of years.

Start and end dates of all the dates in our data model.

We change data type of both fileds to numbers. Dates in Power BI are actually numbers that represent days passed since 1/1/1900. For example, if we were to convert number 5 to date type, we would get 1/5/1900.

We change dates to numbers and sort them: ascending in first column and descending in second.

As we see, earliest and latest date are actually just the smallest and largest number. We find the smallest number in first column: we select Transform > Statistics > Minimum (see image above). We get a step that returns the smallest number in the model. In the Applied Steps window, we rename this step to MinDate. We then find the latest date: we duplicate the MinDate step and rename this new step to MaxDate. We modify the formula in the step from Min to Max.

We duplicate the MinDate step, rename it to MaxDate and modify formula to Max.

We now have start and end date in two steps, which we’ll use in creating our calendar table.

Creating a list

We’ll create a list of dates using start and end date. We click the formula sign fx and select Insert Step. We confirm the option with Insert. We then enter = {MinDate..MaxDate} in formula bar.

Using formula in the image above, we create a range of dates between StartDate and EndDate.

Formula generates a list of dates between the dates. We convert the list to table by right-clicking and selecting To Table. We then convert data type to date. We now have a table of all the dates we need in our data model.

M function

All the transformations we used in this tutorial were stored in Power Query in the form of formulas. We can see all the steps in the Applied Steps window. Formulas are written in a language called M. We can see the formulas in the formula bar.

We can join the steps to a single function that we use every time we create a new dynamic calendar. We’ll explain what the function looks like and how to use it in our next article, Dynamic Calendar in Power Query using an M function.