Help trying to join two tables with date comparison

[PatrickByGecko]

Hi I have two tables (or two power query requests) like this;

Table A

Name / DateOfContract (AAAAMMJJ)

John    /  20240102

Robert /  20240202

Herbert / 20240302

Table B

FromDate   / Rate

20240101  / 10€

20240301  / 15€

I would like to associate in A the newest rate from B (eq.  for each item of A =>max FromDate of B and B.Date <= A.DateOfContract)

It would give

Name / DateOfContract / Rate

John    / 20240102           /10€

Robert / 20240202          /10€

Herbert / 20240302        /15€

Can you help please?

[Sahir_Maharaj · Accepted solution]

Hello @PatrickByGecko,

Can you please try the following:

DateOfContract_Date = DATE(
    LEFT([DateOfContract], 4),
    MID([DateOfContract], 5, 2),
    RIGHT([DateOfContract], 2)
)

Rate = 
VAR ContractDate = DATE(
    LEFT(TableA[DateOfContract], 4),
    MID(TableA[DateOfContract], 5, 2),
    RIGHT(TableA[DateOfContract], 2)
)
RETURN
CALCULATE(
    MAX(TableB[Rate]),
    TableB[FromDate] <= ContractDate,
    ALL(TableB[FromDate]),  // Removes filters that might restrict the row context
    EARLIER(TableB[FromDate]) = MAX(TableB[FromDate]) // Gets the latest 'FromDate' less than 'ContractDate'
)

Hope this helps!