Power BI: New and Repeat Customers
In this post, I will teach you how to track new and repeat customers in Power BI.
While new customers are key to a company's growth, the cost of acquiring them is usually higher compared to repeat customers. It is therefore important to have a balanced growth between new and repeat customers.
Below is the template that we will build.
The source data is a simple sales table with date of sale, customer, and amount.
To follow along with the tutorial, you can download the source data from here.
Create the Power BI measures for new and repeat customers
Count of customers
We start with a simple formula, which is the count of customers. To create it, go to Modeling and click on New Measure.
And here is the DAX formula:
Count of customer = DISTINCTCOUNT('Sales table'[customer])
New customers
The next measure is the count of new customers. The logic for counting new customers is the following: we first find the customers of the current month. After that, we find the customers that bought in the past.
To finish, we keep only the current month's customers that did not buy in the past.
Below is the DAX code for it.
New customers =
VAR currentCustomers = VALUES('Sales table'[customer])
VAR currentDate = MIN('Sales table'[date])
VAR pastCustomers = CALCULATETABLE(VALUES('Sales table'[customer]),
ALL('Sales table'[date].[Month],'Sales table'[date].[MonthNo],'Sales table'[date].[Year])
, 'Sales table'[date]<currentDate)
VAR newCustomers = EXCEPT(currentCustomers,pastCustomers)
RETURN COUNTROWS(newCustomers)
This formula uses four variables for intermediary calculations: currentCustomers, currentDate, pastCustomers, and newCustomers.
'currentCustomers' creates a list of customers of the current month. Then, 'currentDate' calculates the first date of the month: if the month is February, currentDate will be equal to the 1st of February.
The third variable 'pastCustomers' has a longer formula which includes one calculation (VALUES) and two filters for this calculation.
VALUES creates the customers' list. The two filters are ALL, which removes filters on Month and Year, and 'earlier than currentDate' which limits the selection to past dates.
The last variable is newCustomers, calculated as the customers of the current month who were not customers in the past.
We now have the count of new customers on our table!
New customers sales
Now we move on to the new customers' sales. The calculation is very similar to the previous formula. The only difference is that before we counted the customers, now we add up their sales.
New customers sales =
VAR currentCustomers = VALUES('Sales table'[customer])
VAR currentDate = MIN('Sales table'[date])
VAR pastCustomers = CALCULATETABLE(VALUES('Sales table'[customer]),
ALL('Sales table'[date].[Month],'Sales table'[date].[MonthNo],'Sales table'[date].[Year])
, 'Sales table'[date]<currentDate)
VAR newCustomers = EXCEPT(currentCustomers,pastCustomers)
RETURN SUMX(newCustomers, CALCULATE(SUM('Sales table'[Sales])))
Repeat customers and their Sales
We calculate returning customers as total customers minus new customers.
Repeat customers = [Count of customer]-[New customers]
The logic is the same for Repeat customers sales
Repeat customers sales = SUM('Sales table'[Sales])-[New customers sales]
Repeat customers from the previous month.
We already have a formula for returning customers. This formula, however, does not differentiate for purchases a couple of weeks apart or a couple of years apart.
A better measure to understand customer loyalty is the number of returning customers from the previous month. Purchasing every month indicates a higher loyalty compared to purchasing every year.
Here is the DAX formula.
Repeat customers from prior month =
VAR currentCustomers = VALUES('Sales table'[customer])
VAR previousMonthDates = PREVIOUSMONTH('Sales table'[date])
VAR previousMonthCustomers = CALCULATETABLE(VALUES('Sales table'[customer]),
ALL('Sales table'[date].[Month],'Sales table'[date].[MonthNo],'Sales table'[date].[Year])
, previousMonthDates
)
VAR repeatCustomersPreviousMonth = INTERSECT(currentCustomers, previousMonthCustomers)
RETURN COUNTROWS(repeatCustomersPreviousMonth)
The formula remains similar to what we saw before with a few differences.
The variable previousMonthDates finds the dates range of the previous month: if the current month is February, it finds the range from January 1st to January 31. This range is afterward used to find the customers purchasing in the previous month.
The second difference is the use of INTERSECT instead of EXCEPT. Intersect finds the customers that are at the same time in the current month, and in the previous month.
Now we move on to the last formula of the tutorial.
Repeat customers from previous month Sales.
The last formula of this tutorial is the sales of repeat customers from the previous month.
Repeat customers from prior month sales =
VAR currentCustomers = VALUES('Sales table'[customer])
VAR previousMonthDates = PREVIOUSMONTH('Sales table'[date])
VAR previousMonthCustomers = CALCULATETABLE(VALUES('Sales table'[customer]),
ALL('Sales table'[date].[Month],'Sales table'[date].[MonthNo],'Sales table'[date].[Year])
, previousMonthDates)
VAR repeatCustomersPreviousMonth = INTERSECT(currentCustomers, previousMonthCustomers)
RETURN SUMX(repeatCustomersPreviousMonth, CALCULATE(SUM('Sales table'[Sales])))
You can now start tracking your new and repeat customers and better understand where your sales are coming from. I hope this was helpful to you!
Enjoyed the article? Subscribe to the newsletter and download the Power BI of the article for free.
36 Comments
This is very helpful! However, I get an error message saying that "a date column containing duplicate dates was specified in the call to function 'firstdate' and that's not supported. Does it mean that I have a customers who made two orders the very same day? Can you help me understanding what this error message means, please? Tx
Hi Mari,
thank you for your comment.
I verified and in my example, there are also duplicated dates so I am not sure why it's not working in your case.
After a bit of digging, a solution is to replace FIRSTDATE with MIN. Can you please check it and let me know if it fixes it?
I will also update this part in the article for future readers.
Luca
Luca,
Hi. This was very helpful. In addition to adding this to my table, I'd like to be able to add a chart of the new vs returning customers, so that I can slice on it. I'm relatively new to Power BI, so I'm hoping you can help,
Thanks,
Hi Susan,
I'm glad that was helpful!
Can you share a bit more on what you are trying to achieve?
If you want to slice your charts based on a filter 'new customers/repeat customers', then we need to decide based on which date we classify customers as New vs Repeat.
Example:
1- based on the previous year (all customers that purchased before Dec 31, 2019 are Repeat Customers and all the other customers are New ones).
2- based on the previous month (all customers that purchased before August 31, 2020 are Repeat customers and the other customers are New).
etc
Luca
Luca,
In my use case, any time I want to look at a 'slice' of time, I want to know how many of my customers are 'first time customers' vs customers who have purchased any time in the past.
So, when I look at my data with no filters, all of my customers are new customers. And, when I apply a non-time filter (geography, quantity purchased), all my customers are new customers. However, as soon as I apply any filter that is time based, I can see how many customers are 'new' vs 'returning'. But the table of those customers shows all of the customers, and does not allow me to see which are new and which are returning. In that instance, I'd like to be able to differentiate the new from returning customers.
Thank you,
Susan
Hi Susan,
I think I understand what you want to achieve.
The example in the article only shows the count of customers.
What you are interested in is the detail of the New and Repeat Customers (customer A is new, customer B is repeat).
If that is your goal, I just updated the Power BI file for the article with an additional page giving you that.
Please look at it, and let me know if that fits your use case.
Luca
Luca,
Hi. Adding a filter on the visual, as you did in the example, allows me to create the two tables as you did, which fits my use case. Thank you very much for that. It looks like applying that same filter to the page is much more complicated, right?
I really appreciate your help with this.
Thank you,
Susan
Hi Susan,
it's actually quite easy to apply the same filter to the page.
Power BI has a Filters section on the right. On this section you will see: 'Filters on this visual', 'Filters on this page', 'Filters on all pages'.
You can recreate the same filter in the 'Filters on this page' area and the filter will apply to the full page.
Regards
Luca
Hi Luca, An excellent tutorial – Thank you
I was just wondering whether you had anything on calculating Lost customers?
Many Thanks
Avin
Thank you Avin! I'm glad you liked the tutorial.
I don't have an article now on Lost customers, but I'm always on the lookout for ideas on new material. Can you share more on the analysis that you wish to develop?
I imagine that you are comparing 2 years and you want to explain the variation due to lost customers?
Luca
Hi Luca,
I'll try my best to explain…
I was thinking along the lines of a report that :
i) Identifies all the customers that have had sales in the last x years
ii) Then when a date filter is used e.g Months Sep to Dec
iii) The report then identifies all those customers from (i) that have had 0 sales during the period specified in (ii)
hope this makes sense
Thanks again for your help
Hi Avin,
in that case, you can reuse the formula for New Customers with one modification. The formula for New Customers calculates:
– current customers (customers buying in the selected period)
– past customers (customers that bought prior to the selected period)
Then it takes all current customers which are not past customers.
For your use case, we can reverse the last part of the formula: take all past customers which are not current customers.
Below is the updated formula.
Hope this helps!
Luca
Here are the last 2 rows of the New Customers Formula:
VAR newCustomers = EXCEPT(currentCustomers,pastCustomers)
RETURN COUNTROWS(newCustomers)
Here are the same last 2 rows updated for the Lost Customers:
VAR lostCustomers = EXCEPT(pastCustomers,currentCustomers)
RETURN COUNTROWS(lostCustomers)
Ah I see, that makes sense. I will give it a go.
Thanks for taking the time to do this – much appreciated!
Hi Luca,
I like your articles which are very insightful. Apprecited.
I have a question to ask if there is a date table which is related to your sale table,
Can we replace the 'Sales table' by the 'date' table in below formula?
ALL('Sales table'[date].[Month],'Sales table'[date].[MonthNo],'Sales table'[date].[Year])
look forward to your reply,
thanks,
john
Hi John,
yes indeed you can!
Well spotted.
Luca
Hi,
This is excellent tutorial. I have just one problem: Total of returning customers on year level is zero, cause CountOfCustomers and NewCustomers is identical, even though per month there are returning customers. How to deal with that?
Thank you.
Hi Emil,
thank you for sharing your question!
Here are my comments on it:
I tried to filter the pbix in the tutorial by year, and this is what I see:
Year 2018 – Count of customers 279, New customers 279, Repeat customers 0. This is exactly what you mention. As 2018 is the first year with data, all customers are considered New.
Year 2019 – Count of customers 275, New customers 78, Repeat customers 197. As 2019 is the second year with data, Power BI can correctly determine which customers are New and which ones are Existing by comparing with 2018.
Thus, once you have more than one year in the model, you will be able to see the split between New and Existing customers. Does this answer your question?
Luca
Hi,
your blog literally saved me! I am quite new to Power bi and I am struggling to understand how to exclude some customers (if in a column A the value is greater than 1) from the count of new customers. If I filter the visual, I don't get the correct result. Shall I add a new VAR to the formula?
Thanks
Marika
Hi Marika,
thank you for your comments! I am glad you found the blog to be useful.
In the article, I use VALUES(table[customer]) which returns a list of the customers.
You could wrap that in a CALCULATETABLE to add filters to this list.
For example: CALCULATETABLE(VALUES(table[customer]), filter 1, filter 2)
Does this help you?
Luca
Hi Luca, I have the exact same challenge. to filter the customer ID based on measures.I only want to include CustomerID where Customers have at least a 0 in the measure Total invoiced.
I have tried two versions based on your support to Marika:
Version (Customer filter only in VAR CurrentCustomers)
Count of new customers (PreInvoice, Total invoiced):=
VAR currentCustomers= CALCULATETABLE(VALUES(PreInvoice[CustomerID]), FILTER(PreInvoice, NOT(ISBLANK([Total invoiced]))))
VAR currentDate= MIN('Calendar'[Date])
VAR pastCustomers= CALCULATETABLE(VALUES(PreInvoice[CustomerID]),
ALL('Calendar'[Month], 'Calendar'[Year]), 'Calendar'[Date]<currentDate, FILTER(PreInvoice, NOT(ISBLANK([Total invoiced]))))
VAR newCustomers = EXCEPT(currentCustomers,pastCustomers)
RETURN COUNTROWS(newCustomers)
Version 2 (customer filter lastfilter in VAR pastCustomers)
Count of new customers (PreInvoice, Total invoiced):=
VAR currentCustomers= CALCULATETABLE(VALUES(PreInvoice[CustomerID]), FILTER(PreInvoice, NOT(ISBLANK([Total invoiced]))))
VAR currentDate= MIN('Calendar'[Date])
VAR pastCustomers= CALCULATETABLE(VALUES(PreInvoice[CustomerID]),
ALL('Calendar'[Month], 'Calendar'[Year]), 'Calendar'[Date]<currentDate)
VAR newCustomers = EXCEPT(currentCustomers,pastCustomers)
RETURN COUNTROWS(newCustomers)
Version 3 (customer filter first filter in VAR pastCustomers)
Count of new customers (PreInvoice, Total invoiced):=
VAR currentCustomers= CALCULATETABLE(VALUES(PreInvoice[CustomerID]), FILTER(PreInvoice, NOT(ISBLANK([Total invoiced]))))
VAR currentDate= MIN('Calendar'[Date])
VAR pastCustomers= CALCULATETABLE(VALUES(PreInvoice[CustomerID]), FILTER(PreInvoice, NOT(ISBLANK([Total invoiced]))),
ALL('Calendar'[Month], 'Calendar'[Year]), 'Calendar'[Date]<currentDate)
VAR newCustomers = EXCEPT(currentCustomers,pastCustomers)
RETURN COUNTROWS(newCustomers)
These measures generates the exact same count as without the CustomerID filtered versions. What am I doing wrong?
This measure generates the correct countof customers with the same filter:
Count of customers (PreInvoice, Total invoiced) blank value:= CALCULATE(DISTINCTCOUNT(PreInvoice[CustomerID]), FILTER(PreInvoice, NOT(ISBLANK([Total invoiced]))))
Thanks in advance and much appreciated guide!
Hi Anders,
thank you for your question!
To correctly answer it, I would need to spend time reviewing your data model.
I don't think I can give an answer without looking at your data model.
If you want, we can continue the conversation via email?
My email is lc@finance-bi.com
Regards,
Luca
Hi Luca, thanks for gettin back. I understand 🙂 Unfortunately I'm not able to share my datamodel as I'm working with client data.
I will continue testing and get back to you if/once I manage to solve it.
thanks
anders
Hi Anders,
I understand. Best of luck for finalizing your model, and feel free to share once you are able to solve it.
Luca
Hi Luca,
i found your article extremely helpful. Rather than having a fixed timeframe for repeat customers of 1 months back, I would like to calculate the average order frequency per repeat customer. Any insights how i would go about this? I have a data table with all orders over the years.
Thanks for any comment and thanks again for the great article
Hi Niko,
Thank you for your comment.
I notice that I reply late to your question. If you need fast help in the future, you can send me an email at lc@finance-bi.com.
To ensure we are on the same page, I imagine that by average order frequency you mean:
number of months a customer has been purchasing divided by number of orders
Here is an example. A customer has been purchasing for 3 months, and has a total of 2 orders.
I obtain 3/2 = 1.5 which means the customer on average purchases every 1.5 months.
Here is how I would go about calculating it in DAX:
1- Identify the repeat customers
You can modify the formula for 'New customers sales' for this.
Replace the formula
VAR newCustomers = EXCEPT(currentCustomers,pastCustomers)
with
VAR repeatCustomers = INTERSECT(currentCustomers,pastCustomers)
You now have a list of your repeat customers.
2- The next step is to iterate through this list, calculate the order frequency for each customer, and finally calculate the average of each order frequency.
You can AVERAGEX for that.
The pseudo-formula will be AVERAGEX ( repeatCustomers, 'calculation of number of months divided by number of orders')
For the calculation of the number of months you can use the DAX formula FIRSTDATE. That will tell you when the customer started buying.
For the number of orders, you can use the DAX formula COUNT.
I have used pseudo-code and did not write down the full DAX formula, as that will depend on your data model.
I hope this helps you!
Luca
Hi there,
I have been looking for this for months! All the others I found were not clear so THANK YOU!
I have a question, I would like to identify the orders of the repeat buyers. I cannot use these as filter. What would you suggest?
Thank you
Sandrine
Hi Sandrine,
thank you for very kind comment!
For your question, I propose to add the Order Number column to the table and filter for Repeat Customers different from 0.
However, for that to work, you will need to do a modification in the Repeat Customers formula.
Here is the reason for the modification:
When you add the Order Number column to the table, Power BI will calculate New and Repeat customer for each order.
As each order number is unique, Power BI will consider all customers to be New.
Example: Order number 1000 is bought only once, so the customer associated will be considered New.
Order number 1001 is bought only once, so also the customer associated will be considered New. And so on.
We need to modify the Repeat Customers formula so that it disregards the Order Number when deciding which customer is New.
You can do that with CALCULATE and ALL. The formula will become:
Repeat customers = CALCULATE([Count of customer]-[New customers], ALL('Sales table'[Order Number]))
I hope this helps you,
Luca
HI Luca,
Love your work.
Sorry if i have missed it in the above, but I would like to graph sales totals of new customers in the FY by month. E.g. a new customer for this financial year has total sales of $1mil, first sales were in Sept of say $700k. When i apply the month axis, only the sales of the first month appear, so my total sales of new customers YTD , and that shown on the graph doesnt align?
Hi Juju,
thanks for your nice words.
The formula in the article considers a customer to be New only for its first month of sales.
To consider all sales during the year, you could change the formula as below:
New customers sales =
VAR currentCustomers = VALUES('Sales table'[customer])
VAR currentDate = MIN('Sales table'[date])
VAR firstDayOfYear = DATE(YEAR(currentDate),01,01)
VAR pastCustomers = CALCULATETABLE(VALUES('Sales table'[customer]),
ALL('Sales table'[date].[Month],'Sales table'[date].[MonthNo],'Sales table'[date].[Year])
, 'Sales table'[date]
Excellent article!
I'd like to find out how many of the customers who purchased in year 1 have returned in year 2, year 3, etc.
This will enable me to calculate the Life Time Value of a particular Customer.
As the article stand today, it will always compare to the year before…
Do you have any advice?
Thanks
Hi Julie,
I think I had missed your comment, sorry for that.
If you want to reach me, you can send me an email at lc@finance-bi.com so you can make sure to reach me all the time.
For that, I might use the Cohort Analysis example (https://finance-bi.com/blog/power-bi-cohort-analysis/) instead of the New and Repeat Customers example.
I just replied to a question from Lina also asking about the Customer Lifetime Value. You can find my reply at the bottom of the article.
Hope this helps you!
Luca
Hi Luca
I reviewed that Total of New Customers Sale show 8370 is wrong, It must be 5064. You review again
Hi Long,
well spotted! You are right.
If we add up the New Customer Sale by month, then we obtain $5064 instead of $8370.
The reason is the following:
When Power BI calculates the new customers for June, it includes only customers who started buying from June. It does not include customers who started buying between January and May.
When Power BI calculates the new customers for the Total Year, it includes all customers who started buying from January of that year.
For example, the June sales of a customer who started buying in January:
– are not included in the month of June (because the customer started buying earlier than June)
– are included in the Total Year number (because the customer started buying this year)
That creates a difference between adding up the Sales by month versus calculating the total year number.
That said, I agree with you.
When you build a dashboard, the numbers should be matching. Otherwise, your users will lose confidence in it.
So based on your situation, you can decide whether the more appropriate number should be $8370 or $5064.
If the number you want to show is $5064, you can find an example of how to reproduce it using the second solution here:
https://finance-bi.com/blog/power-bi-totals-incorrect/
I hope my answer is clear. Let me know if you have more comments,
Luca
Hi Luca,
Thank you very much for this article – and the ongoing insightful discussion!
Using your example data table, can one be able to count distinct new, lost, and returning customers by year if we replace the 'Sales' column with 'Product Name'? It will be useful to see the products our new customers buy on their first purchase, and whether they move on to buy other products available in our shop?
Please help.
Thank you
Hi Siyanda,
thank you for your question!
To see the products bought by new customers, you could take the 'New customers sales' measure and add it to a table along with the Products column.
The 'New customers sales' measure will however need one modification: in the calculation of pastCustomers inside the ALL statement, you should also add the Product column.
Does this help you?
Luca
Dear Luca,
I need to calculate a new customer from a different query …
New customer is those who are coming after 12 months ….
For an e.g If a customer comes in January 2020 and he come after 12 months in February 2021 then its a new customer.