cancel
Showing results for
Did you mean:
Helper I

## Find difference between 2 rows

 DateTime SerialId GenHours Difference 9/22/2020 18:04 M3501-118 4789 blank 4/12/2021 18:19 M3501-118 5047 258 5/5/2021 14:28 M3501-118 5047 0 6/17/2021 15:10 M3501-118 5049 2 1/12/2021 17:01 M8501-112 928.4 blank 3/8/2021 20:31 M8501-112 1317 388.6 4/12/2021 18:19 M8501-112 1317 0 5/5/2021 14:29 M8501-112 1317 0 8/31/2020 23:44 M8501-116 1201.7 blank 1/6/2021 20:56 M8501-116 1301 99.3 3/8/2021 20:31 M8501-116 1315 14 4/12/2021 18:19 M8501-116 220 1095 4/24/2020 10:46 M8501-114 1859 blank 1/1/2021 17:00 M8501-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
Community Support

Hi, @axk180022

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.

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
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

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:

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.

Super User

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

Solution Sage

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

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 !

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}}),
#"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}}),
#"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"SerialId", "GenHours"}, {"SerialId.1", "GenHours.1"}),
#"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"``````
Helper I

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
)
)
)

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())

Super User

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 ) )``````
Helper I

I am not getting any values, its blank.

Super User

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

Check the attachment.

Announcements