cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
adhumal2 Frequent Visitor
Frequent Visitor

Comparing 2 month data for employee movements

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?

 

File 1.PNGPrevious MonthFile 2.PNGCurrent MonthOutput.PNGOutput

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Comparing 2 month data for employee movements

Hi @adhumal2 ,

 

At first, you need to add a column on each table.4-1.PNG

 

4-2.PNG

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] )
    )
)

4-3.PNG

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.4-4.PNG

 

4-5.PNG

 

Best Regards,

Eads

 

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

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Community Support Team
Community Support Team

Re: Comparing 2 month data for employee movements

Hi @adhumal2 ,

 

At first, you need to add a column on each table.4-1.PNG

 

4-2.PNG

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] )
    )
)

4-3.PNG

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.4-4.PNG

 

4-5.PNG

 

Best Regards,

Eads

 

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

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
adhumal2 Frequent Visitor
Frequent Visitor

Re: Comparing 2 month data for employee movements

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

Community Support Team
Community Support Team

Re: Comparing 2 month data for employee movements

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.

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
adhumal2 Frequent Visitor
Frequent Visitor

Re: Comparing 2 month data for employee movements

Thanks a lot.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 378 members 3,644 guests
Please welcome our newest community members: