topic Re: IN / OUT strings labels versus last year in DAX Commands and Tips

IN / OUT strings labels versus last year

Anonymous — Tue, 28 Jun 2022 12:57:20 GMT

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,

Re: IN / OUT strings labels versus last year

amitchandak — Mon, 27 Jun 2022 16:49:11 GMT

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

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

Re: IN / OUT strings labels versus last year

WinterMist — Mon, 27 Jun 2022 19:08:24 GMT

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

Re: IN / OUT strings labels versus last year

Anonymous — Tue, 28 Jun 2022 09:25:37 GMT

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.

Re: IN / OUT strings labels versus last year

v-jianboli-msft — Fri, 01 Jul 2022 03:21:25 GMT

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.

Re: IN / OUT strings labels versus last year

Anonymous — Fri, 01 Jul 2022 12:47:23 GMT

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