DAX Query to find change in job level field

[madhav2020]

Hey all,

Can any of you help me out in getting a query for the below scenario.

Employee promotion table:

Emp No	Job Level	Period
123	1	Mar-19
456	2	Mar-19
654	1	Mar-19
987	3	Mar-19
223	5	Mar-19
123	1	Mar-20
456	3	Mar-20
654	2	Mar-20
987	3	Mar-20
223	6	Mar-20

On comparison of period between Mar-19 & Mar-20 formula should find change in employee level and give the output as below,

Emp No	Job Level	Period	Output
123	1	Mar-19
456	2	Mar-19
654	1	Mar-19
987	3	Mar-19
223	5	Mar-19
123	1	Mar-20	No Change
456	3	Mar-20	Promoted
654	2	Mar-20	No Change
987	3	Mar-20	No Change
223	6	Mar-20	Promoted

An urgent help is appretiated. Thanks in advance.  

[Jimmy801]

Hello @madhav2020 

there is for sure no solution without coding 🙂

Here an approach in Power Query

let
	Source = #table
	(
		{"Emp No","Job Level","Period"},
		{
			{"123","1","Mar-19"},	{"456","2","Mar-19"},	{"654","1","Mar-19"},	{"987","3","Mar-19"},	{"223","5","Mar-19"},	{"123","1","Mar-20"},	{"456","3","Mar-20"},	
			{"654","2","Mar-20"},	{"987","3","Mar-20"},	{"223","6","Mar-20"}
		}
	),
	TransDate = Table.TransformColumns
	(
		Source,
		{"Period", each Date.From(_, "en-GB"), type date}
	),
    #"Sorted Rows" = Table.Sort(TransDate,{{"Emp No", Order.Ascending}, {"Period", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Emp No"}, {{"AllRows", each _, type table [Emp No=text, Job Level=text, Period=date]}}),
	AddColumn = Table.TransformColumns
	(
		#"Grouped Rows",
		{
			"AllRows",
			(tableint)=>
			let 
				AddC = Table.AddColumn
				(
					tableint,
					"Change?",
					(add)=>
					try 
						if Table.SelectRows(tableint, each [Index]= add[Index]-1)[Job Level]{0}=add[Job Level] then "No change" else "change"
					otherwise
						"No change"
				)
			in
				AddC
		}
	),
    #"Expanded AllRows" = Table.ExpandTableColumn(AddColumn, "AllRows", {"Job Level", "Period", "Change?"}, {"Job Level", "Period", "Change?"})

in
	#"Expanded AllRows"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[madhav2020]

Hey jim.

Thanks for painstaking effort in coding so many lines. Will check and get back to you.

[harshnathani · Accepted solution]

Hi @madhav2020 ,

Promtion or not = 

var _getpriorvalue = CALCULATE(MAX('Table'[Job Level]), FILTER('Table', 'Table'[Emp No] = EARLIER('Table'[Emp No]) && 'Table'[Period] < EARLIER('Table'[Period])))

RETURN

SWITCH(
    TRUE(),
    _getpriorvalue = 'Table'[Job Level] && _getpriorvalue <> BLANK(), "No Change",
    _getpriorvalue < 'Table'[Job Level] &&  _getpriorvalue <> BLANK(), "Promoted",
    BLANK()
)

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Funmilash22]

Is there a way to do this for demotions so go from level 2 to 3 back to level 1?

[madhav2020]

Hey harsh thanks ! Will check and get back to you