Customer Retention in Power BI: DAX Measures

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

Customer retention is one of the most common scenarios of data analysis, which is very useful for business. As a business, you would like to know how many new customers you have in each month, how many returning,  and how many lost customers. Knowing these, would help you to focus on the growth plan, and bring more new customers, keep more returning customers, and reduce the number of lost customers. I have written previously about a simple way to find out which customers are new, or lost. However, in this post, I am going to share more details about some other analysis that can be useful, especially when you want to analyze the customer retention for each period (when the date is an axis of the chart).

Customer Retention Over Period

It is useful to know if a customer is new, lost or returning right now, which is what I have explained here:

2016-11-22_09h07_06

However, it is even more useful to know that at which period that customer became a new, lost, or returning customer:

The visual above is doing the calculation of new, lost, and returning customers for each period.

the data model for this sample dataset is like this:

Below is a list of expressions that are useful for this analysis.

Staring Easy: This period’s Customers

Getting this period’s customers, is a simple discount count as below:

this is just showing how many unique customers we have in each period (active customers who made a purchase in this period)

Running Total Customers: All Customers Until This Period

Another useful calculation for customers is to find out the total of customers from the beginning of this business until now. regardless of the fact that they are active or not. This is a running total calculation, which you can achieve either using quick measures or writing the statement like below;

The reason for using IF statement and checking the sales value is that if there is no sales in this period, we consider it as a month that nothing yet happened (these are probably months in the future in our date table), we are not interested to do our calculation for those months.

Running Total Sales

In addition to the running total of the count of customers, it is also good to have the running total of sales. the Sales Measure itself has this calculation:

and the running total sales comes as below;

This is a measure that we use in calculating the count of new, lost, and returning customers.

New Customers – Fixed Period

for calculating the new, lost and returning customers. I first, explain how it works with a fixed period. Let’s say we consider a customer new, if that customer purchased everything in his/her transaction history only the current active period, and let’s say the active period is one year (fixed). So, if there is a customer who purchased in total (up until that given date) $250, and in the period of 1 year from the given date in the table, it is also $250, then that customer is new in that period, otherwise, not. Here is one of the ways you can calculate it:

The code above is creating a virtual table with two new columns. the table has one row per customer (because it is created on the basis of DimCustomer, and has one column for the customer’s total sales, and another one for the sales until now, which means the sales until the given date in the context of the table visual. The return result is filtered for those records that their total sales are similar to their sales until now.

my sample dataset has most of the customers only purchased in one period. However, further down in the screenshot above, you can see that there are customers who purchased in multiple periods, so the count of this period’s customers is not always exactly similar to the New Customers –  Fixed Period.

Returning Customers – Fixed Period

if the sales amount coming from a customer in the last active period is less than the total sales of that customer, then that customer is returning. It means the customer is not purchasing in this period for the first time, the customer purchased in prior periods too.

You might think, whey we don’t create the _customer variable above as a calculated table instead? the reason is that if you do it as a calculated table, then the calculation is done at the time of refresh, and the date of the table visual won’t impact the result. For this type of calculation, especially if the period is not fixed (further down in this article) it is better to do calculations dynamically.

Lost Customers – Fixed Period

You can calculate Lost customers from the three prior measures: [Running Total Customers] – [New Customers – Fixed Period] – [Returning Customers – Fixed Period]. However, sometimes, you might just need New customers and Lost customers. So better to have an independent calculation for the Lost customers itself.

A customer is lost that has no sales transaction in the last active period (one year from the given date) but had sales transactions up until that date (running total sales).

The measure above using in a visual as below:

Making the Period Flexible; What If Parameter

You can also make the period flexible using the What-If parameter for Period. let’s say in this example, period is the number of months;

This will create a calculated table with a Period Value measure as the selected number of months. Now, this can be used in a calculation like below examples.

New Customers – Flexible Period

The only change in the below expression is the use of the [Period Value] measure and changing the interval to month.

Returning Customers – Flexible Period

Lost Customers – Flexible Period

Results

As a result, now I have flexible customer retention by each period, which can be used in charts and table visual and any other visual by date:

Download Sample Power BI File

Download the sample Power BI report here:

Enter Your Email to download the file (required)

Video

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

4 thoughts on “Customer Retention in Power BI: DAX Measures

  • Dear Raza,

    Thank you for a very helpful post, When I am trying to create a running sales and customers using DAX variable getting below date error. what is [Date] referring in the query.

    Column reference to ‘FullDateAlternateKey’ in table ‘DimDate’ Cannot be used with a variation ‘Date’ because it does not have any.

    Running Sales_Var =
    var _currdt=MAX(DimDate[FullDateAlternateKey].[Date])
    return
    CALCULATE(
    [Sales],
    FILTER(
    ALLSELECTED(DimDate[FullDateAlternateKey].[Date]),
    ISONORAFTER(DimDate[FullDateAlternateKey].[Date], _currdt, DESC)
    )
    )

    • .[Date] should be only used for scenarios that you are using the Auto time intelligence of Power BI feature.
      if you are not using that, then remove “.[date]” in any of those expressions
      Cheers
      Reza

  • Hi Reza,

    very nice post, thank you! I am using modified patterns of The Italians but I really like the flexible version, as I am showing data using different Time Intelligence, e.g. YTD and MAT and I need to adjust the definition of “lost and found” dynamically 🙂

    My question is – could you please give a hint how to modify the last formulae in order to show sales of new (lost, returning) customers?

    Thank you
    Martin

    • If you want to get the Sales for new customers, for example, you can return that as the result of Calculate, something like this:
      Calculate ([Sales],_newCustomers)
      Because the _newCustomers is a subset of customers and filters the FactSales table, then you would get the sale of the new customers only.
      Cheers
      Reza

Leave a Reply

Your email address will not be published. Required fields are marked *