Need help making workflow that recognizes renewals

Question

Hi I need help making a formula that can look at this data and data like it, and be able to recognize that a renewal happened. We are able to see a renewal happened here since the start date of the 2nd order happened a day after with the same company and product, the only problem is that sometimes there are multiple sales orders that would classify as part of the renewal. Is there any way to make a loop or so that checks the end date of the sales order and compares it the start dates of others sales orders in its conc.? The end goal is to find the net new transaction price and quantity caused by the renewal. Sorry if it isn't enough info

Renewal Help.PNG

Cslick · Answer

Yeah the goal is to find the net new transaction price and quantity, so lets say the first price is 90 and its renewed for 100, I would need to have the total be 10. The same would apply for quantity too, a running total for all the renewals in these categories would be awesome!

Amol_Telore · Answer

@Cslick Yes. It does helps. Need one more clarification on the question, in the question you mentioned your goal is to calculate new transaction and quantity if it is renewal. So is it running total if renewal found or you want some formula to be applied..?

Cslick · Answer

Would something like this help?

Renewal Help.xlsx