Calculated Column to Flag Change in Value by Employee ID, Month Over Month

[Anonymous]

Thanks for any help you can provide!

I have spend too much time trying to figure this out, so figured it was time to bring it here.  I have searched other threads, but am not finding a relatable solution...

I have a table of employee data that contains a row for each month, for each employee - for up to 28 months.  In other words, Employee A will have 28 rows of data showing their status and particulars for that month.  

I am trying to create a few calculated columns to essentially flag changes in employee details (position code, position type, company, etc.) from one month to the next.  I want the calculated colum to show "1" if there was a change from the prior month, and "0" for no change.  

I have created a sample table to help explain.  You can see that Employee 0099 has multiple months of data, with a change in Company in 8/2022.  Therefore, a "1" shows in the CoCodeChg column.

I have tried variables, EARLIER, IF statements - all with no luck.  

Looking forward to any ideas this group may have. 

[TerryBear]

@amitchandak - can you please provide some guidance on how you would declare the _month variable?  Thanks!

[AquaTard]

TrackChangeFlag = var _max = maxx(filter( 'Action', 'Action'[EmployeeId] = earlier( 'Action'[EmployeeId]) && 'Action'[ActionId] < earlier( 'Action'[ActionId] )), 'Action'[ActionId] )

return

if( isblank(_max) || 'Action'[Track]= maxx(filter( 'Action', 'Action'[EmployeeId] = earlier( 'Action'[EmployeeId] )&& 'Action'[ActionId] =_max), 'Action'[Track] ),0,1)

-- Note I set EmployeeID = instead of < as my data needs grouped by Employee

[AquaTard]

I had to make a few changes to make this work with my data (notably I have more 'actions' than just 'job status changes' in my table).  The _month variable you're referring to is a typo - the original poster meant to reference the _max variable created in the measure.

[amitchandak · Accepted solution]

@Anonymous ,New column

Flag = var _max = maxx(filter( 'Table', 'Table'[persiD] < earlier( 'Table'[persiD] ) && 'Table'[Month] < earlier( 'Table'[Month] )), 'Table'[Month] )
return
if( isblank(_max) || 'Table'[CoCOde] = maxx(filter( 'Table', 'Table'[persiD] < earlier( 'Table'[persiD] ) && 'Table'[Month] =_month), 'Table'[CoCOde] ),0,1)

Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8

[Anonymous]

@amitchandak Thanks so much for your reply!  What you shared is similar to what I have tried, but your logic is definitely better than what I was coming up with. 

I am testing it out now, but am getting an error for the = _month near the end.  Would it be necessary to create another variable for month?