Forum Discussion

jiangxm80's avatar
jiangxm80
Icon for Helper I rankHelper I
3 years ago
Solved

Calculate the value from previous row

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.

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    3 years ago

    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's avatar
    Jihwan_Kim
    3 years ago

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

     

3 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

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