topic Re: CALENDARAUTO not picking up date from a table in DAX Commands and Tips

CALENDARAUTO not picking up date from a table

Enigma — Mon, 18 Oct 2021 07:35:45 GMT

Hi,

I have a main table called IT_support_call_logs and multiple related tables. The main table has one row per IT support ticket created. Also, I have a date table built using CALENDARAUTO() and custom columns as per the fiscal year of the organisation.

A new requirement has come up where I need to show the dates of the last ticket logged +1 year.
For instance, if the last ticket logged was on October 18th 2021, I also need to show the year 2022 in the visuals.

For this, I created another table with just one row with the following code:

AdjustTable = ROW( "Next Year", DATE( YEAR(MAX(tblSherlockRaw[Ticket End Date])) + 1, MONTH(MAX(tblSherlockRaw[Ticket End Date])), DAY(MAX(tblSherlockRaw[Ticket End Date])) ) )

This gives me October 18th 2022 as the value in the new table. I created a relationship between this table and the calendar table.

However, CALENDARAUTO() does not pick up dates of 2022. It stops at 31-12-2021 instead of 31-12-2022.

Is there something wrong I am doing?

Re: CALENDARAUTO not picking up date from a table

amitchandak — Mon, 18 Oct 2021 07:51:34 GMT

Enigma , better create with calendar

Date =

var _min = eomonth(min(tblSherlockRaw[Ticket End Date])),0)

var _max = eomonth(max(tblSherlockRaw[Ticket End Date])),12)

return

calendar(_min, _max)

Re: CALENDARAUTO not picking up date from a table

Enigma — Mon, 18 Oct 2021 09:32:33 GMT

Thanks amitchandak ,

Is there any reason CALENDARAUTO should not be used? is it not reliable?

Re: CALENDARAUTO not picking up date from a table

v-yingjl — Fri, 22 Oct 2021 01:52:28 GMT

Hi Enigma ,

Please note this when using Calendarauto() function:

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.

Dates from calculated table would not be included in the calendar when using Calendarauto().

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: CALENDARAUTO not picking up date from a table

Enigma — Fri, 22 Oct 2021 06:04:58 GMT

Thanks for this helpful information, v-yingjl I was not aware of this.

Re: CALENDARAUTO not picking up date from a table

Enigma — Fri, 22 Oct 2021 08:13:45 GMT

I solved this by adding the fiscal_year_end_month parameter of CALENDARAUTO.
And then just adding +1 to the max of the year.

DateTable = var MinYear = YEAR(MIN(tblSherlockRaw[Ticket End Date])) var MaxYear = YEAR(MAX(tblSherlockRaw[Ticket End Date])) + 1 RETURN ADDCOLUMNS( FILTER( CALENDARAUTO(8), YEAR([Date]) >= MinYear && YEAR([Date]) <= MaxYear ), // custom columns... )

Re: CALENDARAUTO not picking up date from a table

Silver1 — Sat, 22 Apr 2023 23:54:38 GMT

i have a problem, my calendarauto is return the wrong year.

in my table, my first year is 2006. But when i put the formula my return is that