It's nice to see the macro answer and learn something new! I got stuck on this one since I was trying to do it without adding a start of month table and I have very little experience with macros. I was able to solve it without using a macro and without having to manually add a Start of Month table. I had one hard coded filter to remove activity prior to May 2013 so that my results matched the same date ranges shown in the solution. It doesn’t really need to be included to make this work. The 24 month criteria for calculating the numerator (open accounts from prior 24 months) is also hard coded based on the directions. Everything else is dynamic so that if you had accounts older than 24 months they would not be included in the calculation nor would you need to add a record in the Start of Month table to include September 2013. I annotated the best I could while trying to keep the canvas clean. I could have added tool containers, but I need to move on. I chopped it into 2 pictures so that it is legible. I’d be happy to answer any questions on the workflow formulas.
My solution. Watched the webinar on Batch Macros today, so this one was perfect practice :) Pretty pleased I was able to do this one in just a few tools!
Note: The instructions indicate numerator is Open & denominator is Closed, but then results in the start file have it showing calculated as the opposite. In addition, I believe to be accurate with identifying a true "Retention Rate," you'd want to know the # of customers remaining after closed accounts... so wouldn't it be (Open - Closed)/Open? Just a thought. I solved for both in my solution, just in case!
This one was a struggle for me, but finally managed to solve and it looks to be similar to other solutions.
