topic Calculated column with SWITCH conditions and current week vs previous week comparisons in DAX Commands and Tips

Calculated column with SWITCH conditions and current week vs previous week comparisons

Amanda24 — Sat, 09 Sep 2023 17:31:10 GMT

Hello,

I really need help with calculated column results as it does not give correct results.

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: Calculated column with SWITCH conditions and current week vs previous week comparisons

lbendlin — Mon, 11 Sep 2023 23:34:49 GMT

You need to explicitly check PreviousWeekQnty against BLANK(), and move the "New Material" step up for that.

RETURN SWITCH( TRUE(), ISBLANK(PreviousWeekQnty),"New Material", CurrentWeekQnty = PreviousWeekQnty, "Quantity didn't change", CurrentWeekQnty > PreviousWeekQnty, "Increased", "Decreased" )

Re: Calculated column with SWITCH conditions and current week vs previous week comparisons

v-zhangti — Tue, 12 Sep 2023 11:13:21 GMT

Hi, Amanda24

You can try the following methods.

Column:

Weeknum = WEEKNUM([Record Date],2)

comparison_test = VAR _CurrentWeekQnty = SUM('Table'[Qnty]) VAR _PreviousWeekQnty = CALCULATE(SUM( 'Table'[qnty]), FILTER(ALL('Table'),[Weeknum]=SELECTEDVALUE('Table'[Weeknum])-1&&[Material]=SELECTEDVALUE('Table'[Material]))) VAR _N=SWITCH( TRUE(), _PreviousWeekQnty=BLANK(),"New Material", _CurrentWeekQnty = _PreviousWeekQnty, "Quantity didn't change", _CurrentWeekQnty > _PreviousWeekQnty, "Increased", _CurrentWeekQnty < _PreviousWeekQnty, "Decreased" ) Return _N

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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