I need to the latest / most recent non blank selling price and cost price as per customer and item name.
I had used this calculation for a calculated column as suggested
RECENT DATE = IF(CALCULATE(MAX(Table1[Date_]),ALLEXCEPT(Table1,Table1[Item Name],Table1[Customer Name]))=Table1[Date_],"Y")
This formula works and I was able to filter by dept.
But i require the last non-blank value of the selling price and another issue with this formula is that if there are 3 transactions done for that day it sums the selling price of all 3 transactions rather than giving me a single amount.
For eg. the most recent transaction of bbq sauce (in the given data) is on 10/4/23 but selling price is blank so i require the last time selling price was not blank for bbq sauce by customer and item.
I have uploaded the data and the desired result.
https://docs.google.com/spreadsheets/d/1vaM-SyZSevNx5oUkvk3xwivwB-J_KuPZ/edit?usp=sharing&ouid=10407...
