Forum Discussion

RobBeijers312's avatar
RobBeijers312
Helper I
1 year ago
Solved

In need help with a measure for difference between two rows

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

    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's avatar
RobBeijers312
Helper I
1 year ago

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's avatar
bhanu_gautam
Super User
1 year ago

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
)