Forum Discussion
Identifying New Customer Sales
I need to create a measure that only calculates sales for 'New Customers.' A customer is considered 'New' only in the month of their very first purchase. If they buy again a month later, they are 'Returning.' How do I write a DAX measure that filters sales based on the customer’s first-ever appearance in our data?
hey Amik_singh revert with more information if below suggested measure doesn't help. Sentence After -- is a comment for better context.
New Customer Sales =
VAR CurrentCustomers = VALUES('Sales'[CustomerID])
VAR EndDate = MAX('Date'[Date])-- Find customers whose first purchase date falls within the currently selected period
VAR NewCustomers = FILTER(CurrentCustomers,
VAR FirstPurchaseDate =
CALCULATE( MIN('Sales'[OrderDate]),
ALL('Sales') -- Look at their entire history
)
RETURN
-- Check if their first purchase happened in the visible date range
FirstPurchaseDate IN VALUES('Date'[Date])
)RETURN
CALCULATE([Total Sales], KEEPFILTERS(NewCustomers))
2 Replies
hey Amik_singh revert with more information if below suggested measure doesn't help. Sentence After -- is a comment for better context.
New Customer Sales =
VAR CurrentCustomers = VALUES('Sales'[CustomerID])
VAR EndDate = MAX('Date'[Date])-- Find customers whose first purchase date falls within the currently selected period
VAR NewCustomers = FILTER(CurrentCustomers,
VAR FirstPurchaseDate =
CALCULATE( MIN('Sales'[OrderDate]),
ALL('Sales') -- Look at their entire history
)
RETURN
-- Check if their first purchase happened in the visible date range
FirstPurchaseDate IN VALUES('Date'[Date])
)RETURN
CALCULATE([Total Sales], KEEPFILTERS(NewCustomers))I offer you an alternative solution that removes filters only from the Calendar and not from any other dimension. This is a solution for the case in which a customer is to be considered new multiple times, based on the product color (or any other attribute like sales territory etc...).
Imagine you have a Customer that buys a blue product on june 2025, then a red product on july 2025.
With the code provided in the previous answer (a very good code that answers your request in which you only referred to time), it will be counted as new only on june 2025 and not in july 2025 even if you include the color in the matrix or you filter the color in a slicer. If, on the other hand, you want to consider the product selection and not only focus on time of the first purchase (so it will be new only not only on june 2025 but also on july 2025), the code must be modified as follows
New Customer Sales =
VAR CurrentCustomers = VALUES('Sales'[CustomerID])VAR NewCustomers =
FILTER(
CurrentCustomers,
VAR FirstPurchaseDate =
CALCULATE(
MIN('Sales'[OrderDate]),
ALL('Calendar') -- remove filters only from the calendar table
)
RETURN
FirstPurchaseDate IN VALUES('Date'[Date])
)RETURN
CALCULATE([Total Sales], KEEPFILTERS(NewCustomers))Just to be clear: I am not saying the code you have accepted as a solution is not good, I am just trying to complete the solution offering also one for those interested in this little different approach
Hope I contributed to improving the solution for any kind of user
With best regards
If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
