Up/Down/No change week over week

[Anonymous]

Hi All, 

I have weekly data in my table and I want to compare latest two week's data and want to show up and down. I think this can be done via DAX functon but since I am still new to powerBI, I am not sure how to achieve this. 

I have created table chart in below formar - 

Date	Product A	Product B	Product C	Product D	Total
04/04/2022	50000	25000	35000	50000	160000
03/28/2022	45000	25000	40000	40000	150000

I have added filter to my report to achieve the correct value for each product. I have weekly data and filter out only latest two weeks. Let's assyme 04/04/2022 is the current week and I want to show up and down for each products with total compared with earlier week. 

E..g Product A is up this week compared with earlier while 

Product B is no change

Product C etc etc and total is up. 

Is there any way i can show color codes? or up/down/no change? 

Also, I don't want to show earlier date and just want to show current week(latest week's) data. 

Can someone please help me to achieve this?

[v-eqin-msft]

Hi @Anonymous ,

Agree with @amitchandak —— If they are columns better unpivot data.With help from date table and week rank you can get WOW.

Please unpivot data firstly, and then create measures:

Rank = RANKX(ALL('Table'),CALCULATE( MAX('Table'[Date])) ,,ASC,Dense)

Icon Measure = 
var _previous= CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),[Attribute]=MAX('Table'[Attribute]) && [Rank]=MAXX('Table',[Rank])-1))
var _curr= CALCULATE(SUM('Table'[Value]),ALLEXCEPT('Table','Table'[Date],'Table'[Attribute]))
return IF(_previous<>BLANK(), SWITCH(TRUE(), _previous>_curr,"TriangleLow",_previous=_curr,"TriangleMedium",_previous<_curr,"TriangleHigh"))

Apply the Icon Measure to conditional formatting setting:

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

[Anonymous]

@v-eqin-msft  - I really really appreciate your detailed answer. it did work for me if I don't use any filters. 

My data is already unpivot and I have another column called 'Stage name'.

The up/down works perfectly fine with overall data. But if I add filter to my report (stage name) and exclude a couple of stages then it is not showing correct up/down and keep showing up/down based on the overall values in the table. 

---------------------------

Also, I created a 'custom text column' in my table using 'Product' column and another text column called 'licenses' with if and then condition.

E.g. I have a Product column which has following data - Product A, Product B, Product C, Renewals, etc.  and another column has license type data - deal, call, cloud, etc. 

= Table.AddColumn(Table, "New_attribute", each if [Product] = "ProductA" and [license] = "Cloud" then "ProductA" else if
[Product] = "Renewals and [license] = "Cloud" then "ProductA" else if
[Product] = "ProductB" and [license] = "Call" then "ProductB"

and so on.......

else "Null")

And if I use this 'custom column' instead of the 'Product' column for up/down (without any filter) then it's not working as well for over all values. 

The Rank one works perfectly fine though! 

Can you please help me on the Icon measure formula ?

[Anonymous]

@v-eqin-msft  - Could you please help me on this? 

[amitchandak]

@Anonymous , Hope product is column and A,B,C,D are in rows, not as columns. If they are columns better unpivot data.

With help from date table and week rank you can get WOW

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8

[Anonymous]

@amitchandak  - Hi Amit, the article is really helpful, and it does have a lot of information which can be helpful in future. 
However, I think what I am looking for is DAX formula logic. My original row data is at opportunity level but when I can aggregate it at week level. 

WEEK_DATE	Products	ACV
04/04/2022	A	50000
04/04/2022	B	25000
04/04/2022	C	35000
04/04/2022	D	20000
03/28/2022	A	40000
03/28/2022	B	25000
03/28/2022	C	25000

So I wanted to compare the ACV for product A for 04/04/2022 vs 03/28/2022. And same for other products and total as well. 
And want to show as is it Up/Down/No change or may be just assigned the background color on the my chart - 

Date	Product A	Product B	Product C	Product D	Total
04/04/2022	50000	25000	35000	50000	160000
03/28/2022	45000	25000	40000	40000	150000

Let me know if you think it is doable. Appreciate your all help.