- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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
)
Proud to be a Super User! |
|
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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
)
Proud to be a Super User! |
|
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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
Proud to be a Super User! |
|

Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - February 2025
Check out the February 2025 Power BI update to learn about new features.

Subject | Author | Posted | |
---|---|---|---|
04-16-2024 04:45 AM | |||
07-18-2024 08:11 PM | |||
06-16-2024 08:23 PM | |||
05-22-2024 06:11 AM | |||
08-13-2024 11:53 AM |