Part of a scenario I need to model requires that I take when a set of customers joined, and place an effect on that number in a date-relative way. For example, the number of customers that join in Apr 19 need to get charged 25% of a fee in their first month, 50% in their second month, and 100% in their 3rd month. The number of customers that join in May 19 will have the exact same pattern (relative to their date of joining. The relative pattern will be the same for all months, but which relative month and the % assumptions will be changed constantly, to model various scenarios. The data results need to be part of a longer list of pivot data, which will be used for various slicing/dicing. In the attached, I've extracted a small bit of this, showing the following:
- few lines of customers joining, and varying amounts of them
- shaded area showing where the changes will be made to a) which relative month and b) how much will be charged in that relative month
the "?" under amounts column are the numbers I need. I have put in comments in the first two cells, explaining what that result SHOULD be. Now I just need the combination of interim pivot table and formula that will give me a dynamic situation where the assumptions will change.
I'm envisioning some combination of the actual date (the month for which I want to calculate the charge), and some sort of 'join date' (the month a set of customers joined), and summation of each joining month's relative amount. Obviously one constraints is that in something like the May 19 entry, I can't have any effect from customers that join in Jun 19 or later (because they'll not have joined yet).
Hope this all makes sense. If not, happy to explain further, if told what's unclear.
