Counting State changes

[masterelaichi]

Hi,

I am not sure what the rules are about cross-posting. I have also posted this same query in ExceleratorBI. Apologies in advance if cross-posting isn't allowed

I have a table containing staff data. A certain staff member can, over time, change different position ( Position column in the data set)

I am trying to count how may times the position changes, i,e, If employee 1 is initially in Position A and then moves to B, that should count as 1. If, the next position remains at B, then no change. Basically, I am trying to include a code that flags a change in the position which can be counted

I am trying count the movement of an employee by position by month

How do I attach an excel file with a sample dataset ?

Thank you

 

[Anthony47]

How do I attach an excel file with a sample dataset ?

You need to use an external file sharing service; the easiest to use is, in my opinion, www.filedropper.com
When you upload a file you will be given a link to download the file: share it with your next message

Bye

 

[masterelaichi]

Thanks Anthony

Here is the link to the file

File Dropper - Online file sharing

Online file sharing

www.filedropper.com

 

[masterelaichi]

I think I have made some progress. I created an index column for the table and used variables and the Lookupvalue function. I created a new column called ChangeFlag which indicates when there is a change in the position

The flag conditions are as follows -
1. Same employee number, different position - flag is set to "Changed"
2. Different employee number, different or same position - flag is set to "No change"

ChangeFlag =

VAR currentPosition= LOOKUPVALUE(PersonPositionTable[Position],PersonPositionTable[Index],PersonPositionTable[Index],PersonPositionTable[Position])
VAR previousPosition = LOOKUPVALUE(PersonPositionTable[Position],PersonPositionTable[Index],PersonPositionTable[Index]-1,PersonPositionTable[Position])
VAR currentPersonnel = LOOKUPVALUE(PersonPositionTable[Emp number],PersonPositionTable[Index],PersonPositionTable[Index],PersonPositionTable[Emp number])
VAR previousPersonnel = LOOKUPVALUE(PersonPositionTable[Emp number],PersonPositionTable[Index],PersonPositionTable[Index]-1,PersonPositionTable[Emp number])
VAR flagPositionChange = if(or(currentPosition=previousPosition,currentPersonnel<>previousPersonnel),"No Change","Changed")

Return
flagPositionChange

I haven't found an anomaly as yet but I am still running some tests. If there is a simpler way to tackle this, I would love to know it :D