Calculating Row Difference by Employees

[katina]

Hi all,

I've been studying many of the previous subjects here and in Youtube, but I couldn't find a solution for my problem. I assume this is a quite simple to solve but it seems I don't have enough competence to implement it.
How do I calculate the Pay Increase row by row for for each employee. I did an illustration in Excel, and the red marked column is what I need:

Many thanks already in advance if you're able to help me with this 🙂

-katina

[Anonymous]

Hi @katina,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, kindly "Accept  as  Solution" and give it a 'Kudos' so others can find it easily.

Thank you,
Pavan.

[Anonymous]

Hi @katina,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please "Accept  as  Solution" and give a 'Kudos' so other members can easily find it.

Thank you,
Pavan.

[FarhanJeelani · Accepted solution]

Hi @katina ,

You can calculate the Pay Increase in Power BI using DAX by comparing the current row's Monthly Pay with the previous row's Monthly Pay for the same employee.

Use the following DAX formula in Power BI to create a calculated column:

Pay_Increase =
VAR PrevPay =
CALCULATE(
MAX('Table'[Monthly Pay]),
FILTER(
'Table',
'Table'[Employee_ID] = EARLIER('Table'[Employee_ID]) &&
'Table'[Valid_Date] < EARLIER('Table'[Valid_Date])
)
)
RETURN
IF(ISBLANK(PrevPay), 0, 'Table'[Monthly Pay] - PrevPay)

Explanation:
Find Previous Pay → Using CALCULATE(), we filter the table to get the Monthly Pay from the latest Valid_Date before the current row’s date for the same Employee_ID.

Subtract → If a previous record exists, we subtract it from the current Monthly Pay. If there’s no previous pay (first row for an employee), return 0.

Please mark this post as solution if it helps you. Appreciate kudos.

[katina]

Hi,

yes that works - almost 🙂 As I tried this to my current data I noticed a new situation: When an employee has a decrease in monhtly pay, then MAX function causes problems - it keeps the maximum monthly pay and calculates the rest row differences comparing to it. How can I tackle these possible pay decreases, too? - katina

[Anonymous]

Hi @katina,

Thank you for reaching out in Microsoft Community Forum.

Thank you @FarhanJeelani  @pankajnamekar25  for the helpful response.

We hope your issue has been resolved. If so, please consider marking it as "Accept as Solution" and giving it a 'Kudos' if it was helpful. This will help other members find it more easily.

Please continue using Microsoft community forum.

Regards,
Pavan.

[pankajnamekar25]

Hello @katina 

You can use DAX to calculate new column

Pay_Increase =
VAR CurrentPay = Employees[Monthly Pay]
VAR PreviousPay =
CALCULATE(
MAX(Employees[Monthly Pay]),
FILTER(
Employees,
Employees[Employee_ID] = EARLIER(Employees[Employee_ID]) &&
Employees[Valid_Date] < EARLIER(Employees[Valid_Date])
)
)
RETURN
IF(ISBLANK(PreviousPay), 0, CurrentPay - PreviousPay)

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.