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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.