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

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors