topic Re: Find purchase price based on sales date (first price before date) in DAX Commands and Tips

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

Anonymous — Mon, 21 Aug 2023 10:31:24 GMT

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

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

Semaj06 — Mon, 21 Aug 2023 10:41:43 GMT

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)

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

Anonymous — Mon, 21 Aug 2023 11:17:27 GMT

Hi Semja,

Unfortunatly both solutions give the same wrong result.

Regards

Michel

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

Semaj06 — Mon, 21 Aug 2023 10:53:37 GMT

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)

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

Anonymous — Mon, 21 Aug 2023 11:29:02 GMT

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