Find difference between 2 rows

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"

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 !

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:

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.

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

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.

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.