How to use changed price for sales which happen after price update date?

[NorbertG]

Hi everyone.
I have three tables. 1st one 'price', 2nd one 'product', 3rd is 'sales' 

price_id	id_ref	date	active	price
1	413	01.01.2023	TRUE	10
2	414	01.01.2023	TRUE	11
3	444	01.01.2023	FALSE	12
4	444	01.09.2023	TRUE	11,4

product_id	id_ref	name
11	412	frame
12	413	door
13	414	window
14	444	chair

sale_id	product_id	quantity	date_of_sale	price*quantity
21	12	10	20.01.2023
22	13	10	15.03.2023
23	14	10	30.06.2023
24	14	10	11.09.2023

Relationships:
'price'[id_ref] *:1 'product'[id_ref]
'product'[product_id] *:1 'sales'[product_id]

All i want to achieve is for the column 'sales'[quantity*price] to take approperiate value from table price, according to 'sales'[date_of_sale]

[ERD]

Hi @NorbertG , the calculated colmn:

Column =
VAR _id = sales[id]
VAR _item = sales[item_id]
VAR _dt = sales[date_of_sale]
VAR _max_dt =
    CALCULATE (
        MAX ( 'product'[date] ),
        'product'[id] = _id,
        'product'[id_ref] = _item,
        'product'[date] <= _dt
    )
RETURN
    CALCULATE (
        MAX ( 'product'[price] ),
        'product'[id] = _id,
        'product'[id_ref] = _item,
        'product'[date] = _max_dt
    ) * sales[quantity]

[NorbertG]

Sadly, it doesn't work for me. It is all on me because i made mistake in my explanation. I forgot about table in between them.

[ERD]

@NorbertG , please, check again your data. What's in your sales table? Column name is product_id, but data is id_ref.

[NorbertG]

I checked my data. Really sorry for the inconvinience

[ERD · Accepted solution]

Your column:

Column =
VAR _pr_id = sales[product_id]
VAR _id_ref =
    RELATED ( 'product'[id_ref] )
VAR _dt = sales[date_of_sale]
VAR _max_dt =
    CALCULATE (
        MAX ( 'price'[date] ),
        'price'[id_ref] = _id_ref,
        'price'[date] <= _dt
    )
RETURN
    CALCULATE (
        MAX ( 'price'[price] ),
        'price'[id_ref] = _id_ref,
        'price'[date] = _max_dt
    ) * sales[quantity]

[NorbertG]

Thank you! Now it works as it should. I'm really sorry for my mistakes and again I'm really thankful!