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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
jiangxm80
Frequent Visitor

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.

2 ACCEPTED SOLUTIONS
ryan_mayu
Super User
Super User

@jiangxm80 

you can try to create an index column in PQ

1.PNG

then create a column

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

2.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Jihwan_Kim
Super User
Super User

Hi, 

I assume the order is by column 2.

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_0-1668065748372.png

 

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

 

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.


Go to My LinkedIn Page


View solution in original post

3 REPLIES 3
Jihwan_Kim
Super User
Super User

Hi, 

I assume the order is by column 2.

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_0-1668065748372.png

 

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

 

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.


Go to My LinkedIn Page


resolved, perfect!

ryan_mayu
Super User
Super User

@jiangxm80 

you can try to create an index column in PQ

1.PNG

then create a column

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

2.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

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