cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RGallagher320
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 AM10/1/20 2:23 AM
10/1/20 2:31 AM10/1/20 3:54 AM
10/1/20 3:54 AM10/1/20 5:17 AM
10/1/20 5:17 AM10/1/20 6:51 AM
10/1/20 6:51 AM10/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?

1 REPLY 1
AllisonKennedy
Super User III
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. 


If you found this post helpful, please give Kudos. ?


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


www.excelwithallison.com

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors