Matching data by most recent past date in a different table

Hi  joeyrobbins ,

Here are the steps you can follow：

1. Create calculated table.

Table_1 =
var _1=SELECTCOLUMNS('Table1',"Order Date",[Order Date])
var _2=SELECTCOLUMNS('Table2',"Order Date",[Date])
return
DISTINCT(
UNION(_1,_2))

2. Create calculated column.

ID =
MAXX(FILTER(ALL(Table1),'Table1'[Order Date]='Table_1'[Order Date]),[ID])

Status =
IF(
    'Table_1'[Order Date] >=MAXX(ALL('Table2'),[Date]),
    MAXX(FILTER(ALL('Table2'),'Table2'[Date]=MAXX(ALL('Table2'),[Date])),[Status]) 
    ,MAXX(FILTER(ALL('Table2'),'Table2'[Date]=MINX(ALL('Table2'),[Date])),[Status]))

Inventory =
IF(
   'Table_1'[Order Date] >=MAXX(ALL('Table2'),[Date]),
    MAXX(FILTER(ALL('Table2'),'Table2'[Date]=MAXX(ALL('Table2'),[Date])&&'Table_1'[ID]='Table2'[ID]),[Inventory])
    ,MAXX(FILTER(ALL('Table2'),'Table2'[Date]=MINX(ALL('Table2'),[Date])),[Inventory]))

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly