How to find a valid purchase price according to posting date

[Matt78]

Hi all,

I have 2 lists here, the one on the left is sales and the other one is a list of purcahse prices. As you can see, the prices changes some times and my issue here is that I would need to get a valid prices on the sales matrix whatever is valid on the certain posting date. I think I may have something wrong with my DAX because it leaves out July prices:

Purchace price =

var postingdate = max('Value Entries'[Posting Date])

var item_ = MAX('Value Entries'[Item No])

return

CALCULATE(MAX('Purchase Prices'[Price]), filter('Purchase Prices', 'Purchase Prices'[Ending Date] >= postingdate && 'Purchase Prices'[Starting Date] <= postingdate && 'Purchase Prices'[Item No] = item_))

Thank you for the help

Kind regards

Matti

[Ahmedx · Accepted solution]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

https://1drv.ms/u/s!AiUZ0Ws7G26RiVzXTf7jI-hR6uCU?e=LI5Gso

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

[Matt78]

Thank you for the quick solution, this was exactly what I was looking for 🙂

br

Matt

[Ahmedx]

Can you please share your demo input and expected output!

[Matt78]

Hi,

Purchase price in the sales list should be 3,24 if posting date is 1.7. onwards, now it is empty.

br

Matt