Forum Discussion

PatrickByGecko's avatar
PatrickByGecko
Icon for Helper V rankHelper V
2 years ago
Solved

Help trying to join two tables with date comparison

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?

Need Help
  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    2 years ago

    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!

1 Reply

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Icon for Super User rankSuper User
    2 years ago

    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!