How to measure new customer sales

[Eddie_301177]

Hi there,

I need to show count number of new customers based on the following formula: 0 sales last 13 months, >0 sale last month. Below are my workings so far:

1) For all customers:

CountAllCustomer2 = DISTINCTCOUNT(SummarizedQty2[Sold To])

2) For loss customers:

CountLossCustomer2 = CALCULATE(DISTINCTCOUNT(SummarizedQty2[Sold To]),SummarizedQty2[Last12MonthQTY]=0)

3) For new customer: ????

I'm kinda stuck at new customer now. Appreciate your help guys.

[Sahir_Maharaj · Accepted solution]

Hello @Eddie_301177,

Can you please try this approach:

CountNewCustomer2 = 
CALCULATE(
    DISTINCTCOUNT(SummarizedQty2[Sold To]),
    FILTER(
        SummarizedQty2,
        CALCULATE(SUM(SummarizedQty2[Quantity]), DATESINPERIOD(SummarizedQty2[Date], MAX(SummarizedQty2[Date]), -13, MONTH)) = 0 &&
        CALCULATE(SUM(SummarizedQty2[Quantity]), DATESINPERIOD(SummarizedQty2[Date], MAX(SummarizedQty2[Date]), -1, MONTH)) > 0
    )
)

[HarshSathwara19]

Try this for New Customer

New Customer Calculation:

1. Sales in the last 13 months = 0
2. Sales in the last month > 0

   You can use the CALCULATE function with conditions that filter the data based on these two criteria.

   Here’s the DAX formula to calculate the number of new customers:

   CountNewCustomer2 = CALCULATE(
       DISTINCTCOUNT(SummarizedQty2[Sold To]),
       SummarizedQty2[Last12MonthQTY] = 0,  -- 0 sales in the last 13 months
       SummarizedQty2[LastMonthQTY] > 0     -- >0 sales in the last month
   )
           

Explanation:

• DISTINCTCOUNT(SummarizedQty2[Sold To]): Counts the distinct customers in the Sold To column.
• SummarizedQty2[Last12MonthQTY] = 0: Filters customers with no sales in the last 13 months.
• SummarizedQty2[LastMonthQTY] > 0: Filters customers with more than 0 sales in the last month.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,
HSathwara.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

One of ways to achieve this is using EXCEPT DAX function in the measure.

FYI, INTERSECT DAX function is also useful when calculating existing customers count.

EXCEPT function (DAX) - DAX | Microsoft Learn

INTERSECT function (DAX) - DAX | Microsoft Learn

# New Customers: = 
VAR _untilpreviousmonth =
    EOMONTH ( MAX ( 'calendar'[Date] ), -1 )
VAR _datestart =
    EOMONTH ( _untilpreviousmonth, -13 ) + 1
VAR _existingcustomeruntilpreviousmonthlist =
    CALCULATETABLE (
        SUMMARIZE ( sales, customer[customer_id] ),
        FILTER (
            ALL ( 'calendar' ),
            'calendar'[Date] >= _datestart
                && 'calendar'[Date] <= _untilpreviousmonth
        )
    )
VAR _selectedmonthcustomerslist =
    SUMMARIZE ( sales, customer[customer_id] )
RETURN
    COUNTROWS (
        EXCEPT ( _selectedmonthcustomerslist, _existingcustomeruntilpreviousmonthlist )
    )

# existing Customers: = 
VAR _untilpreviousmonth =
    EOMONTH ( MAX ( 'calendar'[Date] ), -1 )
VAR _datestart =
    EOMONTH ( _untilpreviousmonth, -13 ) + 1
VAR _existingcustomeruntilpreviousmonthlist =
    CALCULATETABLE (
        SUMMARIZE ( sales, customer[customer_id] ),
        FILTER (
            ALL ( 'calendar' ),
            'calendar'[Date] >= _datestart
                && 'calendar'[Date] <= _untilpreviousmonth
        )
    )
VAR _selectedmonthcustomerslist =
    SUMMARIZE ( sales, customer[customer_id] )
RETURN
    COUNTROWS (
        INTERSECT( _selectedmonthcustomerslist, _existingcustomeruntilpreviousmonthlist )
    )

[Sahir_Maharaj · Accepted solution]

Hello @Eddie_301177,

Can you please try this approach:

CountNewCustomer2 = 
CALCULATE(
    DISTINCTCOUNT(SummarizedQty2[Sold To]),
    FILTER(
        SummarizedQty2,
        CALCULATE(SUM(SummarizedQty2[Quantity]), DATESINPERIOD(SummarizedQty2[Date], MAX(SummarizedQty2[Date]), -13, MONTH)) = 0 &&
        CALCULATE(SUM(SummarizedQty2[Quantity]), DATESINPERIOD(SummarizedQty2[Date], MAX(SummarizedQty2[Date]), -1, MONTH)) > 0
    )
)

[Eddie_301177]

Hi Sahir,

Many thanks for your recommendation. I've booked a 5min slot with you today to better understand this, thanks.

[Anonymous]

Hi @Eddie_301177 ,

Did the Jihwan_Kim , Sahir_Maharaj's expressions and suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

Regards,

Xiaoxin Sheng