Compare week on week with SWITCH ouput

[Amanda24]

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

[ThxAlot]

Material.pbix

[Amanda24]

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".

[Amanda24]

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.

[some_bih]

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? 

[Amanda24]

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"

[some_bih]

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