CALENDARAUTO Function for Date Table

[KayceeInMelbourne]

Hi all,

The 'Manage Dates' feature is absolutely brilliant. Bravo!

I noticed that the minimum and maximum dates are hard-coding every date column in the model.

For example:

VAR __FirstTransactionDate = 
MINX ( { MIN ( 'Test Table'[Date] ), MIN ( 'Test Table (2)'[Date] ), MIN ( 'Test Table (3)'[Date] ), MIN ( 'Test Table (4)'[Date] ) }, ''[Value] )

Is there a simplified version that wouldn't require manual updates (or refresh via Bravo)... effectively the equivalent of MIN(CALENDARAUTO()) without adding another table that is simply a list of all dates?

The only way I can see is
Step 1: Creat a table -->
'List of Dates' = CALENDARAUTO()
Step 2: Modify the min and max dates in the calculated date table to refer the list table. For example:
__FirstTransactionDate = MIN ('List of Dates'[Date])

I would like to avoid having the excess data but equally, that is a better compromise than the maintenance of the code if any new tables are added.

Thanks everyone for your advice.

Cheers
Kaycee

[marcosqlbi]

You can, but there are two issues:

1. You lose the feature provided by the columns marking the last date of transactions in your model, which means that time intelligence measures no longer remove results in the future
2. There are cases where you introduce a circular dependency issue with other calculations of calculated columns/tables, but I don't remember which templates are affected by that.

If this is what you need, of course you can either customize the template or modify the resulting calculated table, but we are not going to modify the standard template for that because it would create issues described in 2 (original version of the template used CALCULATEAUTO, indeed).

[KayceeInMelbourne]

Thanks @marcosqlbi, this makes perfect sense, thank you.

As a side, could you tell me what concept would I need to research to understand why I can't reference the date column when using CALENDARAUTO as a variable?

For example this wouldn't work as the [Date] column is 'greyed out'.

Earliest Date := 
 VAR _AllDates = CALENDARAUTO()
 VAR _Result = MIN('_AllDates'[Date])
 
 RETURN
 _Result

[dgosbell]

You cannot reference columns in table variables like that. Using MINX over the variable should work

Earliest Date := 
 VAR _AllDates = CALENDARAUTO()
 VAR _Result = MINX( _AllDates, [Date])
 
 RETURN
 _Result

[KayceeInMelbourne]

Makes perfect sense, thanks so much for taking the time to reply!