Row differences using DAX (PowerBI)

[frankly]

¿What would be the simplest DAX formula in PowerBI to calculate row differences?

I tried using EARLIER() but it cannot handle the operation at the limit (missing data):

EARLIER/EARLIEST refers to an earlier row context which doesn't exist.

row	value	diff(value)?
1	5
2	1	-4
3	6	5
4	7	1
5	3	-4
6	3	0
7	1	-2
8	1	0
9	8	7

[rubayatyasmin]

Hi, @BjoernSchaefer 

try this

Difference =
CALCULATE(
SUM('Data'[value]),
FILTER(
ALL('Data'),
'Data'[row] = EARLIER('Data'[row]) - 1
)
) - 'Data'[value]

the code will only work if your row column has unique value. If not then create an index column starting from 0. 

If you have missing values in your value column then try this: make sure you have an index column starting from0

Difference =
LOOKUPVALUE (
Data[value],
Data[Index],
Data[Index] - 1,
BLANK ()
) - Data[value]

this thread might help:

Solved: I need output like this by using measure ( calcula... - Microsoft Fabric Community

[rubayatyasmin]

@frankly Or you can use IF statememnt:

Difference =
IF(
Tabelle[row] = 1,
BLANK(),
(LOOKUPVALUE(Tabelle[value], Tabelle[row], Tabelle[row] - 1) - Tabelle[value]) * -1
)

[ERD]

Hi @frankly ,

You can try this measure:

diff =
VAR _current_row = SELECTEDVALUE ( Table_[row] )
VAR _current_value = SELECTEDVALUE ( Table_[value] )
VAR _prev_row = MAXX ( FILTER ( ALL ( Table_[row] ), Table_[row] < _current_row ), Table_[row] )
VAR _prev_value = MAXX ( FILTER ( ALL ( Table_ ), Table_[row] = _prev_row ), Table_[value] )
RETURN
    IF ( NOT ISBLANK ( _prev_value ), _current_value - _prev_value )

+

For the calculated coumn:

Column =
VAR _current_row = Table_[row]
VAR _current_value = Table_[value]
VAR _prev_row = MAXX ( FILTER ( VALUES ( Table_[row] ), Table_[row] < _current_row ), Table_[row] )
VAR _prev_value = MAXX ( FILTER ( Table_, Table_[row] = _prev_row ), Table_[value] )
RETURN
    IF ( NOT ISBLANK ( _prev_value ), _current_value - _prev_value )

[BjoernSchaefer]

Hey frankly,

i was able to do it with SWITCH and LOOKUPVALUE.
Maybe there's a more elegant way.

formula = SWITCH(TRUE(),Tabelle[row]=1,BLANK(),(LOOKUPVALUE(Tabelle[value],Tabelle[row],Tabelle[row]-1)-Tabelle[value])*-1)

Kind Regards

Björn