Forum Discussion
Time dim table incorrectly recognized by CALENDARAUTO()
CALENDARAUTO doesn't know which tables and columns to look at so it looks at all of them. In my particular use cases, I don't typically need to look at more than a couple of columns to make sure I've captured the date range for all other columns I want to. You can have a bunch of date columns, but it's often predictable which ones will be dictating your maximum and minimum.
Super UserI don't like relying on CALENDARAUTO.
I'd suggest
CALENDAR( MIN ( 'Sample FactTable'[Date] ), MAX ( 'Sample FactTable'[Date] ) )
Or if you want to make sure you have full years,
CALENDAR(
DATE ( YEAR ( MIN ( 'Sample FactTable'[Date] ) ), 1, 1),
DATE ( YEAR ( MAX ( 'Sample FactTable'[Date] ) ), 12, 31 )
)
Thanks AlexisOlson. I agree MIN/MAX would be an easy workaround for the simplified example with a single fact table. For my actual reports, doing so would be a little more involved as I'd have to get the global min/max across multiple fact tables, but it should still be feasible.
Would you mind giving some background on your reservations about CALENDARAUTO()? Is it generally regarded as unreliable? I think in my case the root cause is it's detecting the Time data type as a blank date...which is annoying.
- AlexisOlson
CALENDARAUTO doesn't know which tables and columns to look at so it looks at all of them. In my particular use cases, I don't typically need to look at more than a couple of columns to make sure I've captured the date range for all other columns I want to. You can have a bunch of date columns, but it's often predictable which ones will be dictating your maximum and minimum.
Thanks for your help!