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?