Lost customer between two years

Thanks v-huizhn-msft

I will try to be as clear as possible.

I've been asked to find a way to compare customers activity between two years.

I don't need to have the exact values of their purchases but i have to show if between one time interval to an another they have been working with us. ( From 2016 to 2017).

Three answer can  be possible :

• New customer
• A steady customer
• A customer who stopped working with us.

Until now i succeed to determinate news customers and steady customers

I found formula for each request.

New = 
COUNTROWS (
    DISTINCT (
        EXCEPT (
            SELECTCOLUMNS ( Fusion; "Customer name"; Fusion[Customer name] );
            SELECTCOLUMNS (
                FILTER ( ALL ( Fusion ); Fusion[Dates].[year] = MAX ( Fusion[Dates].[year] ) - 1 );
                "Customer Name"; Fusion[Customer name]
            )
        )
    )

steady = 
COUNTROWS (
    DISTINCT (
        INTERSECT (
            SELECTCOLUMNS ( Fusion; "Customer name"; Fusion[Customer name] );
            SELECTCOLUMNS (
                FILTER ( ALL ( Fusion ); Fusion[Dates].[year] = MAX ( Fusion[Dates].[year]) -1 );
                "Customer name"; Fusion[Customer name]
            )
        )
    )

stopped =
COUNTROWS (
    DISTINCT (
        EXCEPT (
            SELECTCOLUMNS (
                FILTER ( ALL ( Table1 ), Table1[year] = MAX ( Table1[year] ) - 1 ),
                "customerId", Table1[customerId]
            ),
            SELECTCOLUMNS ( Table1, "customerId", Table1[customerId] )
        )
    )
)

You can see that each formula compare the customer list from the previous year.

For "new" and "Steady" you can see that the formula work well but for the "Stopped". It is not working like i would like.

I've try to modificate some functions "Except, Countrow..." but i can't succeed.

Hoping this is clear enough

Best regards