Reply
RobBeijers312
New Member

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.

 

DAX vacation hours.png

 

 

Thanks in advance

1 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
)




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






View solution in original post

5 REPLIES 5
RobBeijers312
New Member

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_0-1741958204736.png

 

 

RobBeijers312
New Member

Hi @bhanu_gautam ,

 

Thanks for the reply.

RobBeijers312_0-1741954321766.png

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.

 

@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
)




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






@bhanu_gautam thanks, it works!

bhanu_gautam
Super User
Super User

@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




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)