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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
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)
)

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.