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
Table 2 : Dates
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.
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 :
This shows how many individual customers are transacting in the current month.
This shows how many customers trading in the current month have traded in previous months.
This measure shows how many new customers have traded in the current month for the first time.
This shows how many customer we have lost month to month.
Measure 1 – Customer Count
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.
The measure for Lost Customers can then be simply calculated as :
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.
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.