cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mohit19
Frequent Visitor

MRR Calculation

There are five tables in my database:

Bill, Subscription, User, Credit card & Sites

 

In Subscription table, there is "type" column which contains below values:

  1. Free (free)
  2. Free plus (free)
  3. Pro Comped (free)
  4. Pro (paid = $15/mo)
  5. Growth (paid = $29/mo)
  6. Enterprise (paid = $99/mo)
  7. Pro Managed (paid = $custom)

Above in increasing order of price of subscription starting from Pro.

 

Over time our users have paid the varying amount within these subscriptions type. There is a “schedule” field where we tell you if it is a monthly or yearly plan.

 

You will also need to note that Bills have a column called “status.” The different statuses that a bill can have are:

  1. Pending(this amount is considered as zero)
  2. Paid(+)
  3. Voided(this amount is considered as zero and end of subscription)
  4. Failed(this amount is considered as zero)
  5. Refunded (negative value)
  6. Charged back (negative value)

 For the amount, you can refer column "amount" in bills table.

 

Required Calculation: 

 

  1. Expansion MRR: Additional MRR from existing site that have upgraded This is when a site that is already on a paid subscription and upgrades to a higher paid subscription. For example, when a user that is on a Pro Subscription ($15/mo) and then upgrades to an Enterprise Subscription ($99/mo). You can refer to column "Actual Amount" in Bills table, where amount is set according to status.
  2. Reactivation MRR: MRR from a site that had previously been paying, stop paying and then started paying again. For example, if a user was on a paid Growth subscription ($29/mo) for 6 months, then stopped paying for 6 months and then started paying for a Growth Subscription again.
  3. Contraction MRR: MRR lost from downgrades This is when a site that is already on a paid subscriptions downgrades to one of our lower paid subscriptions. For example, if a site that is on an Enterprise Subscription ($99/mo) downgrades to a Pro Subscription ($15/mo).
  4. Churned MRR: MRR lost from cancellations - You will find this by looking for sites that had a bill during the previous cycle and no longer have one for this month. Note: for annual plans, the user will potentially not had a bill for 11 months previously but if they do not pay again, they are considered churned.

 

Please find the .pbix file link:

https://www.dropbox.com/s/36y2nf2mf8cgb2u/Test17Sep.pbix?dl=0

Please Guide

Thanks in advance! 

 

 

2 REPLIES 2
v-yuezhe-msft
Microsoft
Microsoft

@mohit19,

About first and third requirement, create the following column in bills table. About the other requirements, do you want to drag a user list or site list? Do you want to calculate the result based on static months(6 months and 11 months) by evaluating user's subscription or site's subsscription? Please post expected result in table format based on sample data in your PBIX file.

subscriptionid = RELATED(subscriptions[subscriptionid])


Then create a new table using dax below.

Table = CALCULATETABLE(bills,FILTER(bills,bills[subscriptionid]>=4 && (bills[status]="paid"||bills[status]="voided")))


Create the following columns in the new table.

bill_end pf per subscription = CALCULATE(MAX('Table'[bill_at]),ALLEXCEPT('Table','Table'[Site id],'Table'[subscriptionid]))
index = RANKX(FILTER('Table','Table'[Site id]=EARLIER('Table'[Site id])),'Table'[bill_end pf per subscription],,ASC,Dense)
pre = var prev=CALCULATE(FIRSTNONBLANK('Table'[subscriptionid],1),FILTER('Table','Table'[Site id]=EARLIER('Table'[Site id])&&'Table'[index]=EARLIER('Table'[index])-1)) return IF(ISBLANK(prev),'Table'[subscriptionid],prev)
checkupgradeOrDowngrade = IF('Table'[subscriptionid]>'Table'[pre],"upgrade",IF('Table'[subscriptionid]<'Table'[pre],"downgrade",BLANK()))


For more details, please review attached PBIX file.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yuezhe-msft

Thank you!

For other requirements, I need it by site list, no it should not be based on static months.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.