Forum Discussion
Monthly Recurring Revenue Model-Upsell/Downsell Question
Hello,
I am working on creating a Dashboard that displays monthly MRR and am having trouble creating measures that will give me the correct values at the row/customer level across every month/year.
Every formula seems to work all the way down to the customer level except upsell and downsell of existing customers. Any ideas what I need to do to allow the measure to make it work at the customer level?
The formatting would be :
MRR
New Adds
Ramp
Non-Start
Upsell
(Downsell)
(Churns)
Here are the meassures I have so far. Posting these in case these may be causing some of my issues.
The source file is excel and has the below layout :
| Company Name | Rev Type | Customer Number | Customer Name | Revenue Category | Customer Status | Customer Status Description | Churn Code | Start Date | End Date | Doc Date | Revenue |
| Company 1 | Segment 1 | 123456 | Clinic 1 | Credits | 5 | Billing Adjustment | 0 | 2/28/2022 | 2/28/2022 | 1/31/2017 | 0 |
| Company 1 | Segment 1 | 123456 | Clinic 1 | Credits | 5 | Billing Adjustment | 0 | 2/28/2022 | 2/28/2022 | 2/28/2017 | 0 |
| Company 1 | Segment 1 | 123456 | Clinic 1 | Credits | 5 | Billing Adjustment | 0 | 2/28/2022 | 2/28/2022 | 3/31/2017 | 0 |
| Company 1 | Segment 1 | 123456 | Clinic 1 | Credits | 5 | Billing Adjustment | 0 | 2/28/2022 | 2/28/2022 | 4/30/2017 | 0 |
| Company 1 | Segment 1 | 123456 | Clinic 1 | Credits | 5 | Billing Adjustment | 0 | 2/28/2022 | 2/28/2022 | 5/31/2017 | 0 |
| Company 1 | Segment 1 | 123456 | Clinic 1 | Credits | 5 | Billing Adjustment | 0 | 2/28/2022 | 2/28/2022 | 6/30/2017 | 0 |
| Company 1 | Segment 1 | 123456 | Clinic 1 | Credits | 5 | Billing Adjustment | 0 | 2/28/2022 | 2/28/2022 | 7/31/2017 | 0 |
| Company 1 | Segment 1 | 123456 | Clinic 1 | Credits | 5 | Billing Adjustment | 0 | 2/28/2022 | 2/28/2022 | 8/31/2017 | 0 |
| Company 1 | Segment 1 | 123456 | Clinic 1 | Credits | 5 | Billing Adjustment | 0 | 2/28/2022 | 2/28/2022 | 9/30/2017 | 0 |
| Company 1 | Segment 1 | 123456 | Clinic 1 | Credits | 5 | Billing Adjustment | 0 | 2/28/2022 | 2/28/2022 | 10/31/2017 | 0 |
| Company 1 | Segment 1 | 123456 | Clinic 1 | Credits | 5 | Billing Adjustment | 0 | 2/28/2022 | 2/28/2022 | 11/30/2017 | 0 |
| Company 1 | Segment 1 | 123456 | Clinic 1 | Credits | 5 | Billing Adjustment | 0 | 2/28/2022 | 2/28/2022 | 12/31/2017 | 0 |
| Company 1 | Segment 1 | 123456 | Clinic 1 | Credits | 5 | Billing Adjustment | 0 | 2/28/2022 | 2/28/2022 | 1/31/2018 | 0 |
| Company 1 | Segment 1 | 123456 | Clinic 1 | Credits | 5 | Billing Adjustment | 0 | 2/28/2022 | 2/28/2022 | 2/28/2018 | 0 |
| Company 1 | Segment 1 | 123456 | Clinic 1 | Credits | 5 | Billing Adjustment | 0 | 2/28/2022 | 2/28/2022 | 3/31/2018 | 0 |
| Company 1 | Segment 1 | 123456 | Clinic 1 | Credits | 5 | Billing Adjustment | 0 | 2/28/2022 | 2/28/2022 | 4/30/2018 | 0 |
| Company 1 | Segment 1 | 123456 | Clinic 1 | Credits | 5 | Billing Adjustment | 0 | 2/28/2022 | 2/28/2022 | 5/31/2018 | 0 |
| Company 1 | Segment 1 | 123456 | Clinic 1 | Credits | 5 | Billing Adjustment | 0 | 2/28/2022 | 2/28/2022 | 6/30/2018 | 0 |
| Company 1 | Segment 1 | 123456 | Clinic 1 | Credits | 5 | Billing Adjustment | 0 | 2/28/2022 | 2/28/2022 | 7/31/2018 | 0 |
Logic for Calculations
Total MRR- This would be starting MRR for each month
Added MRR- New customer MRR. Logis is that status is 1 & start date= Current month
Non-Start Adds- Customer Status = 0
Ramp- Change in value (Current Month -Prior Month) for customers who have started in the last two months
Churn- Customers who no longer have MRR. End date= Prior Month
2 Replies
Logic for Calculations
Total MRR- This would be starting MRR for each month
Added MRR- New customer MRR. Logis is that status is 1 & start date= Current month
Non-Start Adds- Customer Status = 0
Ramp- Change in value (Current Month -Prior Month) for customers who have started in the last two months
Churn- Customers who no longer have MRR. End date= Prior Month
- amitchandak
Super User
downsouth22 , based on what I got
I think you should be able to add the variance logic in the measure I created lost, new customer, add an additional condition in retain have value -
refer Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://youtu.be/EyL7KMw877Q
Switch( True(),
Isblank([Previous Months MRR]) && not(isblank([Total MRR])) , "New",
not(Isblank([Previous Months MRR])) && (isblank([Total MRR])) , "Lost",
IF([Variance]>0, "Upsell", "Downsell")
)
Have the bucket on 4 values and you can join and create new measures to coun, as shown in video
