cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver I
Resolver I

Churn rate over time

I want to calculate the churned MRR/MRR, and see this over time. The problem is that the churned MRR and MRR are connected to two different date tables (lost date and won date). Therefore I cannot see the churn rate results over time. 

 

I define the won/lost mrr as follows:

 

Total Won MRR = CALCULATE(SUM(Deals[Deal - Value]),FILTER('Deal Status','Deal Status'[Deal - Status]="Won"))
Total Lost MRR = CALCULATE(SUM(Deals[Deal - Value]),FILTER('Deal Status','Deal Status'[Deal - Status]="Lost"))
Churnrate = [Total Lost MRR]/[Total Won MRR]

 



Some sample data, sales table:

Client   Won Date      Lost Date      Status    Value
A          2019-08-10                         Live         $1000
B          2019-10-01   2019-11-30    Lost        $500
C          2019-08-10                         Live         $1000
D          2019-10-01                         Live        $500
 
Desired churnrate = $500/$2500

I have two date tables, a 'lost dates' table and a 'won dates' table. Relationships are as follows:

Lost dates Table           Sales Table

Date                    1 : *   Lost date

 

Won dates Table              Sales Table

Date                       1 : *    Won date

How do I make the date table connected to both values?
1 REPLY 1
Highlighted
Super User IX
Super User IX

Re: Churn rate over time

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors