Find purchase price based on sales date (first price before date)

[Power_BI_Dev9]

Good morning,

I’ve been struggling for one DAX solution I can’t figure out how to write it. 

I’m trying to find the latest purchase price based on the salesdate so I can calculate the Margin of a product on a given date.

I'm getting close with:

Purchase price calculated = calculate (

    MAX(Purchase_Table[Purchase_Price]),

    filter ( ALLEXCEPT(Purchase_Table , Purchase_Table[Product]),

    Purchase_Table[Date] <  min ( Sales_Table[Date])

    && Purchase_Table[Date] = LASTDATE(Purchase_Table[Date])))

I know that the last filter is incorrect, but I'm trying to get the last date in the selection. It now gives me the following result:

As you see, the Purchase_Price_Calculated on 31-jan should be 49, however it's showing 56. It's not giving the last date in the filter. 
I'm missing the final piece in this puzzle.

Any help will be appreciated.

Michel

[Power_BI_Dev9 · Accepted solution]

I was able to solve it myself with the following code:

var maxdate = CALCULATE(LASTDATE(Purchase_Table[Date]), FILTER(ALLEXCEPT(Purchase_Table, Product_table[Product]),

Purchase_Table[Date] < MIN(Sales_Table[Date])))

var Purchaseprice =

calculate (

    MAX(Purchase_Table[Purchase_Price]),

    filter ( ALLEXCEPT(Purchase_Table , Purchase_Table[Product]),

    Purchase_Table[Date] <  min ( Sales_Table[Date])

    && Purchase_Table[Date] =  maxdate))

    return Purchaseprice

[Semaj06]

Purchase price calculated = 

var maxdate=selectedvalue(Sales_Table[Date])

var searchproduct=selectedvalue(Sales_Table[Product])

Return

calculate (

    MAX(Purchase_Table[Purchase_Price]),

     Purchase_Table[Product]=searchproduct&&

    Purchase_Table[Date] <  maxdate)

[Semaj06]

HI

Try this

Purchase price calculated = 

var maxdate=selectedvalue(Sales_Table[Date])

Return

calculate (

    MAX(Purchase_Table[Purchase_Price]),

    filter ( ALLEXCEPT(Purchase_Table , Purchase_Table[Product]),

    Purchase_Table[Date] <  maxdate)

[Power_BI_Dev9]

Hi Semja,

Unfortunatly both solutions give the same wrong result.

Regards

Michel