CALENDARAUTO not picking up date from a table

[Enigma]

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?

[Silver1]

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

[Enigma]

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...
)

[amitchandak · Accepted solution]

@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)

[Enigma]

Thanks @amitchandak ,

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

[v-yingjl]

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.

[Enigma]

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