Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 )
)
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
7 | |
7 | |
6 | |
6 |
User | Count |
---|---|
27 | |
12 | |
10 | |
9 | |
6 |