Hello,
I have some transactions data (purchases of some products made at a certain date). I need to pull from the data the most recent price paid for each product. That's not hard, but here are a couple of caveats:
1. there can be multiple transactions on the same date for the same product --> in that case, let's just take the average. I'd use the Summary tool: Group by Product, Max Invoice Date, Average Quantity, Average Total Cost. Then Formula (Avg_Total Cost / Avg_Quantity) to return the Unit Cost.
2. in these multiple transactions on the same day, there could be some odd ones that we need to ignore from the average calculation (see line in red: someone fat-fingered the total cost). Here, I'm a bit stuck and I'd love some help. Any suggestions?
Thank you all for your help.
