Power BI: Auto-Create a Calendar Table Using DAX

Creating a calendar table is in many cases, very essential for many Power BI dashboards. Using a standalone calendar table can make it easier to join it to different tables that have date columns and then using the joins to create unique visualizations without the need to repeat most common fields of date columns.

We’ll create a calendar table with fields for date, day, month, week, quarter, year, ISO week etc.

Here we have data with customer Subscription Date and we’d like to create a calendar that we can use as a standalone table. Our dataset has 10,000 rows so we want to make sure that our calendar will include all the dates that are in the dataset.

Create Calendar Table Using DAX

Create Calendar Table From Your Data Using CALENDARAUTO()

When you have a large dataset and you want to automatically detect the start date and end date that is in your data, without the need to manually search for them, then use the function CALENDARAUTO().

Using the following code, you will create a one column table that has continuous dates where the range of dates is calculated using your data in the model automatically.

The start date is the beginning of the year from the earliest date in your dataset. And the end date is the end of the year from the latest date in your dataset.

For example, in our dataset, the earliest data is Jan 10, 2020 and the latest date is May 29, 2020. So the CALENDARAUTO() function will have the date range of Jan 1, 2020 to Dec 31, 2022.

CALENDARAUTO([fiscal_year_end_month]) 

You can leave the [fiscal_year_end_month] as empty. If empty defaults to 12, which means that the last month is 12 (December) so the calendar will start from January 1 and end on Dec 31

Or you can put any number between 1 and 12 here. For example if you put 3 in the brackets (where 3 refers to the month number and stands for March), the calendar will start in April 1 (and year will from the earliest date) and end on March 31 (year will be from latest date in the dataset).

You can read more about the CALENDARAUTO() function from the Microsoft site here.

Create Calendar Table Manually CALENDAR()

If you want to create a calendar that has a start date and end date beyond the years available in your dataset, then you can manually set a start date and end date using CALENDAR() function in DAX.

This function will create a one column table of continuous dates from the start date till the end date.

CALENDAR(<start_date>, <end_date>)  

This function cannot be used in DirectQuery in calculated columns or row-level security (RLS) rules.

Examples from the Microsoft site include:

= CALENDAR (DATE (2015, 1, 1), DATE (2021, 12, 31)

Here the Date function has the syntax DATE(<year>, <month>, <day>) and you can use it set your start and end date.

= CALENDAR (MINX (Sales, [Date]), MAXX (Forecast, [Date]))

You can also use the above example to extract the minimum and maximum date in your dataset to set it as the start and end date for your calendar.

Create All Fields In Calendar Table

Extract Day of Month from Date Column

You can extract the day of month from the date column in DAX using a calculated column with the DAY() function. The function will return a value between 1 to 31.

DAY(<date>)  

You can use a string as well to get the day as the return value. For example, the below code, in both cases will return the value of 4 as the result.

= DAY("3-4-1007")  
= DAY("March 4 2007")  

You can also use any expression (simple or complicated) that will return a date as the result in this function. You can also embed this function into other functions as nested.

Extract Time from Date Column

When you have a date column, you can choose whether you see the time in the same column as well or not.

For a date column, you can select the datatype from Column tools.

Once you select the Date or Date/time datatype from the Column tools, you can select the format from the Format dropdown.

You can find the Date/time format from the same place as above.

Once you have set the Datetime datatype, you can extract the hour, minute and second from the date field using these DAX functions.

TIME(hour, minute, second)  

HOUR(<datetime>)  

MINUTE(<datetime>)  

SECOND(<time>) 

The HOUR() function returns the hour as a number between 0 (12:00 A.M.) to 23 (11:00 P.M.).

Both MINUTE() and SECOND() functions return a number from 0 to 59.

Get Day of Week from Date Column

Returns a number from 1 to 7 identifying the day of the week of a date. By default the day ranges from 1 (Sunday) to 7 (Saturday).

WEEKDAY(<date>, <return_type>)  

Returned value	Corresponding Day
1	Sunday
2	Monday
3	Tuesday
4	Wednesday
5	Thursday
6	Friday
7	Saturday

Get Day Name from Date Column

Instead of getting a number telling us whether it is a Sunday (1) or Monday (2) etc, it is more useful to get the value as “Mon” or “Monday” etc. The way to achieve it is by using the DAX function FORMAT().

The way to check what to use in FORMAT() function, refer to the Format dropdown from the Column tools. Here we can see that the format “dddd” gives us the value of “Wednesday”.

The FORMAT() function has many uses, and you can read about in details here from the Microsoft site.

FORMAT(<date>,"dddd")

The “dddd” will return the full day name – eg Sunday, Monday etc.

The “ddd” format will only return the first three letters of the day – eg Sun, Mon, Tue etc.

Extract Week Number of Year from Date Column

The DAX function WEEKNUM() returns the week number of the year for the date that you provide to it.

It has two systems that you can use based on your needs:

1. Week of January 1 is considered first week of the year (this is the default). So every 7 days starting from Jan 1 is one week, then on Jan 7, week 2 starts and so on. Assumes that weeks start on Sunday.
2. First Thursday of the year is considered first week of the year. Assumes that weeks start on Monday.

WEEKNUM(<date> [, <return_type>])  

where <date> can be any date format value or expression that evaluates to date format..

<return_type> is optional to include. If omitted, then the system where week of January 1 is considered first week of the year is used.

Get ISO Week of Year from Date Column

ISO week system is a leap week calendar system (ISO 8601) mostly used in Europe or in businesses to set fiscal years. In the ISO system, the first week is the one where four or more days fall in the new year.

Here’s more details on ISO Week system from Wikipedia.

Here’s a table created in Power BI comparing the Normal week number vs ISO week number starting from Dec 15th to Jan 15 to give you an idea of the difference between the two systems. You can see that they are completely different.

Extract Month from Date Column

Returns the month as a number from 1 (January) to 12 (December).

MONTH(<datetime>) 

Get Quarter of Year from Date Column

Quarters are used in business finance to break down the year into 4 portions. Each set of months is then referred to as a ‘Quarter’ – Q1, Q2, Q3, and Q4.
Usually

1. Quarter 1 is January-March
2. Quarter 2 is April-June
3. Quarter 3 is July-September
4. Quarter 4 is October-December

QUARTER(<date>)

Returns the quarter as a number from 1 (January – March) to 4 (October – December).

Get Year from Date Column

Returns the year of a date as a four digit integer in the range 1900-9999.

YEAR(<date>)