How to Determine When Returned Orders Have Been Fulfilled
I'm struggling with how to handle this problem conceptually. My company delivers our products to customers and in the delivery tables, there's a field for Units Ordered, Units Delivered, Units Accepted, and Units Returned. This table is aggregated by month so the dates are always for the first of the month for the customer order for each row and orders can be delivered over the course of many months. So when there is a Return in one month, the first units delivered and accepted would replace the return before becoming billable units again. Management wants a report to tell them when a return has been fulfilled. The challenge is that sometimes the full return amount is not fulfilled the following month and may take a few months to fully complete.
I have tried tons of ways over the last two weeks and there's some kind of mental block on this one. At this point, I'm sure I'm overthinking this, and I would appreciate any ideas on how best to figure out if/when a return amount has been fulfilled (I'm attaching a sanitized sample of the data I'm working with).