Forum Discussion

Amik_singh's avatar
Amik_singh
Advocate I
8 months ago
Solved

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 'Re...
  • Amar_Kumar's avatar
    Amar_Kumar
    8 months ago

    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))

FBergamaschi's avatar
FBergamaschi
Super User
8 months ago

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