CALENDARAUTO Function in DAX
CalendarAuto Function is used to Generate dates based on the date column of Data Model. It will generate dates from minimum of date value in the date column till the maximum of date.
It will generate based on the parameter value we are passing. It will generate from the month (parameter value) till the respective year end.
Syntax:
CALENDARAUTO([fiscal_year_end_month])
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.
Parameters:
Fiscal_year_end_month : 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.
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.
Example:
In the following Example , if the minimum date is 10th Jan 2016 and the maximum date is 22nd Feb 2018 in the model, it will generate date from 01-Jan-2016 till 31st Dec 2018. Since we are not passing any argument, it will take 12 as default which is December.
In the following Example, if the minimum date is 10th Jan 2016 and the maximum date is 22nd Feb 2018 in the model, it will generate date from 01-Jan-2016 till 28th Feb 2019. Since we are passing 3 as Argument, it will consider March as the starting of Fiscal year.
CALENDARAUTO(3) -- generate dates from 01-March-2016 till 28th Feb 2019.
No comments:
Post a Comment