In need help with a measure for difference between two rows

[RobBeijers312]

For a report I need the accrual of vacation hours per year and per leave definition. This can be calculated by subtracting the column "leavehoursbalance" from the column "Totalentitlementhours" next year.

For example:

totalentitlementhours from leavedefinitionid 5006 in year 2025 = 202

leavehourbalanc from leavedefinitionid 5006 in year 2024 = 164

202 - 164 = 38

Measure I need = The acruall of leavedefinitionid 5006 in year 2025 is 38.

Thanks in advance

[RobBeijers312]

Hi @bhanu_gautam ,

The measure doesnt work at this moment. The current outcome is the oucome of CurrentEntitlement. 

The fault at this moment is PreviousBalance. When I return PreviousBalance as the outcome the outcome is blank.

[RobBeijers312]

Hi @bhanu_gautam ,

Thanks for the reply.

Currently the outcome of the measure is the sum of totalentitlementhours. So it seems that the problem lies with the formula of the PreviousBalance. When I only return PreviousBalance the outcome is unfortunately blank.

[bhanu_gautam · Accepted solution]

@RobBeijers312 Try using this version

Accrual =
VAR CurrentYear = MAX('Table'[year])
VAR PreviousYear = CurrentYear - 1
VAR CurrentEntitlement =
CALCULATE(
SUM('Table'[totalentitlementhours]),
'Table'[year] = CurrentYear
)
VAR PreviousBalance =
CALCULATE(
SUM('Table'[leavehoursbalance]),
'Table'[year] = PreviousYear
)
RETURN
IF(
ISBLANK(PreviousBalance),
BLANK(),
CurrentEntitlement - PreviousBalance
)

[RobBeijers312]

@bhanu_gautam thanks, it works!

[bhanu_gautam]

@RobBeijers312 , Try using measure

DAX
Accrual =
VAR CurrentYear = MAX('Table'[year])
VAR PreviousYear = CurrentYear - 1
VAR CurrentEntitlement =
CALCULATE(
SUM('Table'[totalentitlementhours]),
'Table'[year] = CurrentYear
)
VAR PreviousBalance =
CALCULATE(
SUM('Table'[leavehoursbalance]),
'Table'[year] = PreviousYear
)
RETURN
CurrentEntitlement - PreviousBalance