Knowledge base

In this article we’ll create a dynamic calendar in Power BI using CALENDARAUTO() function.

Calendar is the most important table in Power BI data model. We have seen how calendar table looks in our Calendar in Power BI article. We have also created a dynamic calendar in Dynamic Calendar in Power BI Using an M Function. Today we’ll create a dynamic calendar using a DAX function.

CALENDARAUTO() function

Formulas in Power BI are written in DAX language. Syntax is very similar to Excel’s formulas. Function for creating a calendar is CALENDARAUTO(). Function finds the earliest and latest date in our data and generates a list of all the dates between the beginning of the earliest and the end of the latest year. For example, if those dates are April 3, 2000 and November 20, 2019, CALENDARAUTO() will generate a list of all the dates between January 1, 2000 and December 31, 2019.

Data table.

We create a calendar by clicking New Table and typing Calendar = CALENDARAUTO() in formula field. We confirm the formula with Enter. We get a new table Calendar, that contains all the dates in our data model.

Creating a simple calendar with CALENDARAUTO().

We usually want to know date attributes like day, month, quarter and year. Attributes allow us to filter our data. When we want to know our expenses in the year 2019, Power BI uses a calendar table to find all the dates in year 2019 and returns an aggregated result.

We describe each attribute in its own column. For month attribute we click New Column and type Month = MONTH(Calendar[Date]) to formula bar. We do the same for day, week, quarter, and year. We use following functions:

Quarter = QUARTER(Calendar[Date]),

WeekdayNo = WEEKDAY(Calendar[Date]),

WeekNo = WEEKNUM(Calendar[Date]).

Custom DAX function

We can store all the manual steps into a function and use it every time we need to create a calendar table. The function creates a calendar table of all the dates in the data and all of its attributes. The function is fairly simple:

Function that creates a calendar table in Power BI.

We use it the same as we used function CALENDARAUTO(): we click New Table and paste the code into formula bar. We get a calendar table:

Final look of the calendar table.

Problem with CALENDARAUTO(): date and time Power BI

Using CALENDARAUTO() is simple and fast, but it has some disadvantages. If we have a time field in our data, it will be treated as a date January 0, 1900. Time in Power BI is a decimal number, representing a share or a part of the day. Similarly, day is a number, representing number of days passed since January 1, 1900. For example, number 0.5 represents 12 pm on January 0, 1900. If we convert a decimal number between 0 and 1 to a date, we’ll always get January 0, 1900. CALENDARAUTO() will read this as the earliest date (it’s unlikely we would have any even earlier dates in our data) and set 1899 as the earliest year in our data (since January 0, 1900 doesn’t exist). This will create a huge calendar table, that will slow our model down.

Power BI treats numbers as dates and vice versa. In upper example, we have the same numbers in all three columns, formatted as number, date and time.

Similar problem arises if we have birth dates in our data. The function will again read it as the earliest date entry and create a big calendar table that will slow our model down. Better way to create a calendar table is shown in Dynamic Calendar in Power Query using an M function.