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

MRR Churn calculation

 

 

Hello! 

I have a simple model with the following structure: 

Date | Subs | Income

 

I have the following code to calculate MRR for new customers. 

MRRNew = 
IF(
	ISFILTERED('transactions'[Date]);
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column.");
	VAR __CURRENT_VALUES = SUMMARIZE(VALUES('transactions'); 'transactions'[Subs])
	VAR __EXISTING_VALUES =
		CALCULATETABLE(
			SUMMARIZE(VALUES('transactions'); 'transactions'[Subs]);
			FILTER(
				ALL('transactions'[Date].[Date]);
				'transactions'[Date].[Date] < MIN('transactions'[Date].[Date])
			);
			KEEPFILTERS(__CURRENT_VALUES)
		)
	RETURN
		CALCULATE(
			SUM('transactions'[Income]);
			EXCEPT(__CURRENT_VALUES; __EXISTING_VALUES)
		)
)

 

 

Some additional info: 

MRR -  The Monthly Recurring Revenue at the end of each month. I can calculate it using SUM('transactions'[Income])

 

Churned MRR - The lost MRR from churning customers in the current month.

 

I wonder if anyone could help me to calculate churn MRR. As I understand I need to get all subscriptions from the last month and exclude subscriptions from the current month, but I cannot figure out how to do this correctly. 

 

 

Sample pbix file: 

https://cp.sync.com/dl/a1dac0e10#43rernz3-j8gp26nb-xtq47nde-sukfp6pz

 

 

 

 

 

4 REPLIES 4
Super User IV
Super User IV

Re: MRR Churn calculation

Sample data and expected results would help tremendously. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

That being said, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

 

It should help you get the values for current month and last month. Also, you might be looking for some time interval stuff, you can find that here:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User III
Super User III

Re: MRR Churn calculation

Hi @rflipper 

 

If the measure that you show is correct (I haven't checked it thoroughly) the MRR for churned customers is almost the same, you just have to invert the arguments in the final EXCEPT:

EXCEPT( __EXISTING_VALUES;__CURRENT_VALUES)

with these you'd be taking the customers present last month that are not present this month. That's the churners as you very well have said. I'd probably more self-explanatory names for the vars though. 'Existing' and 'Current' values are a bit confusing but well that's actually a matter of taste.

 

MRRNew = 
IF(
	ISFILTERED('transactions'[Date]);
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column.");
	VAR __CURRENT_VALUES = SUMMARIZE(VALUES('transactions'); 'transactions'[Subs])
	VAR __EXISTING_VALUES =
		CALCULATETABLE(
			SUMMARIZE(VALUES('transactions'); 'transactions'[Subs]);
			FILTER(
				ALL('transactions'[Date].[Date]);
				'transactions'[Date].[Date] < MIN('transactions'[Date].[Date])
			);
			KEEPFILTERS(__CURRENT_VALUES)
		)
	RETURN
		CALCULATE(
			SUM('transactions'[Income]);
			EXCEPT( __EXISTING_VALUES; __CURRENT_VALUES )
		)
)

 

 

rflipper Frequent Visitor
Frequent Visitor

Re: MRR Churn calculation

Highlighted
rflipper Frequent Visitor
Frequent Visitor

Re: MRR Churn calculation

@AlB Unfortunately this doesn't work, I suppose because of incorrect filter or sum function

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors