Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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 |
Solved! Go to Solution.
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
@Anonymous - That worked perfectly.
Thanks for your help!
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
User | Count |
---|---|
21 | |
19 | |
12 | |
10 | |
9 |
User | Count |
---|---|
30 | |
25 | |
15 | |
13 | |
10 |