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
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.