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
Rathan
Frequent Visitor

Hello Guys,

 

I have the same question on how to find the difference between two rows from the dated column. Below is how my table looks.

 

 
Date QuantityDiff
06/01/20201 
06/02/202032
06/03/202063

 

I just want the difference between the quantity column. I have tried multiple queries but it works fine in Import Mode but mine is Direct Query mode. Looks like there are many limitations in Direct Query. Could someone help me in giving some guidance regarding this for DIrect Query?

 

Thanks in advance.

 

Hi,

You should have a Calendar Table with a relationship from the Date column of your Data Table to the Date column of your Calendar Table.  To your visual, drag the Date column from the Calendar Table.  Write these measures:

Quantity = sum(Data[Number])

Quantity on previous day = calculate([quantity],previousday(calendar[date]))

Diff in quantity = [quantity]-[Quantity on previous day]

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you. It worked

You are welcome.  If my reply helped, please mark it as Answer.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
tomislav_mi
Helper II
Helper II

Hey guys,

I encountered one problem I simply can't solve. If anyone can help me out I would be extra grateful!

I want to calculate the difference between two rows, but after I import data to the data model, the sort order is mixed up (see below: Report Date and Account Name) so one of my formulas that could work if the sort order is correct doesn't work.

The table looks like this and the Change column like this is my goal.

Account NameMRRReport DateChange
a1001/31/2017 0:00100
a1002/28/2017 0:000
a1504/30/2017 0:00-30
a1005/31/2017 0:00-50
b137/31/2019 0:0013
b138/31/2019 0:000
c57/31/2019 0:005
a1006/30/2017 0:000
a1007/31/2017 0:000
a1008/31/2017 0:000
a1009/30/2017 0:000
a10010/31/2017 0:000
a1803/31/2017 0:0080


You can notice that the account doesn't have the correct order either by the report date. 

If there is any way how to solve it (maybe as a measure? or maybe is there a way how to correctly import data?)
I would be so so grateful.

All the best,

Tomislav

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi, I have almost the same data but the column MRR includes blanks, I appreciate your advice to let me know what the formula will look like? i need if the column value is blank to take the earliest value found instead of calculating based on zeros instead of balnks. Thanks.

Hi,

Refer to column1 of the image.  You will see that the result in the 5th row is not 100-0=100 but 100-180=-80.  You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish, thanks for your support. appreciated.

 

your solution works fine for the import data but i have direct query data so i cannot add a custom column to do what you adviced, i believe i need the same formula on the power query. i appreciate your support. thanks.

You are welcome.  Someone else will help you with that.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you @Ashish_Mathur !

Works amazing!

Thank you so much.

All the best from Croatia!

Tomislav

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
SSS
Helper I
Helper I

Thanks Guys!! It really helped!

Ashish_Mathur
Super User
Super User

Hi @SSS,

 

If you have a calendar table and there is a relatioship from the date column of your base data table to the date column of the calendar table, then you can use this calculated field formula

 

=CALCULATE(SUM(Data[Orders]),PREVIOUSDAY(Calendar{Date]))-SUM(Data[Orders])


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Anonymous

 

You can use one of these...I believe

 

Days from Next Date =
VAR Next_Date =
    MINX (
        TOPN (
            1,
            FILTER ( Table1, [Key] = EARLIER ( [Key] ) && [Date] > EARLIER ( [Date] ) ),
            [Date], ASC
        ),
        [Date]
    )
RETURN
    DATEDIFF ( [Date], Next_Date, DAY )

OR

 

Days from Previous Date =
VAR Previous_Date =
    MINX (
        TOPN (
            1,
            FILTER ( Table1, [Key] = EARLIER ( [Key] ) && [Date] < EARLIER ( [Date] ) ),
            [Date], DESC
        ),
        [Date]
    )
RETURN
    DATEDIFF ( Previous_Date, [Date], DAY )
Regards
Zubair

Please try my custom visuals


Anonymous
Not applicable

Hi @Zubair_Muhammad,

 

Thanks a lot!

 

The first one result a strange column, but the second works when I compared with Excel result.

 

Now, I'll proceed to tests accessing real data from the database.

 

Dax has some mysteries to me, yet. I didn't know the VAR concept or the possibility to call previous or next records in an instruction.

 

Thanks a lot again.

pxg08680
Resolver III
Resolver III

Anonymous
Not applicable

@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
Community Champion
Community Champion

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"

 

Specializing in Power Query Formula Language (M)

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!

Helpful resources

Announcements
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

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