cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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 November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors