topic Re: Compare week on week with SWITCH ouput in DAX Commands and Tips

Compare week on week with SWITCH ouput

Amanda24 — Sun, 10 Sep 2023 08:19:12 GMT

Hi,

I need to compare quantity with the current week vs previous week or comparison week after another week.

My current code:

comparison_test =

VAR CurrentWeekQnty = CALCULATE(SUM('Table'[qnty]),FILTER(ALL('Calendar'),'Calendar'[Week Rank]=max('Calendar'[Week Rank])))

VAR PreviousWeekQnty = CALCULATE(SUM( 'Table'[qnty]), FILTER(ALL('Calendar'),'Calendar'[Week Rank]=max('Calendar'[Week Rank])-1))

RETURN

SWITCH(

TRUE(),

CurrentWeekQnty = PreviousWeekQnty, "Quantity didn't change",

CurrentWeekQnty > PreviousWeekQnty, "Increased",

CurrentWeekQnty < PreviousWeekQnty, "Decreased",

"New Material"

)

week rank calculated column code:

Week Rank = RANKX(all('Calendar'),'Calendar'[Week],,ASC,Dense)

The example data:

Material	Record Date	Qnty	Current code ouput	Desired code output
Material 1	1.8.2023	16	Quantity didn't change	New Material
Material 1	8.8.2023	16	Quantity didn't change	Quantity didn't change
Material 1	14.8.2023	16	Quantity didn't change	Quantity didn't change
Material 1	22.8.2023	16	Decreased	Quantity didn't change
Material 1	28.8.2023	16	Increased	Quantity didn't change
Material 2	1.8.2023	10	Quantity didn't change	New Material
Material 2	8.8.2023	14	Quantity didn't change	Increased
Material 2	14.8.2023	14	Quantity didn't change	Quantity didn't change
Material 2	22.8.2023	14	Decreased	Quantity didn't change
Material 2	28.8.2023	14	Increased	Quantity didn't change

Re: Compare week on week with SWITCH ouput

some_bih — Sun, 10 Sep 2023 09:08:02 GMT

Hi Amanda24 you wrote "I need to compare quantity":

To understand more your current model and requirements, please answer following:

- why do you use RANKX? Why simple SUM for quantity is not ok?

-Material, meaning material quantity should be compared / checked on Weekly level in calculated column?

Re: Compare week on week with SWITCH ouput

ThxAlot — Sun, 10 Sep 2023 09:17:24 GMT

Material.pbix

Re: Compare week on week with SWITCH ouput

Amanda24 — Sun, 10 Sep 2023 09:21:20 GMT

Hi some_bih

thanks for the question. I use RANKX to rank Weeks in order from 1s to last, so I don't need to deal with year changes issues. I don't use Date -7 days as to determine the last week, because the data upload is happening not every 7 days but can be sometimes after 6 days or so.

I use SUM of material quantities, but the issue is that I want to have comparison done like that.

1. The material 1 was available on August 1 2023, that is the first time Material 1 appeared in the system with quantity 16 pcs for the first time, then output should be "New Material",

2.the next week August 8 2023 the same Material 1 is still with 16 pcs, so output should be as "Quantity didnt change". For some reason, when it comes to comparing Aug 16 to Aug 28, the material qnty is decreased but in reality is the same, so it should be "Quantity didnt change"

Re: Compare week on week with SWITCH ouput

Amanda24 — Sun, 10 Sep 2023 09:39:19 GMT

Thank you for your time ThxAlot , it works great in measure but I'd need it in calcualted format so I can show the changes in visualization format, but for some reason when I try the same in to do this for calculated column, the result is just ouputs as "New Material" and no other options included.

Re: Compare week on week with SWITCH ouput

Amanda24 — Sun, 10 Sep 2023 09:47:27 GMT

Also when trying to use slicer for dates, I get error "OFFSET's Relation parameter contains column "Material" which matches more than one row context or filter context. This is not supported".

Re: Compare week on week with SWITCH ouput

some_bih — Sun, 10 Sep 2023 09:50:24 GMT

Hi Amanda24 in DAX there is function to retreive number of week in year,

check link below

From your table data as example is it possible to calculate what you expect as output in calculated column (Material, meaning material quantity should be compared / checked on Weekly level in calculated column? )?

https://learn.microsoft.com/en-us/dax/weeknum-function-dax?WT.mc_id=DP-MVP-4025372