Advance your Data & AI career with 50 days of live learning, dataviz contests, hands-on challenges, study groups & certifications and more!
Get registeredJoin us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM. Register now.
Hello
I'm trying to work out in Dax how to calculate the difference in value between 2 dates with an added variance.
So in essence the difference for 01/07/2024 is Value for 02/07/2024 - (Value for 01/07/2024 + Transactions for 01/07/2024) I just can't wrap my head around the DAX
| Date | Value | Transactions | Difference |
| 01/07/2024 | 50000 | 1000 | 1000 |
| 02/07/2024 | 52000 | 3500 | 1500 |
| 03/07/2024 | 57000 | 3000 | -1000 |
| 04/07/2024 | 59000 | 1500 | -500 |
| 05/07/2024 | 60000 | 500 |
Solved! Go to Solution.
Hi,
I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
Difference measure: =
VAR _nextrowvalue =
CALCULATE (
SUM ( Data[Value] ),
OFFSET (
1,
ALL ( Data ),
ORDERBY ( Data[Date], ASC ),
,
,
MATCHBY ( Data[Date] )
)
)
VAR _currentrowvalue =
SUM ( Data[Value] )
VAR _currentrowtransaction =
SUM ( Data[Transactions] )
RETURN
IF (
NOT ISBLANK ( _nextrowvalue ) && HASONEVALUE ( Data[Date] ),
_nextrowvalue - ( _currentrowvalue + _currentrowtransaction )
)
Hi @SarahESkells ,
In addition to the solution provided by Jihwan_Kim, you can also produce the required output by writing calculated column like below:
Variance =
VAR NextRow =
CALCULATE (
SUM ( [Value] ),
FILTER ( 'Table', 'Table'[Date] = EARLIER ( 'Table'[Date] ) + 1 )
)
VAR CurrentValue = 'Table'[Value]
VAR CurrentTransaction = 'Table'[Transactions]
RETURN
IF ( NextRow = BLANK (), BLANK (), NextRow - CurrentValue - CurrentTransaction )
I attach an example pbix file.
Best regards,
hi @SarahESkells Please check the snapshot of the formula
if this solves your issue then please accept the same as the solution.
hi @SarahESkells Please check the snapshot of the formula
if this solves your issue then please accept the same as the solution.
Hi @SarahESkells ,
In addition to the solution provided by Jihwan_Kim, you can also produce the required output by writing calculated column like below:
Variance =
VAR NextRow =
CALCULATE (
SUM ( [Value] ),
FILTER ( 'Table', 'Table'[Date] = EARLIER ( 'Table'[Date] ) + 1 )
)
VAR CurrentValue = 'Table'[Value]
VAR CurrentTransaction = 'Table'[Transactions]
RETURN
IF ( NextRow = BLANK (), BLANK (), NextRow - CurrentValue - CurrentTransaction )
I attach an example pbix file.
Best regards,
Hi,
I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
Difference measure: =
VAR _nextrowvalue =
CALCULATE (
SUM ( Data[Value] ),
OFFSET (
1,
ALL ( Data ),
ORDERBY ( Data[Date], ASC ),
,
,
MATCHBY ( Data[Date] )
)
)
VAR _currentrowvalue =
SUM ( Data[Value] )
VAR _currentrowtransaction =
SUM ( Data[Transactions] )
RETURN
IF (
NOT ISBLANK ( _nextrowvalue ) && HASONEVALUE ( Data[Date] ),
_nextrowvalue - ( _currentrowvalue + _currentrowtransaction )
)
Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!
Check out the October 2025 Power BI update to learn about new features.
| User | Count |
|---|---|
| 8 | |
| 6 | |
| 3 | |
| 3 | |
| 3 |
| User | Count |
|---|---|
| 13 | |
| 9 | |
| 8 | |
| 8 | |
| 7 |