Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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.
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,
@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, @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.
@v-angzheng-msft This is just a version of my solution as a calculated column rather than a measure...
Hi axk180022
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 !
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
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 ) )
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |