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

Find difference between 2 rows

DateTimeSerialIdGenHoursDifference
9/22/2020 18:04M3501-118  4789  blank
4/12/2021 18:19M3501-118  5047  258
5/5/2021 14:28M3501-118  5047  0
6/17/2021 15:10M3501-118  5049  2
1/12/2021 17:01M8501-112  928.4  blank
3/8/2021 20:31M8501-112  1317  388.6
4/12/2021 18:19M8501-112  1317  0
5/5/2021 14:29M8501-112  1317  0
8/31/2020 23:44M8501-116  1201.7  blank
1/6/2021 20:56M8501-116  1301  99.3
3/8/2021 20:31M8501-116  1315  14
4/12/2021 18:19M8501-116  220  1095
4/24/2020 10:46M8501-114  1859  blank
1/1/2021 17:00M8501-114  1622  237

 

I have the above table. I would want to find the difference between each row based on the serialId, Would want the "difference" column result. Can anyone please provide the solution.

 

Thank you

11 REPLIES 11
v-angzheng-msft
Community Support
Community Support

Hi, @axk180022 

 

May I ask if your problem has been solved? Is the above post helpful to you?

If  it does, could you please mark the post which help as Answered? It will help the others in the community find the solution easily if they face the same problem with you. Thank you.

 

 

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Ashish_Mathur
Super User
Super User

Hi,

This calculated column formula works

Diff = if(isblank(CALCULATE(max(Data[DateTime]),FILTER(Data,Data[SerialId]=EARLIER(Data[SerialId])&&Data[DateTime]<EARLIER(Data[DateTime])))),blank(),abs(Data[GenHours]-lookupvalue(Data[GenHours],Data[DateTime],CALCULATE(max(Data[DateTime]),FILTER(Data,Data[SerialId]=EARLIER(Data[SerialId])&&Data[DateTime]<EARLIER(Data[DateTime]))),Data[SerialId],Data[SerialId])))

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-angzheng-msft
Community Support
Community Support

Hi, 

 

@AlexisOlson Yes, it is. I could not reproduce your results before opening your attachment, because I simply regarded [Gen Hours] in your formula as a column. I found it after I just opened the attachment.
I think what @axk180022  might need is a column. I took this as a challenge and got the above formula.

 

Hi, @axk180022 , I believe that the solutions provided by @AlexisOlson @speedramps  are all workable, and I hope these are useful to you.

 

 

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

v-angzheng-msft
Community Support
Community Support

Hi, @axk180022 

 

Try this:

Column = 
var _CurrDateTime=[DateTime]
var _PrevDateTime=MAXX(FILTER(ALL('Table'),[SerialId]=EARLIER([SerialId])&&[DateTime]<_CurrDateTime),[DateTime])
var _GenHours=CALCULATE(MAX('Table'[GenHours]),FILTER(ALL('Table'),[SerialId]=EARLIER([SerialId])&&[DateTime]=_PrevDateTime))

var _if=IF(ISBLANK(_GenHours),BLANK(),ABS([GenHours]-_GenHours))
return _if

Result:

vangzhengmsft_0-1635739680545.png

Please refer to the attachment below for details. Hope this helps.

 

 

Best Regards,
Community Support Team _ Zeon Zheng


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-angzheng-msft This is just a version of my solution as a calculated column rather than a measure...

speedramps
Solution Sage
Solution Sage

Hi axk180022

 

Click here to download a demo 

 

In Power Query:-

 

replace “blanks” with null
click on SerialId and sort
click on DateTime and sort

 

On the far left Queries menu:-
right click on the table and References. Call the new table Table1.
right click on the table and References. Call the new table Table2.


In Table1 add an index starting from 0
In Table2 add an index starting from 1

In Table1 merge Table2 on SerialId and Index
Expand the Table2.GehHours


Add a new column difference = GehHours - Table2.GehHours

 

Click here to download a demo 

 

Remember we are BI community voluntrees so please click the thumbs-up for me taking the trouble to help you and then accept the solution if it works.  Thank you !

 

 

AlexisOlson
Super User
Super User

There are a bunch of ways to approach this but one method I've used multiple times is to generate offset index columns and do a self-merge matching the two index columns to grab the prior row. It's a bit weird but more computationally efficient than any other methods I've come up with.

 

Here's a slightly different variation similar to the ones I linked:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdHREYMgEATQVhy+jdweBxz0kAoc+28jCkTNoOaPjzcsu8yzSZbZMjEN0ExiRvN2nvACdD0Pg0RNZhlnIxYFYoNIHfQksUBvfXOSWe9dsIgN+gy6gjUZR3LMhA1qhVxgYp2kSGe1QqbsegiHeNflGv50eXBqHeqK7LLICYYKmTDF1ibsb/Shl25t+Njl6+D/dKmQmZpjaR9NWc7BUi9Uv899rE29C8xmWT4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DateTime = _t, SerialId = _t, GenHours = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateTime", type datetime}, {"SerialId", type text}, {"GenHours", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index0", 0, 1, Int64.Type),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index1", 1, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index0"}, #"Added Index1", {"Index1"}, "Added Index1", JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"SerialId", "GenHours"}, {"SerialId.1", "GenHours.1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Added Index1", "Diff", each if [SerialId] = [SerialId.1] then [GenHours] - [GenHours.1] else null, type number),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index0", "Index1", "SerialId.1", "GenHours.1"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"SerialId", Order.Ascending}, {"DateTime", Order.Ascending}})
in
    #"Sorted Rows"

 

Try pasting this into your Advanced Editor window and walking through the steps one at a time.

 

Edit:

As @speedramps suggests, we can do the merge on multiple columns to eliminate the [SerialId] = [SerialId.1] check.

 

Here's the modified query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdHREYMgEATQVhy+jdweBxz0kAoc+28jCkTNoOaPjzcsu8yzSZbZMjEN0ExiRvN2nvACdD0Pg0RNZhlnIxYFYoNIHfQksUBvfXOSWe9dsIgN+gy6gjUZR3LMhA1qhVxgYp2kSGe1QqbsegiHeNflGv50eXBqHeqK7LLICYYKmTDF1ibsb/Shl25t+Njl6+D/dKmQmZpjaR9NWc7BUi9Uv899rE29C8xmWT4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DateTime = _t, SerialId = _t, GenHours = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateTime", type datetime}, {"SerialId", type text}, {"GenHours", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index0", 0, 1, Int64.Type),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index1", 1, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index0", "SerialId"}, #"Added Index1", {"Index1", "SerialId"}, "Added Index1", JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"SerialId", "GenHours"}, {"SerialId.1", "GenHours.1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Added Index1", "Diff", each [GenHours] - [GenHours.1], type number),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index0", "Index1", "SerialId.1", "GenHours.1"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"SerialId", Order.Ascending}, {"DateTime", Order.Ascending}})
in
    #"Sorted Rows"

Gen Hours is a measure thats created to take only the 4 latest values from table based on the datetime which is below

 

Gen Hours =
CALCULATE (
SUM ( Merge1[All rows.GENERATORHOURS] ),
KEEPFILTERS (
TOPN (
4,
FILTER (
ALLSELECTED ( Merge1 ),
Merge1[Serial ID] = MAX ( Merge1[Serial ID] ) &&
Merge1[All rows.GENERATORHOURS] > 0
),
Merge1[CALLACTIONDATETIME], DESC
)
)
)
 
could you please help me create a calculated column something like this.
 
 Difference = if(CALCULATE(MAX(Merge1[DateTime].[Date]),FILTER(Merge1,Merge1[Serial ID]=EARLIER(Merge1[Serial ID])))= Merge1[DateTime].[Date],ABS(Merge1[All rows.GENERATORHOURS]-LOOKUPVALUE(Merge1[All rows.GENERATORHOURS],Merge1[DateTime].[Date],CALCULATE(max(Merge1[DateTime].[Date]),FILTER(Merge1,Merge1[Serial ID]=EARLIER(Merge1[Serial ID])&&Merge1[DateTime].[Date]<EARLIER(Merge1[DateTime].[Date]))),Merge1[Serial ID],Merge1[Serial ID])),BLANK())
 

Ah, I didn't realize you were looking for a DAX solution. That's actually a bit easier.

 

Try this:

Difference = 
VAR CurrDateTime = SELECTEDVALUE ( Merge1[DateTime] )
VAR PrevDateTime = CALCULATE ( MAX ( Merge1[DateTime] ), Merge1[DateTime] < CurrDateTime )
VAR PriorGenHours = CALCULATE ( [Gen Hours], Merge1[DateTime] = PrevDateTime )
RETURN
    IF ( NOT ISBLANK ( PriorGenHours ), ABS ( [Gen Hours] - PriorGenHours ) )

I am not getting any values, its blank.

 

@AlexisOlson 

I can't tell what's going wrong since it works fine for me.

 

AlexisOlson_0-1635456521276.png

 

Check the attachment.

 

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

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!

Top Solution Authors
Top Kudoed Authors