Customer Churn

A common business request is to report on Customer Churn, that is to show the movement in customers from one period to the next.

To do this with Excel involves massive data loads and complex formula that need to be frequently updated.

I have created a data model using PowerPivot to show how we can use DAX expressions to perform the calculations.

The workbook is attached to this blog post, however, if you want to re-create the example complete the following steps:

1. Create 2 tables

Table 1 titled : Sales

Sale Table

Table 2 : Dates

Date Table

Load the 2 tables to Power Pivot as linked tables

After you’ve loaded the tables you’ll need to create a relationship between the Sale table and the Date table.

Relationship Window

Once the data is present and the relationship is created you should create a single Pivot Table.

Our objective here is to calculate measures to show how the customer numbers have churned (or changed) month to month.

The reported measures we’ll create will be for :

Customer Count

This shows how many individual customers are transacting in the current month.

Returning Customers

This shows how many customers trading in the current month have traded in previous months.

New Customers

This measure shows how many new customers have traded in the current month for the first time.

Lost Customers

This shows how many customer we have lost month to month.

Measure 1 – Customer Count

=COUNTROWS(distinct(Sales[CustomerID]))

The Distinct function takes in a column of values and returns the distinct or unique values in the column.
We then count the rows in this column to return the number of unique customer ID’s.

Measure 2 – Returning Customers

=IF(
CALCULATE(COUNTROWS(), DATEADD(FIRSTDATE(Dates[Date]), -1,Month))=0
, BLANK()
, CALCULATE(
COUNTROWS(DISTINCT(Sales[CustomerID]))
,DISTINCT(Sales[CustomerID])
, DATESBETWEEN(Dates[Date]
,BLANK()
, DATEADD(FIRSTDATE(Dates[Date]), -1, Month)
)))

Read the blog post by Chris Webb at for a good discussion of this calculation.

Measure 3 – New Customers

In order to calculate this measure we can take the current month customer count (measure 1) and deduct the figures for returning customers.

=COUNTROWS(distinct(Sales[CustomerID])) // This gives us a count of the individual customers in the current month

-

IF(
CALCULATE(COUNTROWS(), DATEADD(FIRSTDATE(Dates[Date]), -1, MONTH))=0
, BLANK()
, CALCULATE(
COUNTROWS(DISTINCT(Sales[CustomerID]))
,DISTINCT(Sales[CustomerID])
, DATESBETWEEN(Dates[Date]
,BLANK()
, DATEADD(FIRSTDATE(Dates[Date]), -1, MONTH)
))) //This is a copy of Returning Customers measure from above.

Measure 4 – Lost Customers

To calculate the Lost Customers measure we can take the previous period Customer Count and deduct the Returning Customers from the current month.  The balance gives us the number of customers lost.

I’ve used an intermediate measure called PrevMonthCustomer.

=calculate(countrows(distinct(Sales[CustomerID])),DateAdd(Dates[Date],-1,month))

The measure for Lost Customers can then be simply calculated as :

[PrevMonthCustCount]-[Returning Customer]

We need to wrap this in an IF function that checks if the current month customer count is blank.

=if(
isblank(COUNTROWS(distinct(Sales[CustomerID]))),
BLANK(),
[PrevMonthCustCount]-[Returning Customer]

)

This concludes the measures required.    All you need to do is drop them onto the Pivot Table and add the Date or Month to the rows.

See the complete report below along with a check table I used to verify the numbers.

Completed Report

This model is not yet complete as it currently only works when the sales data is at month granularity with the first sale reported on the first of each month.

Additionally, we have issues if the Customer leaves but then returns in a future month.

Why not have a go a solving these limitations. You will learn far more about DAX by solving problems.

Feel free to comment.

About Lee Hawthorn

Data Professional
This entry was posted in Power Pivot and tagged , , . Bookmark the permalink.

2 Responses to Customer Churn

  1. Michael says:

    If you already have the month in your data do you need the Dates table? Has there been any progress on discovering if a customer leaves but then returns

  2. Michael says:

    Have you made any progress discovering the rejoined customers?. Also if I have a month column in my data do i need the dates table.

Leave a Reply