Calculating the Difference in Earnings by Employee/Type

[ehmacc]

Hello!

I am trying to add a caluclated column to my data that will show for lines of current checks the difference in the earnings amount compared to the previous check. I Only need the data to show values if the line is the Current Check Line. Example below- desired output in Red- For employee 1, their Regular Earnings did not change, but their Commission was $300 less; Employee 2 had a $500 increase in Regular Earnings. 

 Is this possible? The longer term output is that I will be creating a quick table visual for managers to be able to see the difference in the earnings by employee for the current check compared to last check. 

[v-juanli-msft · Accepted solution]

Hi @ehmacc 

Create a calculated column below

diff =
VAR last_date =
    CALCULATE (
        MAX ( 'Table'[Check Date] ),
        FILTER (
            'Table',
            'Table'[Employee ID] = EARLIER ( 'Table'[Employee ID] )
                && 'Table'[Earning Type] = EARLIER ( 'Table'[Earning Type] )
                && 'Table'[Check Date] < EARLIER ( 'Table'[Check Date] )
        )
    )
VAR previous_value =
    CALCULATE (
        SUM ( 'Table'[Current Amount] ),
        FILTER (
            'Table',
            'Table'[Employee ID] = EARLIER ( 'Table'[Employee ID] )
                && 'Table'[Earning Type] = EARLIER ( 'Table'[Earning Type] )
                && 'Table'[Check Date] = last_date
        )
    )
RETURN
    IF ( previous_value = BLANK (), 0, [Current Amount] - previous_value )

Best Regards
Maggie

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