Comparing 2 month data for employee movements

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.