cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper II

## How to Calculate Time difference between 02 row and summarise based on filter

Hi,

I have an alarms table where i want to calculate the time difference between 02 rows recorded against the respective alarm of the machines.

My table contains multiple columns including DateTime, Machine id, Alarm Message, Alarm / Event (1 for alarm 2 for Event), Alarm high/low status (1-High, 0-Low), Batch no., Product Name etc.

I want to filter the specific alarms based in Machine ID, Product Name, Batch No., Alarm and to calculate the difference of Date & Time from last row to the next row, based on the condition if alarm message for a specific machine id is high at a timestamp and low at another time stamp.

I need Total of time difference or you can say stoppage time for a particluar machine id to be displayed in a Card and in a new

column in the table (As shown in table).

So far i have tried to add a Calculated column and a Mesure as well but both are not working:

Calculated Column:
Diff =
VAR nextIndex = Sheet1[Index] - 1
RETURN
DATEDIFF(
CALCULATE(
VALUES(Sheet1[DateTime]),
FILTER(ALL(Sheet1), Sheet1[Index] = nextIndex)
),
Sheet1[DateTime],
HOUR)

Mesaure:
TimeDiff =

var time = SELECTEDVALUE(Sheet1[DateTime])

return DATEDIFF(time,MAXX(CALCULATETABLE(Sheet1,REMOVEFILTERS(Sheet1[DateTime]),Sheet1[DateTime] < Time),Sheet1[DateTime]))

Any help or suggestion would be highly appreciable.

My data looks like this:

 DateTime Machine ID Product Batch no. Alarm Message Alarm / Event Alarm Status Time Difference 01.01.2023 04:10:20 001 XXXX NM\$\$\$\$ Emergency Pressed 1 1 01.01.2023 04:10:30 002 ZZZZ NM%%%% Machine Start 2 1 01.01.2023 04:10:45 005 AAAA NM##### Low Temp 1 1 01.01.2023 04:11:15 001 XXXX NM\$\$\$\$ Emergency Pressed 1 0 00:00:55 01.01.2023 04:11:45 002 ZZZZ NM%%%% Machine Stop 2 0 01.01.2023 04:12:15 005 AAAA NM##### Low Temp 1 0 00:01:30 01.01.2023 04:15:10 008 BBBB NM@@@@ High Temp 1 1 01.01.2023 04:16:30 008 BBBB NM@@@@ High Temp 1 0 00:01:20
Super User
``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc5RCsAwCAPQqwy/y4i2juFVSu9/jUqHfxn490jinAK98wzWL4xQhEGaACqrEe2fGtfhR52phvp/s1aWNltlabPn9NGX6VM/p64N", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DateTime = _t, #"Machine ID" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DateTime", type datetime}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Time Difference", each try [DateTime]-Table.Sort(Table.SelectRows(#"Changed Type",(k)=>k[Machine ID]=[Machine ID] and k[DateTime]<[DateTime]),{{"DateTime", Order.Descending}}){0}[DateTime] otherwise null,type duration)
in

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".