MRR Calculation

[mohit19]

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! 

[v-yuezhe-msft]

@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

[mohit19]

@v-yuezhe-msft

Thank you!

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