What is the difference between CALENDAR and CALENDARAUTO Functions in Power BI DAX
CALENDAR Function (DAX)
This function 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.
Syntax :
CALENDAR(<start_date>, <end_date>)
start_date - is any DAX expression that returns a datetime value.
end_date - is any DAX expression that returns a datetime value.
Remarks
An error is returned if start_date is greater than end_date.
Example :
The following formula returns a table with dates between January 1st, 2015 and December 31st, 2020.
=CALENDAR (DATE (2015, 1, 1), DATE (2020, 12, 31))
For a data model which includes actual Sales data and future sales Forecast. The following expression returns the date table covering the range of dates in these two tables.
=CALENDAR (MINX (Sales, [Date]), MAXX (Forecast, [Date]))
CALENDARAUTO Function (DAX) :
This function returns a table with a single column named “Date” that contains a contiguous set of dates. The range of dates is calculated automatically based on data in the model.
CALENDAR Function (DAX)
This function 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.
Syntax :
CALENDAR(<start_date>, <end_date>)
start_date - is any DAX expression that returns a datetime value.
end_date - is any DAX expression that returns a datetime value.
Remarks
An error is returned if start_date is greater than end_date.
Example :
The following formula returns a table with dates between January 1st, 2015 and December 31st, 2020.
=CALENDAR (DATE (2015, 1, 1), DATE (2020, 12, 31))
For a data model which includes actual Sales data and future sales Forecast. The following expression returns the date table covering the range of dates in these two tables.
=CALENDAR (MINX (Sales, [Date]), MAXX (Forecast, [Date]))
CALENDARAUTO Function (DAX) :
This function returns a table with a single column named “Date” that contains a contiguous set of dates. The range of dates is calculated automatically based on data in the model.
Syntax :
CALENDARAUTO([fiscal_year_end_month])
CALENDARAUTO([fiscal_year_end_month])
fiscal_year_end_month- is any DAX expression that returns an integer from 1 to 12. If omitted, defaults to the value specified in the calendar table template for the current user, if present; otherwise, defaults to 12.
In this example, the MinDate and MaxDate in the data model are July 1, 2010 and June 30, 2015.
CALENDARAUTO() will return all dates between January 1, 2010 and December 31, 2015.
CALENDARAUTO(3) will return all dates between March 1, 2010 and February 28, 2016.
Remarks:
The date range is calculated as follows:- The earliest date in the model which is not in a calculated column or calculated table is taken as the MinDate.
- The latest date in the model which is not in a calculated column or calculated table is taken as the MaxDate.
- The date range returned is dates between the beginning of the fiscal year associated with MinDate and the end of the fiscal year associated with MaxDate.
- An error is returned if the model does not contain any datetime values which are not in calculated columns or calculated tables.
In this example, the MinDate and MaxDate in the data model are July 1, 2010 and June 30, 2015.
CALENDARAUTO() will return all dates between January 1, 2010 and December 31, 2015.
CALENDARAUTO(3) will return all dates between March 1, 2010 and February 28, 2016.
--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------
No comments:
Post a Comment
Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.