Fetch Cost Price based on Sales Date

[QPV]

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.

[amitchandak · Accepted solution]

@QPV , Assume Cost has cost price with date, item  and sales has sales date ans item

then a new column in sales table

=

var _max = maxx(filter(Cost, cost[Item] = sales[Item] && cost[Cost Date]<= sales[Sales Date]), COst[Cost Date])

return
maxx(filter(Cost, cost[Item] = sales[Item] && cost[Cost Date]=_max), COst[Cost Date])

Change as per need

[QPV]

Dear @amitchandak,

Is something like this possible for reports that use DirectQuery as well? I am trying to recreate the report with DirectQuery so I don't have to manually refresh and re-upload the report for my viewers.

Thanks!

[QPV]

Thank you very much, this worked!