Power BI Blog: Monthly New Customers
10 June 2021
Welcome back to this week’s edition of the Power BI blog series. This week, we will look at how to create a simple measure to count the number of new customers in a database for each month.
We are going to look at a simple database where we have three columns, the Date column, the ID column and the Sales column.
In this database, we are interested in counting the number of new customers each month. Let’s try to do this in DAX.
First, we shall need to count cumulative number unique customers in the dataset. We can do this with the following DAX code:
Cumulative New Customers =
CALCULATE(
DISTINCTCOUNT(CustomerID[ID]),
ALL(
CalendarTable[Dates]
)
)
This will provide the count of the cumulative total of unique customers for each month. Then, we can also calculate the accumulated total of new customers up to and including the previous month, so that we can subtract the two to calculate the new customers for the last period.
We can do this with the following DAX code:
# Customers Last Month =
CALCULATE(
DISTINCTCOUNT(
CustomerID[ID]),
DATEADD(
CalendarTable[Dates],
-1,
MONTH
)
)
Now we can simply subtract the two to obtain the number of new customers each month.
New Customers = [Cumulative New Customers] - [# Customers Last Month]
That’s it for this week!
In the meantime, please remember we offer training in Power BI which you can find out more about here. If you wish to catch up on past articles, you can find all of our past Power BI blogs here.
