Calculate the value from previous row

[jiangxm80]

I have a table as the following.

Column1    Column2

A                 1000

B                   800

B                   500

B                   300

I would like to create one more column

Column1    Column2   Column3

A                 1000            200

B                   800            300

B                   500            200

B                   300            300

How could I make that?

Thanks.

[ryan_mayu · Accepted solution]

@jiangxm80 

you can try to create an index column in PQ

then create a column

Column = 
VAR _next=maxx(FILTER('Table','Table'[Index]=EARLIER('Table'[Index])+1),'Table'[Column2])
return 'Table'[Column2]-_next

[Jihwan_Kim · Accepted solution]

Hi, 

I assume the order is by column 2.

Please check the below picture and the attached pbix file.

Column3 CC =
VAR _number =
    MAXX (
        FILTER ( Data, Data[Column2] < EARLIER ( Data[Column2] ) ),
        Data[Column2]
    )
RETURN
    IF ( NOT ISBLANK ( _number ), Data[Column2] - _number, Data[Column2] )

[jiangxm80]

resolved, perfect!

[ryan_mayu · Accepted solution]

@jiangxm80 

you can try to create an index column in PQ

then create a column

Column = 
VAR _next=maxx(FILTER('Table','Table'[Index]=EARLIER('Table'[Index])+1),'Table'[Column2])
return 'Table'[Column2]-_next