Getting MAX date from a table with conditions from unrelated table

RGallagher320 — Tue, 22 Dec 2020 00:08:49 GMT

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?

Re: Getting MAX date from a table with conditions from unrelated table

AllisonKennedy — Tue, 22 Dec 2020 02:34:52 GMT

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

topic Re: Getting MAX date from a table with conditions from unrelated table in DAX Commands and Tips

Getting MAX date from a table with conditions from unrelated table

Re: Getting MAX date from a table with conditions from unrelated table