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

[Amanda24]

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

[lbendlin · Accepted solution]

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

[v-zhangti · Accepted solution]

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.

[lbendlin · Accepted solution]

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