Forum Discussion
Customer Churn Rate
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-entire-blog-post
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!
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) )
9 Replies
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.
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
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 :)
- 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? Hi there, another option is to explore available third-party connectors. There’s a ready-made solution that provides a free Shopify Power BI template with the essential Shopify tables and predefined relationships, allowing you to connect your data and start quickly: https://vidi-corp.com/shopify-analytics-dashboards/
It’s a helpful shortcut if you’re not experienced with Shopify data modeling or simply want to accelerate the setup process.
