Calendar Function in DAX

Calendar Function in DAX

Calendar function is used to generate dates between 2 specified dates. It is included in SQL Server Analysis Services 2016, PowerPivot 2016 and Power BI only.

Syntax:

CALENDAR(<Start_Date>,<End_Date>)

Returns a table with a single column named “Date” that contains a contiguous set of dates. The range of dates is from the specified start date to the specified end date, inclusive of those two dates.

Parameters: First one supposed to be the Starting Date from where user wants to generate dates and the second one is the End Date till what date we are generating dates.  

Start_date  à can be any DAX Expression or a column that returns a DateTime value.

End_date  à can be any DAX Expression or a column that returns a DateTime value.

First parameter values should be less than the second parameter value.  It will throw error if the starting date is greater than the ending date.

Example:

In the following example we are going to generate dates from user defined start date and end date.
It will generate dates from 1^st jan 2018 till 31^st Jan 2018. It will create a table with the column name – “Date”.

Cal_User_Dates = CALENDAR (DATE (2018, 1, 1), DATE (2018, 1, 31))

Output will be :

 

We can use the date columns of data model as arguments to start from the minimum date of a date column till the maximum date of the same date column or someother date column.  

In the following example we are going to generate dates from minimum date of Order Date column till the maximum date of Order Date column.

 Cal_Data_Model = CALENDAR(MINX(Orders,Orders[Order Date]),MAXX(Orders,Orders[Order Date]))

It will generate dates from minimum order date till maximum order date. we can check the dates in Cal_Data_Model (which we have generated above) with the help of Cards.

 Following is the result –

Another Example-

Calendar_User_Defined = CALENDAR(MINX(Orders,Orders[Order Date]),TODAY())

It will generate dates from minimum order date till current Date. We can check the dates in Calendar_User_Defined (which we have generated above) with the help of Cards.

Following is the result –

Please leave comments and suggestions....