Fetch Cost Price based on Sales Date

Hi everyone,

I receive cost price lists from a factory every six months (or more, occasionally) in an Excel file. I also have an output from our accounting software with the sales per order. I imported both to PowerBI and want to connect them in order to analyze the margins on every order, article, period, etc.

In order for me to view the margin on our sales correctly, I need to add a column to my sales table that fetches the correct cost price, based on the cost price table. Without this column, any views of sales will have a cost price of 0 (unless they happened on the exact day that cost prices go in, which is the first of April or October usually).

Searching online, I found the below code to add this column:

Cost Price = 
VAR CostPrice =
    CALCULATETABLE (
        CALCULATETABLE (
            '000_ImportAVK',
            FILTER (
                ALL ( '300_Dates' ),
                '300_Dates'[Date] <= MAX ( '300_Dates'[Date] )
            )
        ),
        FILTER ( '000_ImportAVK', '000_ImportAVK'[Article Code] = '100_Sales'[Article Code] )
    )
VAR Maxdate =
    CALCULATE ( MAX ( '300_Dates'[Date] ), CostPrice )
    RETURN
    CALCULATE (
        MAX ( '000_ImportAVK'[Cost Price] ),
        '300_Dates'[Date] = Maxdate,
        CostPrice
    )

However, this only gives me the most recent cost price for every order, even if that order is from an earlier date. Is there any way in which it is possible to show the actual cost price of that date?

Any help is appreciated greatly.