cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Regular Visitor

## 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

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 :

1 ACCEPTED SOLUTION
Regular Visitor

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 2
Regular Visitor

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

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