Forum Discussion

skumar0105's avatar
skumar0105
Helper II
3 years ago

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:

 

 

DateTimeMachine IDProductBatch no.Alarm MessageAlarm / EventAlarm StatusTime Difference
01.01.2023 04:10:20001XXXXNM$$$$Emergency Pressed11 
01.01.2023 04:10:30002ZZZZNM%%%%Machine Start21 
01.01.2023 04:10:45005AAAANM#####Low Temp11 
01.01.2023 04:11:15001XXXXNM$$$$Emergency Pressed1000:00:55
01.01.2023 04:11:45002ZZZZNM%%%%Machine Stop20 
01.01.2023 04:12:15005AAAANM#####Low Temp1000:01:30
01.01.2023 04:15:10008BBBBNM@@@@High Temp11 
01.01.2023 04:16:30008BBBBNM@@@@High Temp1000:01:20
Need Help

1 Reply

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago
    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".