topic Churn Rate Measure in DAX Commands and Tips

Churn Rate Measure

Anonymous — Fri, 26 Jul 2019 18:32:58 GMT

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:

Account	Product	Industry	Status	Account Initial Date	Account Final Date
10001	A	Retail	Churn	March 6, 2014	March 28, 2016
10002	A	Health Care	Churn	November 29, 2016	December 12, 2017
10003	A	Retail	Active	June 26, 2014
10004	A	Education	Churn	July 18, 2013	August 26, 2014
10005	A	Retail	Churn	August 31, 2013	October 19, 2016

Re: Churn Rate Measure

Anonymous — Fri, 26 Jul 2019 22:36:49 GMT

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

Re: Churn Rate Measure

Anonymous — Mon, 29 Jul 2019 21:33:09 GMT

Anonymous - That worked perfectly.

Thanks for your help!