The CALENDARAUTO function returns a table with a single column named "Date" containing a sequential range of dates between two dates automatically extracted from the data model (and included in the range).
CALENDARAUTO(
<fiscal_year_end_month>
)
- fiscal_year_end_month: Optional argument. Integer number between 1 and 12 indicating the last month of the fiscal year (where 1 corresponds to January and 12 to December). If omitted, the value indicated in the template of the current user's calendar table is taken -if it exists-. Otherwise it takes the value 12 (corresponding to December).
The CALENDAR function returns a table with a single column named "Date".
The date range is calculated based on the following considerations:
- The starting date is the oldest date that appears in the data model that does not come from a calculated column or a calculated table.
- The end date is the last most recent date in the data model that does not come from a calculated column or a calculated table.
- The date range returned will be one that begins with the first day of the fiscal year corresponding to the beginning date and ends with the last day of the fiscal year corresponding to the end date.
In this example, we start from a table ("Sales") containing information about a set of sales, information that includes the date on which each one occurred:
Next, a custom table is created with the CALENDARAUTO function indicating December as the end of the fiscal year. Note that all the dates included in the sales table therefore belong to the same fiscal year. The function extracts the start (January 15, 2016) and end dates (February 22, 2016), calculates the beginning of the fiscal year for the start date (January 1, 2016) and the end of the fiscal year for the end date (December 31, 2016) and returns a table with the date range between January 1, 2016 and December 31, 2016:
However, if we created the table indicating the end of the fiscal year in January, the oldest and most recent date of the data model would no longer belong to the same fiscal year (January 15, 2016 belongs to the fiscal year that starts on February 1, 2015 and ends on January 31, 2016, and February 22, 2016 belongs to the fiscal year that begins on February 1, 2016 and ends on January 31, 2017), so the dates that limit the range to be returned change: the beginning of the fiscal year for the start date would be, as explained, February 1, 2015 and the end of the fiscal year for the end date would be January 31, 2017, so the CALENDARAUTO function returns the date range between the two:
