CALENDARAUTO Function not working properly?

[markmess77]

I am working with a dataset that contains data from 2019 and 2020. I've been using a date table populated by the dax below, which has been working fine until recently. It now only returns a table for 2020, which means that all of my 2019 data is grouped together without any structure. According to the documentation I've read, the CALENDARAUTO function should automatically calculate the date ranges based on the data in the model.

Date = 
VAR BaseCalendar =
    CALENDARAUTO()
RETURN
 GENERATE(
     BaseCalendar,
     VAR BaseDate = [Date]
     RETURN ROW (
         "DateKey", FORMAT (BaseDate, "yyyymmdd"),
         "Year", YEAR(BaseDate),
         "MonthName", FORMAT(BaseDate, "mmmm"),
         "MonthNumber", FORMAT(BaseDate, "m"),
         "Quarter", "Qtr " & FORMAT(BaseDate, "q")
     )
 )

I was able to fix it by using 

CALENDAR ( DATE (2019, 1, 1), NOW() )

instead of the CALENDARAUTO function. At first glance everything seems to be back to working normally.

However, would this throw off some time intelligence calculations because it is not populating a full year's calendar? I've read somewhere before that some time intelligence functions require a full year's calendar to perform correctly. I haven't had a chance to test this myself, but it is something I'm thinking about as I'm building out some time intelligence measures/visuals.

Are there any potential reasons why the CALENDARAUTO function is not properly working in my case?

[Anonymous · Accepted solution]

@markmess77 

CALENDARAUTO() need a base date column of datetime type in your model. With out base date column you can only used Calendar (startdate, enddate) to create a calendar table.

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

[matbau]

Does it have to be Datetime type? or Will it work fine with a date column as well? I am having a similar issue, but in my case the CALENDARAUTO is going way to far to the past (1900) and giving me an inefficient Date Table. 

[mahoneypat]

I can't explain why CALENDARAUTO() is not working for you, but you can use this variation on your expression to get the full year.  That shouldn't be needed for Time Intelligence to work, but it is a good thing to do.

VAR Basecalendar = CALENDAR(DATE(2019,1,1), DATE(YEAR(TODAY()), 12,31))

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat