New customer after 3 years no purchasing value

[mpmsltd]

Hi there,

I'm trying to identify "new customers" (customers that have a first sale or a sale after 3 years without purchasing) and this post helped me to almost achieve what I need.

https://community.powerbi.com/t5/Desktop/New-customer-after-12-months-no-purchasing-value/td-p/35174...

The only issue that I'm having is that we ship samples to customers (invoice value = 0) and I don't want to consider them in the calculation.

For example:

If a customer has one sale = 0 -> it's not a "new customer"

and If a customer has one sale = 0  last year and now has purchased again -> it's a "new customer"

The first case I was able to filter out by adding a filter for revenue > 0

the second case I couldn't figure out yet. Can anyone help me to add a condition that excludes 0 dollar sales to the query on the post above?

Thank you so much in advance!

[v-cherch-msft · Accepted solution]

Hi @mpmsltd 

You may try below calculated column.Show a simplified sample as below:

Column =
VAR CurrentYear =
    YEAR ( TODAY () )
VAR Last_Year_Sale =
    CALCULATE (
        SUM ( Sale[Sale] ),
        FILTER (
            ALLEXCEPT ( Sale, Sale[Customer] ),
            YEAR ( Sale[Purchase date] ) = CurrentYear - 1
        )
    )
VAR Current_Year_Sale =
    CALCULATE (
        SUM ( Sale[Sale] ),
        FILTER (
            ALLEXCEPT ( Sale, Sale[Customer] ),
            YEAR ( Sale[Purchase date] ) = CurrentYear
        )
    )
RETURN
    IF ( Last_Year_Sale = 0 && Current_Year_Sale <> 0, "New Customer" )

Regards,