Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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!
Solved! Go to 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,
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,
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |