Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
skumar0105
Helper II
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:

 

 

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
1 REPLY 1
lbendlin
Super User
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
    #"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".

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors