Creating a calendar

One of the most frequently asked questions regarding the calendar required to use time intelligence features is, is it better to create it external to Power BI? Or in the query editor? Or maybe with DAX?

In this scenario we are going to create a complete calendar in DAX, including the number and name of the month, quarter, day of the week, etc.

To begin, we create the basic calendar table using the CALENDARAUTO function. This function creates a table with a single column with a sequence of dates covering all the dates that appear in our data set, by default from January 1 of the first year to December 31 of the last year:

However, we want to add other fields to this basic table, for which we are going to use the following DAX functions (note in the image above that the field created is named "Date"):

• To create the year: YEAR
• To create the month number: MONTH
• To create the day number: DAY

We are going to add these new fields to the basic table using the ADDCOLUMNS function:

The names for the month and the day of the week can be created with the FORMAT function, and the week number and the day of the week number with the WEEKNUM and WEEKDAY functions, respectively:

Finally, to obtain the quarter number we can use the formula:

...which calculates the nearest integer equal to or greater than the month divided by 3 (with the CEILING function).

If we also wanted to have the equivalent preceded by a "Q", we can again resort to the FORMAT function and the & operator that allows us to concatenate text strings: