Forum Discussion

katina's avatar
katina
Helper II
1 year ago
Solved

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 eno...
Need Help
  • FarhanJeelani's avatar
    FarhanJeelani
    1 year ago

    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.

pankajnamekar25's avatar
pankajnamekar25
Super User
1 year ago

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.