Calendar Table That Takes Minimum and Maximum of a Column across Multiple Tables

[Anonymous]

Hello,

I have six tables which all contain a "RecordActiveFrom" datetime column. I'm trying to create a calendar table which uses the minimum value of all of the RecordActiveFroms as the startdate and the maximum value of all of the RecordActiveFroms as the end date.

I have tried editing the calendar table code in the advanced editor to take a StartDate that looks similar to "List.Min(Table1[RecordActiveFrom], Table2[RecordActiveFrom], Table3[RecordActiveFrom])" and List.Max for the maximum value but this has returned a number of different errors with date formatting and invalid values.

I have also tried creating a nested MIN function on a calculated column but again ran into some issues.

Does anyone have an efficient solution which could be used for this?

[Greg_Deckler]

I believe that you should just be able to use the DAX CALENDARAUTO function for this. My understanding of CALENDARAUTO is that it creates a calendar table by looking at the dates in the data model and creates a calendar table based upon those dates.

[DM_BI]

Hello @Greg_Deckler 

Does the calendarauto get bigger if we add new data out of the calendarauto?

For example mine begins in 2018 but I will receive data from 2016 and don't know that will happen.

Thank you very much,

DM

[Anonymous]

I think this would work but my model includes other datetime columns aside from RecordActiveFrom that I don't want to calculate the minimum or maximum for. I believe CALENDARAUTO would factor in those dates making my date range larger than needed.

[dramus]

The calendar DAX function will work to generate a Dates table if you can get the data into a correct Date format.

Date Table = CALENDAR(min('Billing Data'[Date]),max('Billing Data'[Date]))

I think there in lies your problem. If all of your RecordActiveFrom fields are not valid Date field, then you are going to have a problem.

My approach would be:

1) Ensure that the fields are Date (Or Date/Time) type.

2) Create measures with the minimum and maximum date fields

3) Use the CALENDAR function with the new measures.