The Power BI CALENDAR function is one of the DAX Date and Time functions useful to return a table with one column that contains a set of continuous dates. This function uses the start_date and end_date to print the continuous range of dates in a column named “Date”. This article explains the POWER BI DAX CALENDAR function to print the dates calendar and the syntax is shown below:
CALENDAR(<start_date>, <end_date>)
This function begins from <start_date> and stops printing dates at <end_date>, inclusive of those two dates. If the start date is greater than the end, it returns an error.
Power BI DAX CALENDAR function Example
To demonstrate the DAX CALENDAR() function, go to the Modeling Tab, click the New Table button, and rename it as May 2024. The below expression uses the DATE function to create a date from year, month, and day for the <start_date> and <end_date> arguments. The formula returns a table with dates between the May 1, 2024, and May 31, 2024. Next, please add this new May 2024 table to the report designer.
May 2024 = CALENDAR(DATE(2024, 5, 1), DATE(2024, 5, 31)
Similarly, we have created one more data table report to display the range of dates in an Order table starting from minimum to maximum Order date. As you can notice that the calendar table starts from 03 January 2019 and ends on 30 December 2022.
OrdersCal = CALENDAR(MINX(Orders, MIN(Orders[Order Date])), MAXX(Orders, MAX(Orders[Order Date])))
If you have multiple tables to compare the actual sales against the forecast, use the below code. Please refer to the DateTime and the function article for the remaining Power BI methods. For more Charts >> Click Here.
OrdersCal = CALENDAR(MINX(SalesTable, [Order Date]), MAXX(ForecastTable, [Order Date]))
CALENDAR Example 2
Create a new Table as CalendarTable with the below expression where the Minimum Order Date is the start date and the maximum Order Date is the End date.
CalendarTable = CALENDAR(MIN(FactInternetSales[OrderDate]), MAX(FactInternetSales[OrderDate]))
Next, go to the Model View click on the : (More options) symbol on any table, and choose the Manage Relationships option. Here, our job is to create a relationship between the FactInternetSales Order Date and the Date column available in CalendarTable. So, choose the Many to One relationship to establish the relationship. You can use CalendarTable and the Adventure Works database to perform calculations by this.
