cancel
Showing results for
Did you mean:
Frequent Visitor

## Getting MAX date from a table with conditions from unrelated table

New to DAX. Table X has many records let's say for October 1, 2020 with date/time.

I am trying to get the MAX date from table X that has a start date  >= Table Y -30/1440 and start date <= Table Y + 5/1440.

So table Y also has many records for date/time on October 1, 2020. Tables X and Y are unrelated.

Example:

Table X

 10/01/2020 12:50:00 AM 10/01/2020 02:27:23 AM 10/01/2020 02:34:37 AM 10/01/2020 03:57:35 AM 10/01/2020 05:20:58 AM

Table Y

 10/1/20 12:46 AM 10/1/20 2:23 AM 10/1/20 2:31 AM 10/1/20 3:54 AM 10/1/20 3:54 AM 10/1/20 5:17 AM 10/1/20 5:17 AM 10/1/20 6:51 AM 10/1/20 6:51 AM 10/1/20 8:09 AM

So Table Y has 10/1/20 12:46 AM and 10/1/20 2:23 AM (12:46 AM is used for the calculation).  So the MAX date out of Table X that falls between the red values is 10/01/2020 12:50:00 AM.

The jist is that for a given date, I want to get the max date out of a range in another (unrelated) table using said given date. I know this is confusing.

Can't figure out a way to do this or if its even possible. Any thoughts?

Super User III

Assuming you're doing the calculation in context of Table Y then?

For example, as a new COLUMN in tableY:

TableXDate = MAXX(FILTER(TableX, TableX[Date] >= TableY[Date] -30/1440 && TableX[Date] <= TableY[Date] + 5/1440), TableX[Date])

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.

www.excelwithallison.com

Announcements