Power BI - Creating a Calendar Table in Power BI using DAX Functions

There are two DAX functions in Power BI that help you create a calendar table, CALENDAR() and CALENDARAUTO(). Using these functions you can create a calendar table within few seconds, let me show how.

CALENDAR() Dax function -

One of the easiest ways of creating a calendar table is using Calendar() function in DAX. Its a simple function with just two input parameters; start date, and end date.

Output of this function is a table with one column that contains dates which fall between the start date and end date (1 day per row). Here's an example of how to create a calendar table using CALENDAR()

Click on "New table" and enter the below mentioned query

Calendar = CALENDAR(DATE(2022,2,22),DATE(2022,12,31))

CALENDAR() function takes two dates as an input so we have used the DATE() function to pass static dates. This function will return a table having dates starting from 1st Jan 2022 to 31st Dec 2022.

It is not mandatory to put static dates when you define the calendar table, you can even use it with relative dates. Here's an example:

Calendar Relative = 
CALENDAR(
        TODAY()-365,
        TODAY()
)

TODAY() function returns the current date, so this query will return dates starting from 365 days prior to current date and end at the current date.

You can also create the calendar table based on the minimum and maximum date in your data.

But wait! what if I tell you there is a simpler way to do this.

CALENDARAUTO() Dax function -

This function produces the same result as the CALENDAR() function but we don't need to pass any start and end date. This start date is based on the minimum date in your data set (Across all columns, except calculated columns, tables, and measures) and the end date is based on the maximin date in your dataset (Across all columns, except calculated columns, tables, and measures).

Let us see how to use this function.

Calendar auto = CALENDARAUTO(3)

Now you might be wondering why have I passed '3' inside the function. Its an optional parameter which is used to specify end of the fiscal year, so in this case the dates will start from 1st April.

If we don't pass any parameter here, it considers calendar year as the fiscal year i.e. starting from 1st Jan.

Note - When we use CALENDARAUTO() function the dates will start from first day of the fiscal year in which the minimum date from our data falls and end on the last day of fiscal year in which our maximum date falls.

Here's a bonus for you as you stayed till the end!!!

When we create a custom calendar table its usually never with just one date column, we need to add more information like a year, month, week etc. which we can use to slice the data or perform different calculations in our Power BI report. Lets see how to do that.

Master Calendar = 
var start_date = MIN(FactInternetSales[OrderDate])
var end_date = MAX(FactInternetSales[OrderDate])
var cal = CALENDAR(start_date,end_date)
return
ADDCOLUMNS(cal,
            "Year",YEAR([Date]),
            "Fiscal Year",IF(MONTH([Date])>3,YEAR([Date]),YEAR([Date])-1),
            "Quarter","Q"&QUARTER([Date]),
            "Quarter No",QUARTER([Date]),
            "Month",FORMAT([Date],"mmmm"),
            "Short Month",FORMAT([Date],"mmm"),
            "Month Number",MONTH([Date]),
            "Week",WEEKNUM([Date]),
            "Week Day",FORMAT([Date],"dddd"),
            "Week Day Number",WEEKDAY([Date],2)
)

Copy the above query and create your own master calendar table, all you need to do is replace start_date and end_date expression.

Note - Here "fiscal year" is starting from April and "Week Day Number" is starting from Monday.

Feel free to modify this query and try different things out.

That's all for this blog, thank you for reading and if you are facing any challenges with Power BI do let me know on bi@ishandeshpande.com and I'll try to write a blog on that.

See you in my next blog.