How to add a custom column to show if the category went up, down, or no change

[PhillipC1]

Hello,

I have a folder connected to Power BI that is stacking weekly files that come in each week that is shown below, to which we want to add a custom column in power bi that will tell us if this person's category went up, down, or had no change.

ID	Status	Week	Score_Group	Current Score	Category
12345	Valid	6/24/2024	Clean	12	Authorized
45678	Valid	6/24/2024	Clean	26	Moderate
64845	Valid	6/24/2024	Clean	50	High
55848	Valid	6/24/2024	Clean	48	High
12345	Valid	7/1/2024	Clean	9	Authorized
45678	Valid	7/1/2024	Clean	54	High
64845	Valid	7/1/2024	Clean	50	High
55848	Valid	7/1/2024	Clean	0	Authorized
12345	Valid	7/8/2024	Clean	24	Moderate
45678	Valid	7/8/2024	Clean	4	Authorized
64845	Valid	7/8/2024	Clean	54	High
55848	Valid	7/8/2024	Clean	26	Moderate
12345	Valid	7/15/2024	Clean	8	Authorized
45678	Valid	7/15/2024	Clean	10	Authorized
64845	Valid	7/15/2024	Clean	48	High
55848	Valid	7/15/2024	Clean	52	High

Essentially we would like the table in power bi to add this column to make it show what the change is from the prior week. This will allow us to then query how many times a person has a shift up or down. 

ID	Status	Week	Score_Group	Current Score	Category	Change
12345	Valid	6/24/2024	Clean	12	Authorized	No Change
45678	Valid	6/24/2024	Clean	26	Moderate	No Change
64845	Valid	6/24/2024	Clean	50	High	No Change
55848	Valid	6/24/2024	Clean	48	High	No Change
12345	Valid	7/1/2024	Clean	9	Authorized	No Change
45678	Valid	7/1/2024	Clean	54	High	Up
64845	Valid	7/1/2024	Clean	50	High	No Change
55848	Valid	7/1/2024	Clean	0	Authorized	Down
12345	Valid	7/8/2024	Clean	24	Moderate	Up
45678	Valid	7/8/2024	Clean	4	Authorized	Down
64845	Valid	7/8/2024	Clean	54	High	No Change
55848	Valid	7/8/2024	Clean	26	Moderate	Up
12345	Valid	7/15/2024	Clean	8	Authorized	Down
45678	Valid	7/15/2024	Clean	10	Authorized	No Change
64845	Valid	7/15/2024	Clean	48	High	No Change
55848	Valid	7/15/2024	Clean	52	High	Up

The flow of change should go Authorized -> Moderate -> High meaning if week 1 I am authorized and in week 2 I move to Moderate or High that is Up where as if I am moderate in week 1 and move to authorized in week 2 then that is down. 

I hope this is explained clearly, but please let me know if there is anything additional I can clarify on to address this. 

[Dangar332 · Accepted solution]

Hi, @PhillipC1 

try below code for Week Number Column

Week_number = 
var a = WEEKNUM('Table'[Week],1)
var currentYear = YEAR('Table'[Week])
var Current_id = 'Table'[ID] 
var Previousweek = MAXX(FILTER('Table',YEAR('Table'[Week])<currentYear && 'Table'[ID]=Current_id ),WEEKNUM('Table'[Week],1))
var final = IF(ISBLANK(Previousweek),a,Previousweek+a)
RETURN
final

The Measure is same as Above

Measure 2 = 
VAR a = MAXX(
		FILTER(
			ALL(
				'Table'[ID],
				'Table'[Week_number]
			),
			'Table'[ID] = MIN('Table'[ID]) && 'Table'[Week_number] < MIN('Table'[Week_number])
		),
		[Week_number]
	)
	VAR b = IF(
		ISBLANK(a),
		MIN('Table'[Week_number]),
		a
	)
	VAR c = MINX(
		FILTER(
			ALL(
				'Table'[ID],
				'Table'[Category],
				'Table'[Week_number]
			),
			'Table'[ID] = MIN('Table'[ID]) && 'Table'[Week_number] = b
		),
		'Table'[Category]
	)
	VAR c1 = SWITCH(
		c,
		"Authorized", 1,
		"Moderate", 2,
		"High", 3
	)
	VAR d = SWITCH(
		MIN('Table'[Category]),
		"Authorized", 1,
		"Moderate", 2,
		"High", 3
	)

	RETURN
		SWITCH(
			TRUE(),
			c1 = d, "NO Change",
			c1 < d, "UP",
			"Down"
		)

You can Download file from below

Best Regards,
Dangar

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

[SachinNandanwar]

@PhillipC1 

This is my attempt.The table name in my solution is new 11_2

Create two calculated columns :

Rank = RANKX (
    FILTER (
        'new 11_2',
       'new 11_2'[ID] = EARLIER ( 'new 11_2'[ID])
    ),
    'new 11_2'[Week].[Date],
    , 
    ASC,Dense
)

PreviousCategory = 
VAR i = ( 'new 11_2'[Rank] )
VAR pi =
    CALCULATE (
        MAX ( 'new 11_2'[Rank] ),
        ALLEXCEPT ( 'new 11_2', 'new 11_2'[id] ),
        'new 11_2'[Rank] < i
    )
RETURN
    CALCULATE (
        MAX ( 'new 11_2'[Category] ),
        ALLEXCEPT ( 'new 11_2', 'new 11_2'[ID] ),
        'new 11_2'[Rank] = pi
    )

and then create a measure to calculate the final status.

Final_Status =
VAR _CurrentCategory = MAX ( 'new 11_2'[Category] )
VAR _NextCategory = MAX ( 'new 11_2'[PreviousCategory] )
RETURN
    SWITCH (
        TRUE (),
        _CurrentCategory = "Moderate"
            && _NextCategory = "Authorized"
            || _CurrentCategory = "High"
            && _NextCategory = "Moderate"
            || _CurrentCategory = "High"
            && _NextCategory = "Authorized", "UP",
            _NextCategory = BLANK ()
            || _NextCategory = _CurrentCategory, "NO CHANGE",
        "DOWN"
    )

[PhillipC1]

This looks good. If we wanted to add another category for example "Critical" which would be up from High how would we add this? So the flow would go from Authorized -> Moderate -> High -> Critical. I am a little confused by what exactly is taking place here.

[SachinNandanwar · Accepted solution]

So basically it compares the category for the current week with the previous weeks categoty..

VAR _NextCategory = MAX ( 'new 11_2'[PreviousCategory] )

the variable name is a misnomer.I named it wrongly, It should had been _PreviousCategory.

Maybe that was the confusion.

   _CurrentCategory = "Moderate" && _PreviousCategory = "Authorized"
|| _CurrentCategory = "High"     && _PreviousCategory = "Moderate"
|| _CurrentCategory = "High"     && _PreviousCategory = "Authorized"
|| _CurrentCategory = "Critical" && _PreviousCategory = "Moderate"
|| _CurrentCategory = "Critical" && _PreviousCategory = "High"
|| _CurrentCategory = "Critical" && _PreviousCategory = "Authorized" ,"UP"
 

I have reformatted the code and renamed the variable to _PreviousCategory.I think now it would be easy for you to adjust the formula.

[PhillipC1]

Got it, that works! Thank you so much.

[Dangar332]

Hi, @PhillipC1 

Make Week Number Column

Week_number = WEEKNUM('Table'[Week],1)

Try below Measure 

Measure 2 = 
VAR a = MAXX(
		FILTER(
			ALL(
				'Table'[ID],
				'Table'[Week_number]
			),
			'Table'[ID] = MIN('Table'[ID]) && 'Table'[Week_number] < MIN('Table'[Week_number])
		),
		[Week_number]
	)
	VAR b = IF(
		ISBLANK(a),
		MIN('Table'[Week_number]),
		a
	)
	VAR c = MINX(
		FILTER(
			ALL(
				'Table'[ID],
				'Table'[Category],
				'Table'[Week_number]
			),
			'Table'[ID] = MIN('Table'[ID]) && 'Table'[Week_number] = b
		),
		'Table'[Category]
	)
	VAR c1 = SWITCH(
		c,
		"Authorized", 1,
		"Moderate", 2,
		"High", 3
	)
	VAR d = SWITCH(
		MIN('Table'[Category]),
		"Authorized", 1,
		"Moderate", 2,
		"High", 3
	)

	RETURN
		SWITCH(
			TRUE(),
			c1 = d, "NO Change",
			c1 < d, "UP",
			"Down"
		)

Download File From below

Best Regards,
Dangar

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

[PhillipC1]

Thank you Dangar! What happens when a new year starts with this? If week number is 1-52 eventually this will rollover into a new year and then we will get repeats in that value. 

[Dangar332 · Accepted solution]

Hi, @PhillipC1 

try below code for Week Number Column

Week_number = 
var a = WEEKNUM('Table'[Week],1)
var currentYear = YEAR('Table'[Week])
var Current_id = 'Table'[ID] 
var Previousweek = MAXX(FILTER('Table',YEAR('Table'[Week])<currentYear && 'Table'[ID]=Current_id ),WEEKNUM('Table'[Week],1))
var final = IF(ISBLANK(Previousweek),a,Previousweek+a)
RETURN
final

The Measure is same as Above

Measure 2 = 
VAR a = MAXX(
		FILTER(
			ALL(
				'Table'[ID],
				'Table'[Week_number]
			),
			'Table'[ID] = MIN('Table'[ID]) && 'Table'[Week_number] < MIN('Table'[Week_number])
		),
		[Week_number]
	)
	VAR b = IF(
		ISBLANK(a),
		MIN('Table'[Week_number]),
		a
	)
	VAR c = MINX(
		FILTER(
			ALL(
				'Table'[ID],
				'Table'[Category],
				'Table'[Week_number]
			),
			'Table'[ID] = MIN('Table'[ID]) && 'Table'[Week_number] = b
		),
		'Table'[Category]
	)
	VAR c1 = SWITCH(
		c,
		"Authorized", 1,
		"Moderate", 2,
		"High", 3
	)
	VAR d = SWITCH(
		MIN('Table'[Category]),
		"Authorized", 1,
		"Moderate", 2,
		"High", 3
	)

	RETURN
		SWITCH(
			TRUE(),
			c1 = d, "NO Change",
			c1 < d, "UP",
			"Down"
		)

You can Download file from below

Best Regards,
Dangar

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

[PhillipC1]

Thank you, I tested this as well and it works.