I’m having difficulty calculating a comparison I’ve been asked to create.
Specifically it is what % of each donation group is cancelling over the past 7 days. The concern is whether or not the more valuable customers are cancelling in greater numbers.
For example, in the past 7 days we lose 10 customers from the ‘25 plus’ group. At the beginning of those 7 days there were 100 customers giving who were in the ‘25 plus’ group. Therefore, the % lost in this donation group is 10%. How does this compare to the previous 7 days? Is the trend getting worse etc?
I have calculated the donation group for each customer from my sales table. The groups are:
For the current 7 days I can calculate the number of donors in each group we’ve lost but how do I determine how many there were at the beginning of the 7 days? Is it just (those lost + those left)?
Then for the previous 7 days I’d need to recalculate the donation count for each customer based on what date block it is and when they started giving and if they stopped - I have both dates.
Any guidance would be much appreciated. If my description is unclear please let me know.
The PBIX is attached. Sign Ups & Cancellations.pbix (3.4 MB)