Forum Discussion
Calculating Row Difference by Employees
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
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.
6 Replies
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 | LinkedInIf this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.
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.
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.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.
