Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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 tryi...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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)
)
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
9 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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 ?