Forum Discussion
Calculating Row Difference by Employees
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.
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.
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
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.