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

[SarahESkells]

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

[Jihwan_Kim · Accepted 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 )
    )

[DataNinja777 · Accepted 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,

[Anonymous · Accepted solution]

hi @SarahESkells Please check the snapshot of the formula

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

[DataNinja777 · Accepted 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,

[Jihwan_Kim · Accepted 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 )
    )