Forum Discussion
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
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-TITHW/m-p/434008It 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/409365Hi 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 ) ) )
