cancel
Showing results for
Did you mean:
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)
)```
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?
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.

Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
Check out my latest book!

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 =

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 ?

Microsoft

Hi @Anonymous,

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)
)```
Helper V

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

Helper V

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

Announcements