MAX & MIN Dates from Multiple tables

[akwang]

Hi I have a model with a calendar Table and 3 other Record tables all with different dates.

To Create the Calendar Table i used this:

DateTable =
ADDCOLUMNS (
CALENDAR (MINX(Table1,[Date ]), MAXX(Table1,[Date ])),
"Year", YEAR ( [Date] ),
"QuarterOfYear", "Q" & FORMAT ( [Date], "Q" ),
"MonthOfYear", FORMAT ( [Date], "MM" ),
"MonthInCalendar", FORMAT ( [Date], "mmm YYYY" ),
"QuarterInCalendar", "Q" & FORMAT ( [Date], "Q" ) & " " & FORMAT ( [Date], "YYYY" ),
"DayInWeek", WEEKDAY ( [Date] ),
"DayOfWeekName", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"RelativeYear", (YEAR(NOW())- YEAR ( [Date] )))

However, I want to have the formula check the dates in all three tables and look for MAX & MIN Date. Can anyone help me?

PS. I do not want to merge the 3 Tables Together

[v-jiascu-msft]

Hi @akwang,

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

Best Regards!
Dale

[Anonymous · Accepted solution]

The range of dates is calculated automatically based on data in the model by using CALENDARAUTO().

Try this....

DateTable = 

ADDCOLUMNS (
CALENDARAUTO(),
"Year", YEAR ( [Date] ),
"QuarterOfYear", "Q" & FORMAT ( [Date], "Q" ),
"MonthOfYear", FORMAT ( [Date], "MM" ),
"MonthInCalendar", FORMAT ( [Date], "mmm YYYY" ),
"QuarterInCalendar", "Q" & FORMAT ( [Date], "Q" ) & " " & FORMAT ( [Date], "YYYY" ),
"DayInWeek", WEEKDAY ( [Date] ),
"DayOfWeekName", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"RelativeYear", (YEAR(NOW())- YEAR ( [Date] )))