Monthly Recurring Revenue Model-Upsell/Downsell Question

[downsouth22]

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.

Total MRR = SUM('Cohort Flat File'[Revenue])

Previous Months MRR = CALCULATE([Total MRR],PARALLELPERIOD('Date Table'[Date],-1,MONTH))

Added Revenue = CALCULATE(SUM('Cohort Flat File'[Revenue]), 'Cohort Flat File'[Start Date] = 'Cohort Flat File'[Ending Date])

Ramp = CALCULATE(SUM('Cohort Flat File'[Revenue]),'Cohort Flat File'[Aging] <=2,  'Cohort Flat File'[Aging] >0 , 'Cohort Flat File'[Customer Status] =1, 'Cohort Flat File'[Doc Date]='Cohort Flat File'[Ending Date])

Non-Start Revenue = CALCULATE(SUM('Cohort Flat File'[Revenue]), 'Cohort Flat File'[Customer Status]=0)

Upsell metric = IF([Variance]>0, [Variance], BLANK())

Downsell metric = IF([Variance]< 0, [Variance], BLANK())

Variance = [Total MRR]-[Previous Months MRR]

Churn = CALCULATE(SUM('Cohort Flat File'[Revenue]), 'Cohort Flat File'[End Date] = EOMONTH('Cohort Flat File'[Ending Date],-1))

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

[downsouth22 · Accepted solution]

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]

@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