Forum Discussion
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 M...
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 can't tell what's going wrong since it works fine for me.
Check the attachment.