Forum Discussion

ehmacc's avatar
ehmacc
Frequent Visitor
6 years ago
Solved

Calculating the Difference in Earnings by Employee/Type

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. 

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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.

1 Reply

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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.