Forum Discussion

RobBeijers312's avatar
RobBeijers312
Icon for Helper I rankHelper 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 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

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
    )

5 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    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

  • RobBeijers312's avatar
    RobBeijers312
    Icon for Helper I rankHelper 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
      Icon for Super User rankSuper 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
      )

  • RobBeijers312's avatar
    RobBeijers312
    Icon for Helper I rankHelper I
    1 year ago

    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.