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

[skumar0105]

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

[lbendlin]

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
    #"Added Custom"

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".