IN / OUT strings labels versus last year

[Anonymous]

Hello, 

I have data that have a month filter and around 92 brands names. 

We want to automatize a dashboard that has the new comers of the brands and the ones who left comparing the last year month. 

As it shown bellow : 

The dashboard has to display in filtering May 2022 for example : "Gucci" left the group brands, "Comets" joined the group brands.

Does anyone can suggest an idea through DAX ? 

Many thanks,

[v-jianboli-msft · Accepted solution]

Hi @Anonymous ,

I created a similar table as below.

You can follow my method:

Here is the DAX:

left =

VAR _curr =

    SUMMARIZE (

        FILTER (

            ALL ( 'Sheet1' ),

            YEAR ( [date] ) = MAX ( 'Sheet1'[date].[Year] )

                && MONTH ( [date] ) = MAX ( 'Sheet1'[date].[MonthNo] )

        ),

        [brand]

    )

VAR _pre =

    SUMMARIZE (

        FILTER (

            ALL ( 'Sheet1' ),

            YEAR ( [date] )

                = MAX ( 'Sheet1'[date].[Year] ) - 1

                && MONTH ( [date] ) = MAX ( 'Sheet1'[date].[MonthNo] )

        ),

        [brand]

    )

VAR _left =

    EXCEPT ( _pre, _curr )

RETURN

CONCATENATEX ( _left, [brand], "," )

 

joined =

VAR _curr =

    SUMMARIZE (

        FILTER (

            ALL ( 'Sheet1' ),

            YEAR ( [date] ) = MAX ( 'Sheet1'[date].[Year] )

                && MONTH ( [date] ) = MAX ( 'Sheet1'[date].[MonthNo] )

        ),

        [brand]

    )

VAR _pre =

    SUMMARIZE (

        FILTER (

            ALL ( 'Sheet1' ),

            YEAR ( [date] )

                = MAX ( 'Sheet1'[date].[Year] ) - 1

                && MONTH ( [date] ) = MAX ( 'Sheet1'[date].[MonthNo] )

        ),

        [brand]

    )

VAR _joined =

    EXCEPT ( _curr, _pre )

RETURN

CONCATENATEX ( _joined, [brand], "," )

Output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-jianboli-msft Thank you so much for your help, the code is very genius 😉 

[WinterMist]

Hello @amitchandak -

What expression would you use if no Sales exists?

What if the only 2 columns are simply: Date & Brand?

How do we identify a Brand GAINED and a Brand Lost?

The underlined SUM(EXPRESSION) below would need to be replaced, but with what?

CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

Thanks for the help.

Nathan

[amitchandak]

@Anonymous , the Same formula's as customer retention can work . You can use datesyd

or

year behind measure for last year

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529
Customer Retention Part 2: Period over Period Retention:https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retention/ba-p/1377458

[Anonymous]

As @WinterMist said .What should we do if there's no sales column ? 

The dax formulas you suggested can be applied only for numeric values. 

My question is how to implement the same idea but in strings values ?

Knowing that Max(stirng_value) and Min(string_value) doesn't give the same result in a CALCULATE function comparing with the SUM. 

Thank you.