Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mpmsltd
Helper I
Helper I

New customer after 3 years no purchasing value

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!

1 ACCEPTED SOLUTION
v-cherch-msft
Employee
Employee

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

1.png

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-cherch-msft
Employee
Employee

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

1.png

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.