cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Super User

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
Super User

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

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
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 310 members 2,829 guests
Please welcome our newest community members: