topic Re: Calculated Column to Flag Change in Value by Employee ID, Month Over Month in DAX Commands and Tips

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

Anonymous — Wed, 22 Feb 2023 16:34:42 GMT

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.

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

amitchandak — Wed, 22 Feb 2023 16:48:09 GMT

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

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

Anonymous — Wed, 22 Feb 2023 18:28:12 GMT

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?

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

TerryBear — Tue, 18 Apr 2023 14:36:28 GMT

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

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

AquaTard — Fri, 22 Sep 2023 02:44:09 GMT

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.

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

AquaTard — Fri, 22 Sep 2023 02:45:28 GMT

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