Commons customers, lost customers and acquired customers for specific product category

[matth]

Hi,

My transactions data looks like this :

Transactions:

LOAD * Inline

[

ID_Transaction,Customer,Category,Year,Amount

    id_001,C001,Vegetables,2022,30

    id_002,C001,Vegetables,2022,60

    id_003,C001,Vegetables,2022,-30

    id_004,C001,Vegetables,2022,-60

    id_005,C001,Vegetables,2023,40

    id_006,C001,Vegetables,2023,60

    id_007,C001,Meat,2022,50

    id_008,C001,Meat,2022,-10

    id_009,C001,Meat,2022,20

    id_010,C002,Vegetables,2022,20

    id_011,C002,Vegetables,2022,30

    id_012,C002,Vegetables,2023,20

    id_013,C002,Vegetables,2023,10

    id_014,C002,Meat,2023,20

    id_015,C002,Meat,2023,-10

    id_016,C002,Meat,2023,20

    id_017,C003,Vegetables,2023,10

    id_018,C003,Vegetables,2023,20

    id_019,C003,Vegetables,2023,-10

    id_020,C003,Meat,2022,20

    id_021,C003,Meat,2023,10

    id_022,C003,Meat,2023,20

    id_023,C003,Meat,2023,-30

    id_024,C004,Vegetables,2022,-10

    id_025,C004,Vegetables,2022,-20

    id_026,C004,Vegetables,2023, 10

    id_027,C004,Meat,2022,-20

    id_028,C004,Meat,2022,-10

    id_029,C005,Vegetables,2023,-10

    id_030,C005,Vegetables,2023, 20

    id_031,C005,Vegetables,2023, 10

]

I'm trying to create 3 tables :

1. Commons customers by taking into account Year AND Category : for example, customers who have a positive amount of vegetables in 2022 AND 2023. They are commons customers for vegetable's category.

2. Lost customers : for example, customers who have a positive amount of vegetables in 2022 but a negative amount (or nothing) in 2023. They are lost customers for vegetable's category.

3. Acquired customers : for example, customers who have a positive amount of vegetables in 2023 but a negative amount (or nothing) in 2022. They are acquired customers for vegetable's category.

I have created two measures named [Category1 Year1] and [Category1 Year2] in order to get commons customers :

[Category1 Year1]

sum ({<Customer = p({<Year = {'2022'},Category={'Vegetables'}>})*p({<Year = {'2023'},Category={'Vegetables'}>})

,Year ={'2022'},Category={'Vegetables'}>}  Amount)

[Category1 Year2]

sum ({<Customer = p({<Year = {'2022'},Category={'Vegetables'}>})*p({<Year = {'2023'},Category={'Vegetables'}>})

,Year ={'2023'},Category={'Vegetables'}>}  Amount)

I think I have successfully create my table with commons customers with Customers in Dimension and this formula in Measure :

({<[Customer]={"=[Category1 Year1]>0"}*{"=[Category1 Year2]>0"}>} [Category1 Year1])

But I can't get my lost customers and acquired customers in my other tables. I tried this formula for acquired customers in another table :

({<[Customer]={"=[Category1 Year2]>0"}*{"=[Category1 Year1]<=0"}>} [Category1 Year2])

With this formula, I get customers C001 and C004 because they have an amount less or equal than 0 in year 2022 in vegetables' category. But I also would like to get C003 and C005, even if they don't have an amount in 2022 for vegetables' category, they are acquired customers.

I've been trying to solve this problem for a long time. I've tried lots of things but to date I've had no results. 

Any help would be most welcome !

I hope I've made myself clear.

Thanks in advance