Time dim table incorrectly recognized by CALENDARAUTO()

[Hawkwing]

Hey everyone, first-time poster here. I have separate date and time dimension tables I've picked up from posts in this forum and elsewhere. My problem is that my date dim table, which uses CALENDARAUTO() to generate a dynamic, contiguous date range based on data in the model, is somehow detecting my Time column in my time dim table as a date, and so its date range starts in 1899:

The data type in my DimTime table's Time column is definitely set to Time, not Date or Date/time:

And its other columns are set to non-date/time data types (whole number, text, etc.). I don't want to stop using CALENDARAUTO, as dynamically selecting the date range based on the model date range is appealing. But I don't understand why it's recognizing my time column as a date/time even though it's explicitly characterized as time only, and I've banged my head against this wall for a while. Nor do I see in CALENDARAUTO's documentation a way to exclude a certain table from its calculation/the model.

Any suggestions on how to avoid my time dim table being recognized by my date dim table? This feels like something obvious that I'm just missing.

Here's the DAX for my date dim table. The time dim table is from this Radacad blog post. And here's a simplified .pbix that *should* just have a date dim range from 1/1/2019 to 1/1/2021, but doesn't due to the issue described above.

DimDate =

ADDCOLUMNS (

CALENDARAUTO(),

"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),

"Year", YEAR ( [Date] ),

"Monthnumber", FORMAT ( [Date], "MM" ),

"YearMonthnumber", FORMAT ( [Date], "MM/YYYY" ),

"YearMonthShort", FORMAT ( [Date], "mmm YYYY" ),

"YearMonthAsInteger", FORMAT ( [Date], "YYYYMM" ),

"MonthNameShort", FORMAT ( [Date], "mmm" ),

"MonthNameLong", FORMAT ( [Date], "mmmm" ),

"DayOfWeekNumber", WEEKDAY ( [Date] ),

"DayOfWeek", FORMAT ( [Date], "dddd" ),

"DayOfWeekShort", FORMAT ( [Date], "ddd" ),

"Quarter", "Q" & FORMAT ( [Date], "Q" ),

"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )

)

[AlexisOlson]

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

[Hawkwing]

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 · Accepted solution]

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.

[Hawkwing]

Thanks for your help!