cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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:

AccountProductIndustryStatusAccount Initial DateAccount Final Date
10001ARetailChurnMarch 6, 2014March 28, 2016
10002AHealth CareChurnNovember 29, 2016December 12, 2017
10003ARetailActiveJune 26, 2014 
10004AEducationChurnJuly 18, 2013August 26, 2014
10005ARetailChurnAugust 31, 2013October 19, 2016
1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

@Anonymous - That worked perfectly.

Thanks for your help!

Anonymous
Not applicable

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

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors