Calculated column to classify customers by spending

[pb202134]

Hello all 

Could you please help, there is an issue with the caclulcated column i created .
I have a table with customers and the vintage from the products they have bought from two different vintages.
An extract from the table for one customer

Account	Vintage	Sales from EP	Item
10005281	2019	498	Product 1
10005281	2019	389	Product 2
10005281	2019	881	Product 3
10005281	2019	408	Product 3
10005281	2020	474	Product 1

I wrote this calculated column to classify each customer based on whether they have bought from both vintages (2019 and 2020) or from one vintage only:

New or existing to EP = 
var sales2019 = CALCULATE([Sales from EP], 'Summary EP orders'[Vintage]=2019)
var sales2020 = CALCULATE([Sales from EP], 'Summary EP orders'[Vintage]=2020)
return

 if
(sales2019>0 && sales2020>0,"both vintages",
IF(sales2019=0 && sales2020>0,"first time in vintage 20",
IF(sales2019>0 && sales2020=0, " vintage 19, not in 20",
 "never bought")))

but my formula above does not work, the example customer appears in two types, instead of being classed as "both" vintages

what needs amending?

thank you

[pb202134]

Thank you @DataInsights . This is working perfectly fine now!

[DataInsights · Accepted solution]

@pb202134,

Your calculated column was missing ALLEXCEPT. Also, I replaced the IF statements with SWITCH (simpler). Try this:

New or existing to EP = 
VAR sales2019 =
    CALCULATE (
        [Sales from EP],
        'Summary EP orders'[Vintage] = 2019,
        ALLEXCEPT ( 'Summary EP orders', 'Summary EP orders'[Account] )
    )
VAR sales2020 =
    CALCULATE (
        [Sales from EP],
        'Summary EP orders'[Vintage] = 2020,
        ALLEXCEPT ( 'Summary EP orders', 'Summary EP orders'[Account] )
    )
RETURN
    SWITCH (
        TRUE (),
        sales2019 > 0
            && sales2020 > 0, "both vintages",
        sales2019 = 0
            && sales2020 > 0, "first time in vintage 20",
        sales2019 > 0
            && sales2020 = 0, " vintage 19, not in 20",
        "never bought"
    )