cancel
Showing results for
Did you mean:
Highlighted
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?
Highlighted
Super User IX

## Re: Churn rate over time

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

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

Proud to be a Super User!