Dax request: Filter a table's values based on values from another table

[PBI12345]

Hello,

In the below scenario, I have two tables. I am trying to build a measure that returns the value from Table2 associated with the maximum date from Table1 where column Time = "Past".

In this example, I would like my measure to return a value of "700".

I have tried a few different calculations but am struggling to filter one table's date range based on another. Would connecting the tables with an inactive relationship and using RELATED work? 

Any help greatly appreciated!

Table1
Date	Time
Jan-24	Past
Feb-24	Past
Mar-24	Past
Apr-24	Past
May-24	Past
Jul-24	Past
Aug-24	Past
Sep-24	Future
Oct-24	Future
Nov-24	Future
Dec-24	Future

Table 2
Date	Value
Jan-24	100
Feb-24	200
Mar-24	300
Apr-24	400
May-24	500
Jul-24	600
Aug-24	700
Sep-24	800
Oct-24	900
Nov-24	1000
Dec-24	1100

[Dangar332 · Accepted solution]

HI, @PBI12345 

If you have relationship b/w both tables on Date Column then try below measure.

Measure =
var Max_date = calculate(max(table1[Date]),table1[time]="Past")
return
calculate(sum(table2[value]),Table2[date]=Max_date)