Forum Discussion

akwang's avatar
akwang
Icon for Advocate II rankAdvocate II
9 years ago
Solved

MAX & MIN Dates from Multiple tables

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

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

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

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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