Calculating the Churn Rate based on retained and churned customer

DAX DAX Calculations
1

Dear Experts

I’m very interested in calculating Customer Life time value (CLTV) form the Cohort analysis showcase available on youtube:

I started creating the needed DAX formulas to calculate (CLTV), For more information about the formulas please refer to this (2) links:
1-https://blog.hubspot.com/service/how-to-calculate-customer-lifetime-value
2-For calculate customer life-span: https://www.quora.com/How-do-you-calculate-customer-lifespan

According to the information of the 2 references links above , I did complete preparing all the ingredients, In order to calculate the Churn Rate%, After calculating the (Retained Customers) and then (Churned Customers) , Churn Rate% = 1-((Retained Customers)/(Churn Customers))

image
image826×99 15 KB

Based on the steps I described above I have some doubts is the calculation of the Churned Rate% steps are correct?

Note: I attached the reource on the following link:

All the calculation measures available on the resources.

2

Hi @MAAbdullah47
Based on the formula you have provided, you can compute the yearly churn rate simply. Please take a look.

SimpleChurnRate = 
VAR CustomerLastYear = COUNTROWS ( FILTER ( Customers,[Sales LY] > 0 ) ) 
VAR CustBothYears = COUNTROWS ( FILTER ( Customers,[Sales LY] > 0 
&&[Total Sales] > 0 ) ) 

RETURN DIVIDE ( CustBothYears, CustomerLastYear, 0 )

I hope this helps you

best,

DJ

1 Like
3

First Thank you @diego for your solution, it works , But I need to know why this important measure didn’t mentioned by Sam in the Cohort Analysis summit, I belive it is the most Important one , this a note I hope Sam take it as a suggestion.

1 Like
4

Hi @MAAbdullah47, It’s great to know you made progress with your query. Just a friendly reminder, if your original question has been answered within the forum it is important to mark your thread as ‘solved’. If you have a follow question or concern related to this topic please start a new topic. More details can be found here - Asking Questions On The Enterprise DNA Support Forum.

5

I did On your post I checked the solution box is that enough?

6

@MAAbdullah47,

Thanks for marking it as the solution. That’s all that was needed.

Thanks
Jarrett

1 Like