cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Customer Churn Rate

Hi,

I'm trying to calculate our customer churn rate, and I've already implemented the measures from DAX patterns:
http://www.daxpatterns.com/new-and-returning-customers/

As a next step I'm trying to figure out the best way to calculate a
precice customer churn rate which lead me to this the last formula in this link:
https://engineering.shopify.com/17488468-defining-churn-rate-no-really-this-actually-requires-an-ent...

However, I'm unsure how to how the formula translates accurately into DAX so it will work for dynamic periods, so I was wondering if anyone more experienced in DAX could give it a shot and help me out?

Thanks!
1 ACCEPTED SOLUTION
Anonymous
Not applicable

@v-huizhn-msft;

I solved my issue in the meantime by disregarding the suggested Churn-rate measure and followed another approach, which was more simple but still correct.

 

Used the following measure, which is in Danish:

Churn rate (Måned) = 
VAR
AktiveSidsteMaaned = CALCULATE('Kunde Churn'[Total aktive kunder]; Dateadd(Periode[Dato];-1;MONTH))
RETURN
VAR
DageIMaaned = CALCULATE(COUNTROWS(Periode);VALUES(Periode[Måned]))
RETURN
VAR
NyeKunderIMaaned = [Total aktive kunder] - AktiveSidsteMaaned
RETURN
VAR
KundeDageImaaned = ((AktiveSidsteMaaned * DageIMaaned) + (0,5*NyeKunderIMaaned*DageIMaaned))
RETURN
CALCULATE( DageIMaaned * DIVIDE('Kunde Churn'[Churned Kunder];KundeDageImaaned)
)

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

The easiest and best description I can give is that it's a full sales model, similar to the Adventure works model, with a separate customer, date and sales table, with sales given at a day granularity level, including individual CustomerKey.

So yes, I do have the churn date of the individual customer, based on historical sales data.

I presume I can use the "Lost Customers" from the DAX patterns as my churn, but still need to define total amount of customers at the beginning of the period, which would be all customers that has made purchase within my 'LostDaysLimit'.

You say the model needs to be in a day granularity, but would the calculation change, when you switch between showing the turn rate at a year, quarter, month or week level?
Greg_Deckler
Super User
Super User

If I understand that formula correctly, you need to have the number of customers who churned on any particular day and the number of customers on that particular day. You then divide those numbers and multiply by thirty.

 

So, what format is your data in? Do you have it summarized by day the number of customers and churns? Some sample data would help tremendously, even if just mocked up and simplified.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Anonymous
Not applicable

Hi,

I’m new to Power BI

I was also wanting to work out the drop out rate of my members per month and year as well. I would also like to know the rate of new months per months I have a table as below:

 

Customer ID, Status, End date, Start date, campaign


I have made the following date table:
The status corresponds to the member’s status e.g. Ended, active, break (taking a break and will contacted at a later date or reinstated)

 

Date =

ADDCOLUMNS (

CALENDAR (DATE(2000,1,1), DATE(2025,12,31)),

"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),

"Year", YEAR ( [Date] ),

"Monthnumber", FORMAT ( [Date], "MM" ),

"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),

"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),

"MonthNameShort", FORMAT ( [Date], "mmm" ),

"MonthNameLong", FORMAT ( [Date], "mmmm" ),

"DayOfWeekNumber", WEEKDAY ( [Date] ),

"DayOfWeek", FORMAT ( [Date], "dddd" ),

"DayOfWeekShort", FORMAT ( [Date], "ddd" ),

"Quarter", "Q" & FORMAT ( [Date], "Q" ),

"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )

)

 

I would like to help with create measures as well for drop out rate and new member ratio per marketing campaign activity to measure it’s effectiveness.

 

How can I go about doing this?  Thank you 🙂

Anonymous
Not applicable

Recarding the "total amount of customers" measure, I made this measure based off "Dax Patterns - Time Patterns": http://www.daxpatterns.com/time-patterns/

Total active customers = 
CALCULATE(
	CALCULATE(
			DISTINCTCOUNT(Sales[CustomerKey]);
				FILTER(
					ALL(Period[Date]);
						Period[Date] > MAX(Period[Date]) - 'Customer Churn'[Churn Days]
									&& Period[Date] <= MAX(Period[Date])
							)
						);
				FILTER(Period; Periode[Dat] <= MAX(Sales[InVoiceDate])
				)
)

The 'Customer Churn'[Churn Days] refers to another measure that holds a table that is used to filter the amount of days the users wants to use as our "Churn Definition".

Was wondering if you can confirm it's correct @Greg_Deckler ?

Hi @Anonymous,

Could you please sahre some your sample data for further analysis?

Best Regards,
Angelia

Anonymous
Not applicable

@v-huizhn-msft;

I solved my issue in the meantime by disregarding the suggested Churn-rate measure and followed another approach, which was more simple but still correct.

 

Used the following measure, which is in Danish:

Churn rate (Måned) = 
VAR
AktiveSidsteMaaned = CALCULATE('Kunde Churn'[Total aktive kunder]; Dateadd(Periode[Dato];-1;MONTH))
RETURN
VAR
DageIMaaned = CALCULATE(COUNTROWS(Periode);VALUES(Periode[Måned]))
RETURN
VAR
NyeKunderIMaaned = [Total aktive kunder] - AktiveSidsteMaaned
RETURN
VAR
KundeDageImaaned = ((AktiveSidsteMaaned * DageIMaaned) + (0,5*NyeKunderIMaaned*DageIMaaned))
RETURN
CALCULATE( DageIMaaned * DIVIDE('Kunde Churn'[Churned Kunder];KundeDageImaaned)
)

View solution in original post

hi @v-huizhn-msft  please translate it in english.

Hi @v-huizhn-msft  can you translate it in english? 

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors