Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
downsouth22
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

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 NameRev TypeCustomer NumberCustomer NameRevenue CategoryCustomer StatusCustomer Status DescriptionChurn CodeStart DateEnd DateDoc DateRevenue
Company 1Segment 1123456Clinic 1Credits5Billing Adjustment02/28/20222/28/20221/31/20170
Company 1Segment 1123456Clinic 1Credits5Billing Adjustment02/28/20222/28/20222/28/20170
Company 1Segment 1123456Clinic 1Credits5Billing Adjustment02/28/20222/28/20223/31/20170
Company 1Segment 1123456Clinic 1Credits5Billing Adjustment02/28/20222/28/20224/30/20170
Company 1Segment 1123456Clinic 1Credits5Billing Adjustment02/28/20222/28/20225/31/20170
Company 1Segment 1123456Clinic 1Credits5Billing Adjustment02/28/20222/28/20226/30/20170
Company 1Segment 1123456Clinic 1Credits5Billing Adjustment02/28/20222/28/20227/31/20170
Company 1Segment 1123456Clinic 1Credits5Billing Adjustment02/28/20222/28/20228/31/20170
Company 1Segment 1123456Clinic 1Credits5Billing Adjustment02/28/20222/28/20229/30/20170
Company 1Segment 1123456Clinic 1Credits5Billing Adjustment02/28/20222/28/202210/31/20170
Company 1Segment 1123456Clinic 1Credits5Billing Adjustment02/28/20222/28/202211/30/20170
Company 1Segment 1123456Clinic 1Credits5Billing Adjustment02/28/20222/28/202212/31/20170
Company 1Segment 1123456Clinic 1Credits5Billing Adjustment02/28/20222/28/20221/31/20180
Company 1Segment 1123456Clinic 1Credits5Billing Adjustment02/28/20222/28/20222/28/20180
Company 1Segment 1123456Clinic 1Credits5Billing Adjustment02/28/20222/28/20223/31/20180
Company 1Segment 1123456Clinic 1Credits5Billing Adjustment02/28/20222/28/20224/30/20180
Company 1Segment 1123456Clinic 1Credits5Billing Adjustment02/28/20222/28/20225/31/20180
Company 1Segment 1123456Clinic 1Credits5Billing Adjustment02/28/20222/28/20226/30/20180
Company 1Segment 1123456Clinic 1Credits5Billing Adjustment02/28/20222/28/20227/31/20180

 

downsouth22_3-1684936467863.png

 

1 ACCEPTED SOLUTION
downsouth22
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

 

 

 

View solution in original post

2 REPLIES 2
downsouth22
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

 

 

 

@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 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.