Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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.
@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.
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.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |