Forum Discussion
Customer Churn Analysis
Hi All,
I'm new to Power BI and got struck while performing Churn analysis on customer data. I need help with Churn Rate analysis for customer in the country has churned 125 days after ordering his last online order.
Can someone help me in DAX Logic, I need this for my project on urgent basis.
For eg:
If we are checking in churn rate in Month of May, 2022
Count of distinct customer who placed an order: 1000
125 Day in past : Jan 2022
Count of distinct customer who placed an order: 2400
Churn Rate: (1000/2400)*100 = 41.67%
This needs to be dynamic
if the month changes it should calculate (End of that Month -125 Days) and use it to calculate Churn Rate.
Thanks in Advance!
Hi Anonymous ,
I'd like to suggest you to create a independent calendar table as slicer.
Please refer this formula to get the count of customer in a specific month selected in the slicer.
count_customer = CALCULATE ( DISTINCTCOUNT ( 'table'[customer] ), FILTER ( ALLSELECTED ( 'table' ), YEAR ( 'table'[date] ) = YEAR ( SELECTEDVALUE ( 'calendar'[date] ) ) && MONTH ( 'table'[date] ) = MONTH ( SELECTEDVALUE ( 'calendar'[date] ) ) ) )And this formula is to get the count of customer in the month of 125 Day ago.
count_customer_125 = VAR _125 = SELECTEDVALUE ( 'calendar'[date] ) - 125 RETURN CALCULATE ( DISTINCTCOUNT ( 'table'[customer] ), FILTER ( ALLSELECTED ( 'table' ), YEAR ( 'table'[date] ) = YEAR ( _125 ) && MONTH ( 'table'[date] ) = MONTH ( _125 ) ) )Then the last measure to get the Churn Rate:
Churn Rate = count_customer/count_customer_125Best Regards,
Jay
4 Replies
Hi Anonymous ,
I'd like to suggest you to create a independent calendar table as slicer.
Please refer this formula to get the count of customer in a specific month selected in the slicer.
count_customer = CALCULATE ( DISTINCTCOUNT ( 'table'[customer] ), FILTER ( ALLSELECTED ( 'table' ), YEAR ( 'table'[date] ) = YEAR ( SELECTEDVALUE ( 'calendar'[date] ) ) && MONTH ( 'table'[date] ) = MONTH ( SELECTEDVALUE ( 'calendar'[date] ) ) ) )And this formula is to get the count of customer in the month of 125 Day ago.
count_customer_125 = VAR _125 = SELECTEDVALUE ( 'calendar'[date] ) - 125 RETURN CALCULATE ( DISTINCTCOUNT ( 'table'[customer] ), FILTER ( ALLSELECTED ( 'table' ), YEAR ( 'table'[date] ) = YEAR ( _125 ) && MONTH ( 'table'[date] ) = MONTH ( _125 ) ) )Then the last measure to get the Churn Rate:
Churn Rate = count_customer/count_customer_125Best Regards,
Jay
Anonymous ,refer
https://blog.ineuron.ai/Customer-Churn-Analysis-5PyOaWdwDF
Or do you need
Customer Retention :I have done something for months here, but the same can be used for days
Customer Retention Part 5: LTD Vs Period Retention
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-5-LTD-and-PeriodYoY-Retention-is-only/ba-p/2114497This topic has been beaten to death. Please refer to this: New and returning customers – DAX Patterns
Hi:
You should have a Date Table, continuous and marked as DATE Table. Let's name it "Dates". This is connected to your Sales Fact Table, we'll call this table "Sales". In sales each customer has a unique ID eg, CustomerID
LOST CUSTOMER =
var custlist = ALL(Sales[CustomerID])
var churndays = 125
Return
COUNTROWS(
FILTER(
custlist,
CALCULATE(COUNTROWS(Sales),
FILTER(ALLSELECTED(Dates),
Dates[Date] > MIN Dates[Date] - churndays) &&
Dates[Date] < MIN(Dates[Date]))) = 0 ))It's key to have your model designed for Fact and Dimension tables like Dates, Customers, Products, etc.
I hope this works. You can just change the churndays variable if you want to see another view. Even the churndays can be made dynamic if needed.
