Merge two tables on ID and if before or after date?

[Anonymous]

I have two tables, a transaction table and a customer table. I make a relationship on the tables based on cusotmer id. But if the customer changes thier address this causes issues as now they are associted with multiple addresses when looking up an order. I want to be able to see the customer info at the time of the transaction. 

Transaction Table

OrderId	CustomerId	Value	Transaction_Date
999	123	10,999	10/11/2021
889	456	5999	3/5/2021

Customer Table

CustomerID	Address	last_change_date
123	123 N North Street	10/8/2021
456	456 W West Street	2/14/2021
123	789 E East Street	1/10/2021

The way it is now is id I wanted to make a the adress for customer 123 dor order 999 it would pull both address we have for the customer. But I want it to be albe to check both cusotmerID and compare the order_Date and check that the order_Date is within the range of of the last_change _dates. 

[smpa01]

@Anonymous  you can use a measure like this

_Address = 
CALCULATE (
    MAX ( Customer[Address] ),
    FILTER (
        VALUES ( Customer[last_change_date] ),
        Customer[last_change_date] <= MAX ( 'Transaction'[Transaction_Date] )
    )
)

[smpa01]

@Anonymous  did you have a chance to try out the above?

[Anonymous]

@smpa01 Yes, I seems to work for some. So one customer could have like 5 differnt adresses over the apst 10 years. And when I use this mesure it only populates 2-3 addresses and the others are left blank. The logic seems sound so Im not sure why its doing that. 

[smpa01]

@Anonymous  can you please provide some example

[Anonymous]

@smpa01 

I changed the  dummy data to fit a bit more to my real data. This is what I get. 

Customer_History

Order_History

What I got is same address for every order. Even for customer who isnt assoctiated with that address. 

[smpa01]

@Anonymous  please post sample data in table format.

[Anonymous]

@smpa01 

I changed the  dummy data to fit a bit more to my real data. This is what I get. 

Customer_History

Order_History

What I got is same address for every order. Even for customer who isnt assoctiated with that address. 

[lbendlin]

Your sample data doesn't cover all possible scenarios.  Please extend it if possible.

I would recommend to separate customers and customer addresses into different tables .

Why would a transaction care if the customer's address has changed? Different shipping costs?