Forum Discussion
CALENDARAUTO Function not working properly?
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?
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.
3 Replies
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.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
