Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |