Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |