Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
SarahESkells
Helper I
Helper I

Calculating difference between 2 rows of data based on date and movement

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

 

DateValueTransactionsDifference
01/07/20245000010001000
02/07/20245200035001500
03/07/2024570003000-1000
04/07/2024590001500-500
05/07/202460000500 

 

3 ACCEPTED SOLUTIONS
Jihwan_Kim
Super User
Super User

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.

 

Jihwan_Kim_0-1721396774505.png

 

 

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

View solution in original post

DataNinja777
Super User
Super User

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 )

 

 

DataNinja777_0-1721399776173.png

 

 

I attach an example pbix file.  

Best regards,

View solution in original post

Rakesh1705
Super User
Super User

hi @SarahESkells Please check the snapshot of the formula

Rakesh1705_0-1721727875384.png

if this solves your issue then please accept the same as the solution.

View solution in original post

3 REPLIES 3
Rakesh1705
Super User
Super User

hi @SarahESkells Please check the snapshot of the formula

Rakesh1705_0-1721727875384.png

if this solves your issue then please accept the same as the solution.

DataNinja777
Super User
Super User

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 )

 

 

DataNinja777_0-1721399776173.png

 

 

I attach an example pbix file.  

Best regards,

Jihwan_Kim
Super User
Super User

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.

 

Jihwan_Kim_0-1721396774505.png

 

 

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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