Forum Discussion
Churn Rate Measure
Hey guys,
I’d really appreciate to get some insights/help.
I’ve been struggling for quite some time to get my Churn Rate measure in place. None of the existing posts have helped me so far.
Account Churn Rate:
(Nº of accounts lost during a period)
÷
(Nº of accounts at the beginning of the period)
*The “number of accounts lost during a specific period” can’t include new accounts acquired during that period. In other words, it can’t include new account acquisition.
My goal is having a unique measure to be applied on any period of time desired: month, quarter or year.
Here’s an example of my database:
| Account | Product | Industry | Status | Account Initial Date | Account Final Date |
| 10001 | A | Retail | Churn | March 6, 2014 | March 28, 2016 |
| 10002 | A | Health Care | Churn | November 29, 2016 | December 12, 2017 |
| 10003 | A | Retail | Active | June 26, 2014 | |
| 10004 | A | Education | Churn | July 18, 2013 | August 26, 2014 |
| 10005 | A | Retail | Churn | August 31, 2013 | October 19, 2016 |
The assumption is that you have a proper Date table in the model that's not connected to your Accounts table (Dates). Once this is in place, you can write:
[# Acc Lost] = var __periodStart = MIN ( Dates[Date] ) var __periodEnd = MAX ( Dates[Date] ) var __accCount = CALCULATE( COUNTROWS( Accounts ), Accounts[Account Initial Date] < __periodStart, Accounts[Account Final Date] >= __periodStart, Accounts[Account Final Date] <= __periodEnd ) return __accCount [# Acc at Beginning] = var __periodStart = MIN ( Dates[Date] ) var __periodEnd = MAX ( Dates[Date] ) var __accCount = CALCULATE( COUNTROWS( Accounts ), Accounts[Account Initial Date] <= __periodStart, OR( ISBLANK( Accounts[Account Final Date] ), Accounts[Account Final Date] >= __periodStart ) ) return __accCount [Account Churn] = DIVIDE( [# Acc Lost], [# Acc at Beginning] )
You should not slice by [Account Final Date] and [Account Initial Date]. These fields should be hidden.
Best
Darek
2 Replies
The assumption is that you have a proper Date table in the model that's not connected to your Accounts table (Dates). Once this is in place, you can write:
[# Acc Lost] = var __periodStart = MIN ( Dates[Date] ) var __periodEnd = MAX ( Dates[Date] ) var __accCount = CALCULATE( COUNTROWS( Accounts ), Accounts[Account Initial Date] < __periodStart, Accounts[Account Final Date] >= __periodStart, Accounts[Account Final Date] <= __periodEnd ) return __accCount [# Acc at Beginning] = var __periodStart = MIN ( Dates[Date] ) var __periodEnd = MAX ( Dates[Date] ) var __accCount = CALCULATE( COUNTROWS( Accounts ), Accounts[Account Initial Date] <= __periodStart, OR( ISBLANK( Accounts[Account Final Date] ), Accounts[Account Final Date] >= __periodStart ) ) return __accCount [Account Churn] = DIVIDE( [# Acc Lost], [# Acc at Beginning] )
You should not slice by [Account Final Date] and [Account Initial Date]. These fields should be hidden.
Best
Darek