Difference between two rows

[SSS]

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

[pxg08680]

@SSS

[Anonymous]

@pxg08680 

I have a similar case and will use same example. Let's say there's another column named "Store" with values 'Store1', 'Store2' and
'Store3'. Is it possible to still use DAX and have 1 table insetad of 3? As current example at some points gives me order diffrence between 'Store1'
and 'Store2'.

[MarcelBeug]

It looks lik you are a looking for a solution in Power Query?

In general, if you want to compare values from different rows, you can add 2 index columns, one starting with 0 and the other with 1.

Then you merge the query with itself, using the index columns as merge columns.

If you want the data from the previous row on the current row, then you should merge on Index 0 and Index 1 (in this sequence);

if you want the data from the next row on the current row, then you should mergen on Index 1 and Index 0.

After merging, adjust the generated code and name your column "Previous" or "Next".

Exoand, using the original column name as prefix,

After expanding sort on one of the original index columns.

Below the code for your case.

let
    Source = Table1,
    #"Added Index" = Table.AddIndexColumn(Source, "Index.0", 0, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index",{"Index"},#"Added Index",{"Index.0"},"Next",JoinKind.LeftOuter),
    #"Expanded Next" = Table.ExpandTableColumn(#"Merged Queries", "Next", {"Orders"}, {"Next.Orders"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Next",{{"Index", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Difference", each [Orders] - [Next.Orders]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index.0", "Next.Orders"})
in
    #"Removed Columns"

[TorbenKirkWolf]

Well, that's a neat trick! This thread showed up on a google search, and it's exactly what I needed. So I just want to let you know that your reply still lives on and helps people 🙂

I've never thought of merging a table with itself on different indexes, but now you've mentioned it, it's such an obvious and elegant solution. I would ever have thought of this myself, so a big thanks from me!

[Zubair_Muhammad · 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 )
        )

[giannicarioni]

Hi, I'm totally new to Power Query and DAX. I need a solution to calculate difference of a value beteen two rows, and tried this one. But when I create a new column and paste the code, I get the error Token Expected: 

I can't find a solution to this problem, probably because I don't know DAX fundamentals. Thank you.

[tbone_wmy]

Hi Zubair,

Is there a way to do this in measure instead of calculated column? My situation is that I need to calculate the average of these differences. But my table changes every time user changes the filter in the visual. So creating one calculated column from a fixed table won't work for me. Thanks in advance!

[Ghibbli]

---

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

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.

[htalat6]

i am actually looking to find the solution to the same usecase.
Please share if you find out anything

[fxz9]

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

[david7f]

thanks for the solution, it works perfectly