Comparing 2 month data for employee movements

[adhumal2]

Dear Friends,

I want to compare data of 2 consecutive months to see if the there are any employee movements and want to capture those movements as output. The movements are as mentioned below 

- Exit - If someone has left the company (appears in previous month , but not in current month data)

- Entry - If someone new has joined the company (appears in current month , but not in previous month data)

- Transfer in - If someone has moved from one position to another position within the organization (the output should show new position related data)

- Transfer Out - If someone has moved from one position to another position within the organization (the output should show old position related data)

Here are the sample files (there are multiple other columns in the original file)

Let me explain each of the employee movement as you can see in the ouput file 

- Employees appearing in both files need not to be captured (we want to capture only employee movements)

- Sunil Shetty has 2 positions (with Full time equivalent value 0.5 and 0.5) in previous month, however in current month his position and org ID has changed for one of the positions. In this case He moves out from position 8888 and gets transferred to position 9998. Hence his movement has been captured as 'transfer out' from the position 8888 and at the same time his movement has been captured as 'transfer in' in the position 9998

- Virat Kohli is also working on 2 positions (with full time equivalent value 0.6 and 0.4) , one of the position (5555 gets delimited. The movement has been captured as 'transfer out'  (not as exit because , he still appears in both month data due to other position)

- Rohit Sharma appears as Exit, because he is not in current month data

- Hardik Pandya appears as 'Transfer in' , though he is on the same position, his FTE value has increased by 0.2 FTE

-Ravindra Jadeja appears as Entry, because he is not in previous month data.

How to create the output using powerBI? Is it possible ? How to do it?

Previous MonthCurrent MonthOutput

[v-eachen-msft · Accepted solution]

Hi @adhumal2 ,

At first, you need to add a column on each table.

Then append these two tables to get a new table. Add an index column and create a new column "countrows".

countrows =
COUNTROWS (
    FILTER (
        Append1,
        Append1[EName] = EARLIER ( Append1[EName] )
            && Append1[OUnit] = EARLIER ( Append1[OUnit] )
            && Append1[FTE] = EARLIER ( Append1[FTE] )
    )
)

Create a new table.

Table =
CALCULATETABLE ( Append1, Append1[countrows] = 1 )

Create three calculated columns:

rank =
RANKX (
    FILTER ( 'Table', 'Table'[EName] = EARLIER ( 'Table'[EName] ) ),
    'Table'[Index],
    ,
    ASC,
    DENSE
)

FTE(Full Time) =
VAR A =
    CALCULATE (
        FIRSTNONBLANK ( 'Table'[FTE], 1 ),
        FILTER (
            'Table',
            'Table'[rank]
                = EARLIER ( 'Table'[rank] ) + 1
                && 'Table'[EName] = EARLIER ( 'Table'[EName] )
                && 'Table'[EID] = EARLIER ( 'Table'[EID] )
        )
    )
VAR c =
    COUNTROWS (
        FILTER (
            'Table',
            'Table'[EName] = EARLIER ( 'Table'[EName] )
                && 'Table'[OUnit] = EARLIER ( 'Table'[OUnit] )
        )
    )
RETURN
    IF (
        c = 1
            && 'Table'[Month] = "P",
        - 'Table'[FTE],
        IF (
            c = 1
                && 'Table'[Month] = "C",
            'Table'[FTE],
            IF ( 'Table'[rank] = 1 && c = 2, 'Table'[FTE] - A )
        )
    )

Remark =
VAR c =
    COUNTROWS ( FILTER ( 'Table', 'Table'[EName] = EARLIER ( 'Table'[EName] ) ) )
RETURN
    IF (
        c = 1
            && 'Table'[FTE(Full Time)] > 0,
        "Entry",
        IF (
            c = 1
                && 'Table'[FTE(Full Time)] < 0,
            "Exit",
            IF (
                c = 2
                    && 'Table'[FTE(Full Time)] < 0,
                "Transfer Out",
                IF ( c = 2 && 'Table'[FTE(Full Time)] > 0, "Transfer In" )
            )
        )
    )

Now you can use these fileds to get your visual.

Best Regards,

Eads

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

[adhumal2]

Thanks a lot.

[adhumal2]

@v-eachen-msft Thanks a lot. I will try this solution on my data and confirm the output soon. Many Thanks already.

[v-eachen-msft]

Hi @adhumal2 ,

If my answer is useful, please accept my reply.Thanks!

Best Regards,

Eads

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

[adhumal2]

Hi,

I tried the solution on my file and have below observations

-  If employee is on 2 positions (with FTE values 0.5 and 0.5), and if one of the position gets reduced by 0.25, still there will be 2 positions (0.5 and 0.25) , in this case the reduced position should be shown as transfer out with 0.25 FTE (only FTE value for 1 position has changed, both position IDs remain same)

- In some cases, though the FTE value has decreased, the FTE column in the 'Table' shows positive FTE value and hence the remark as ' Transfer In'

will you be really kind to help me on this topic?

@v-eachen-msft 

[adhumal2]

@v-eachen-msft Hi, Can you please look into the same and help me resolve!

[mariner84]

Currently, I am looking into ways to track based on employee movements but on a year-to-date as opposed to your month to previous comparison. I'm curious to see the solution to this as well!

[Ovidian]

@mariner84 Hi, Did you find the solution to track based on employees movements on a year-to-date? Thanks!