cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SSS
Helper I
Helper I

Difference between two rows

Hi,

 

I need help in order to solve a problem trying to calculate the differences between two rows in Power BI.

 

My tables look like:

Date              Orders             Index

01/01/2017   8501               1

02/01/2017   8345               2
03/01/2017   7985               3
04/01/2017   8134               4

I would need to performt the difference betweent the rows of Order in order to get something like:

 

Difference

156 (from 8501-8345)

360 (from 8345-7985)

-149 (from 7985-8134)

 

I've tried to add a column with:

=Orders{Index}-Orders{Index+1}

 

but it does not work.

 

I will appreciate any help. Thanks

1 ACCEPTED SOLUTION

Hi @SSS

 

Using DAX you can add this calculated column to get desired results

 

=
VAR NextIndex = Table1[Index] + 1
RETURN
    Table1[Orders]
        - CALCULATE (
            VALUES ( Table1[Orders] ),
            FILTER ( ALL ( Table1 ), Table1[Index] = NextIndex )
        )

Regards
Zubair

Please try my custom visuals

View solution in original post

25 REPLIES 25

Hi @SSS

 

Using DAX you can add this calculated column to get desired results

 

=
VAR NextIndex = Table1[Index] + 1
RETURN
    Table1[Orders]
        - CALCULATE (
            VALUES ( Table1[Orders] ),
            FILTER ( ALL ( Table1 ), Table1[Index] = NextIndex )
        )

Regards
Zubair

Please try my custom visuals

View solution in original post


@Zubair_Muhammad wrote:

Hi @SSS

 

Using DAX you can add this calculated column to get desired results

 

=
VAR NextIndex = Table1[Index] + 1
RETURN
    Table1[Orders]
        - CALCULATE (
            VALUES ( Table1[Orders] ),
            FILTER ( ALL ( Table1 ), Table1[Index] = NextIndex )
        )

And without Index column?

Anonymous
Not applicable

Hi @Zubair_Muhammad,

 

Congrats for the solution.

 

Can I take ask for a complementary demand on the same idea?

 

As the original demand, I need to execute the difference between two dates located in different rows, but I need to consider a key. It is something like this:

 

Key                     Date

1                       01/08/2018

1                       12/09/2018

1                        05/10/2018

2                       02/07/2018

2                        16/08/2018

 

Thanks a lot in advance.

 

Regards.

Can we make the difference as new row and named Difference?

thanks for the solution, it works perfectly

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors