new customer sales measure but only when total sales in the month is > 0

[gaznez]

Hi all,

I'm trying to create a measure to calculate new customer sales - but only when their total sales in the month are > 0.  We have some customers who we provide free of charge samples to before they buy from us.  I only want to class them as a new customer once their sales value is greater than zero.

I found the following measure in a tutorial - which works - but it will pick up new customers with a zero sales value...

New Customer Sales =

VAR Customers =

VALUES(CustomerMaster[Customer])

RETURN

CALCULATE(

    [Total sales],

    FILTER(

        Customers,

        CALCULATE( COUNTROWS('Sales Data'),

        FILTER( ALLSELECTED(Dates[Date]), Dates[Date] < MIN(Dates[Date] ) ) ) = 0 ) )

Can anyone advise how to modify the formula so that it won't pick up customers as new until the first month when they've spent more than zero?

[Ashish_Mathur]

Hi,

Try this approach

1. Create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name by the Month number.
2. Create a relationship (Many to One and Single) from the Date column of the Data Table to the Date column of the Calendar Table
3. To your visual, drag Date from the Calendar Table
4. Write these measures

First date of interaction = calculate(min(data[Date]),datesbetween(calendar[date],minx(all(calendar),calendar[date]),max(calendar[date])))

New customers with sales = Countows(filter(values(Customers[Customer name]),[date of first interaction]>=min(calendar[date])&&[Total slaes]>0))

Hope this helps.

[v-binbinyu-msft]

Hi @gaznez ,

In order to better understand your demands and give the right solution, could you please provide some more specific information? such as your desensitized example data and a screenshot of your desired results?

Thanks for your efforts & time in advance.

Best regards,
Community Support Team_Binbin Yu

[gaznez · Accepted solution]

Hi @v-binbinyu-msft ,

I managed to solve it using the following formula (not long before you replied)...

NewCustSales =

CALCULATE([Total sales],

    FILTER(

        CALCULATETABLE(

            ADDCOLUMNS(

                VALUES(CustomerMaster[Customer]),

                "Firstpurchdate",

                CALCULATE(MIN('Sales Data'[Pstg date]),

                'Sales Data'[Sales Value External] > 0)

            ),

            ALL(Dates),

            ALL(Region[CSR Name]),

            ALL(Region[Region]),

            ALL ('Sales Data'[SOff.])

        ),

            CONTAINS (

                VALUES ( 'Dates'[Date] ),

                'Dates'[Date],

                [Firstpurchdate]

            )

        )

    )

Thanks!