Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors