Forum Discussion

Frequent Visitor

6 years ago

Solved

Calculate Equipment State Duration per Shift

Hi All, I need help please. I need to report on the duration that a piece of equipment was running in a specific state, during each shift. The issue is that the dataset only has entries when a st...

Need Help

Anonymous

6 years ago

here a solution "complete" (corresponding to the status of the information received 😁) avoiding the use of the function List.Accumulate.

starting table:

output Table:

the code for the output table:

let

    ct = Table.TransformColumnTypes(Table,{{"StateDateTime", type datetime}, {"EquipmentState", type text}, {"ShiftIndex", Int64.Type}}),
    idx=List.RemoveLastN(List.Distinct(ct[ShiftIndex]),1),
    lstR=List.Generate(
        ()=>[r=1,pos=List.PositionOf(ct[ShiftIndex],idx{0},Occurrence.Last), lr=sfht(ct{pos},ct{pos+1})], 
        each [r]<=List.Count(idx),
        each [r=[r]+1, pos=List.PositionOf(ct[ShiftIndex],idx{[r]},Occurrence.Last),lr=sfht(ct{pos},ct{pos+1}) ], 
        each [lr]
    ),
   tc= Table.Combine({Table.FromRecords(List.Combine(lstR)),Table}),
   grp = Table.Group(tc, {"ShiftIndex"}, {{"shIdx", each duration(_)}}),
    #"Expanded shIdx" = Table.ExpandTableColumn(grp, "shIdx", {"StateDateTime", "EquipmentState", "Duration"}, {"StateDateTime", "EquipmentState", "Duration"})
in
    #"Expanded shIdx"

the two functions used:

sfht

let

    listRows=(rL,rF)=>
    let
    shift=#duration(0,0,720,0),
    dL=rL[StateDateTime],
    dF=rF[StateDateTime],
    lr=List.Generate(
        ()=>[r=rL&[StateDateTime=#datetime(Date.Year(dL),Date.Month(dL),Date.Day(dL)+sh{1},sh{0},0,0)],idx=1],
        each [r][StateDateTime]<=dF,
        each [r=if Number.Mod(idx,2)=1 then [r]& [StateDateTime=[r][StateDateTime]+shift] else [r]&[ShiftIndex=[r][ShiftIndex]+1],idx=[idx]+1],
     each [r]   
    ), 
    sh=if Time.Hour(dL) < 6 then {6,0} else if Time.Hour(dL)<18 then {18,0} else {6,1}

    in lr

in listRows

and finally duration:

let
count=(tab)=>
let
    ts=Table.Sort(tab,{"StateDateTime"}),
    ai = Table.AddIndexColumn(ts, "i", 0, 1)
   in
   Table.RemoveColumns(Table.AddColumn(ai, "Duration", each try if [ShiftIndex]=ai[ShiftIndex]{[i]+1} then Duration.TotalMinutes(ai[StateDateTime]{[i]+1}-[StateDateTime]) else "" otherwise""),{"i"})
in count

Icey

Community Support

6 years ago

Hi EricSteynMMD ,

How about using DAX?

1. Create EquipmentState table by entering data and sort "EquipmentState" column by "Order" column.

2. Create measures.

Duration in Minutes =
VAR StartTime =
    YEAR ( MAX ( 'Table'[StateDateTime] ) ) & "/"
        & MONTH ( MAX ( 'Table'[StateDateTime] ) ) & "/"
        & DAY ( MAX ( 'Table'[StateDateTime] ) ) & " "
        & TIME ( 6, 0, 0 )
VAR EndTime =
    YEAR ( MAX ( 'Table'[StateDateTime] ) ) & "/"
        & MONTH ( MAX ( 'Table'[StateDateTime] ) ) & "/"
        & DAY ( MAX ( 'Table'[StateDateTime] ) ) & " "
        & TIME ( 18, 0, 0 )
VAR StartUpTime =
    CALCULATE (
        MAX ( 'Table'[StateDateTime] ),
        'Table'[EquipmentState] = "StartUp"
    )
VAR RunningTime_ =
    CALCULATE (
        MAX ( 'Table'[StateDateTime] ),
        'Table'[EquipmentState] = "Running"
    )
VAR StartUpTime_ =
    IF ( StartUpTime = BLANK (), StartTime, StartUpTime )
VAR MaintenanceTime =
    CALCULATE (
        MAX ( 'Table'[StateDateTime] ),
        'Table'[EquipmentState] = "Maintenance"
    )
VAR FailTime =
    CALCULATE ( MAX ( 'Table'[StateDateTime] ), 'Table'[EquipmentState] = "Fail 1" )
VAR FailTime_ =
    IF ( FailTime = BLANK (), EndTime, FailTime )
RETURN
    SWITCH (
        MAX ( EquipmentState[EquipmentState] ),
        "StartUp", DATEDIFF ( StartUpTime_, RunningTime_, MINUTE ),
        "Running", DATEDIFF ( RunningTime_, MaintenanceTime, MINUTE ),
        "Maintenance", DATEDIFF ( MaintenanceTime, FailTime_, MINUTE ),
        "Fail 1", DATEDIFF ( FailTime_, EndTime, MINUTE )
    )

Percentage of Shift = 
[Duration in Minutes]/SUMX(ALL(EquipmentState),[Duration in Minutes])

BTW, .pbix file attached.

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it more quickly.

Calculate Equipment State Duration per Shift.pbix73 KB

EricSteynMMD

Frequent Visitor

6 years ago

Thanks Icey for the detailed solution below.

The only issue we'd have with this approach is that the EquipmentState's are not only limited to the ones I gave in the Example and can in fact be up to 10 different Events/States. They also do not specifically occur in any order and can also sometimes span the duraiton of a number of shifts as well.

Not entirely sure how I send an attachment, but happy to share a copy of the Dataset if it would help?

mahoneypat

Microsoft Employee

6 years ago

Here is another way to add shift start and end to your example data that should be performant. Please take a look. I'm not sure what ShiftIndex is, so I just Filled Down that to. Also, I am in a different locale, so changed the input dates. You may have to change locale at the #"Promoted Headers" step (or just replace the input datetime values).

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc9ND4IwDAbgv2J2JmErzCBnNPHgRbwtHBadugQqki768x1gRD4OS5r1Sd9WKZaTJpP5d7KVYQHbPp2tK4PUNfxHfrdX2uPFvFkRKJaEACFw4CsuU8lbQLohV/tKTMU6Fa04OkSLN1/BRAhIo1YctEUyqPFsllTcq522pWsWRdKLISmai1h25zjKHi/8J9FvXT4+KJ4LGMdMhdh8ZwwxkhXFBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StateDateTime = _t, EquipmentState = _t, ShiftIndex = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"StateDateTime", type datetime}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"StateDateTime"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Other Columns",{{"StateDateTime", type date}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type2"),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "ShiftList", each {#time(5,59,59), #time(6,0,0), #time(17,59,59), #time(18,0,0)}),
    #"Expanded ShiftList" = Table.ExpandListColumn(#"Added Custom", "ShiftList"),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded ShiftList", {{"StateDateTime", type text}, {"ShiftList", type text}}, "en-US"),{"StateDateTime", "ShiftList"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"StateDateTime.1"),
    #"Changed Type" = Table.TransformColumnTypes(#"Merged Columns",{{"StateDateTime.1", type datetime}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"StateDateTime.1", "StateDateTime"}}),
    #"Appended Query" = Table.Combine({#"Renamed Columns", #"Changed Type1"}),
    #"Sorted Rows" = Table.Sort(#"Appended Query",{{"StateDateTime", Order.Ascending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"EquipmentState", "ShiftIndex"})
in
    #"Filled Down"

If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.

Regards,

Pat

EricSteynMMD

Frequent Visitor

6 years ago

Thank you mahoneypat - this does help a lot! Thanks for the inputs

The one last thing we need to look at in this scenario is the ShiftIndex that we just FilledDown.

The ShiftIndex is a number that indicates what shift we are referencing. Hence in your code the Fill down gives us this output:

StateDateTimeEquipmentStateShiftIndex

2020/08/22 05:50:00	Startup	1
2020/08/22 05:59:00	Startup	1
2020/08/22 06:00:00	Startup	1
2020/08/22 06:10:00	Running	2
2020/08/22 12:30:00	Maintenance	2
2020/08/22 14:30:00	Failure	2
2020/08/22 17:59:00	Failure	2
2020/08/22 18:00:00	Failure	2
2020/08/22 18:30:00	Running	3
2020/08/22 18:45:00	ShutDown	3
2020/08/23 05:59:00	ShutDown	3
2020/08/23 06:00:00	Startup	4
2020/08/23 06:00:00	Startup	4
2020/08/23 06:20:00	Running	4
2020/08/23 17:59:00	Running	4
2020/08/23 18:00:00	Running	4
2020/08/23 19:00:00	ShutDown	5

Which is correct for all fields and exactly what we need, however the ShiftIndex now becomes a challenge.

Where we would need to increment the ShiftIndex, when we have an entry in the next Shift, like this:

(I've added a comment filed to help explain)

StateDateTimeEquipmentStateShiftIndex Comment

2020/08/22 05:50:00	Startup	1
2020/08/22 05:59:00	Startup	1
2020/08/22 06:00:00	Startup	2	from 06:00 is the next Shift, so ShiftIndex needs to reflect the next ShiftIndex
2020/08/22 06:10:00	Running	2
2020/08/22 12:30:00	Maintenance	2
2020/08/22 14:30:00	Failure	2
2020/08/22 17:59:00	Failure	2
2020/08/22 18:00:00	Failure	3	from 18:00 is the next Shift, so ShiftIndex needs to reflect the next ShiftIndex
2020/08/22 18:30:00	Running	3
2020/08/22 18:45:00	ShutDown	3
2020/08/23 05:59:00	ShutDown	3
2020/08/23 06:00:00	Startup	4	Correct ShiftIndex, as the State change was logged at 06:00
2020/08/23 06:00:00	Startup	4
2020/08/23 06:20:00	Running	4
2020/08/23 17:59:00	Running	4
2020/08/23 18:00:00	Running	4	from 18:00 is the next Shift, so ShiftIndex needs to reflect the next ShiftIndex
2020/08/23 19:00:00	ShutDown	5

We will also run into an issue with the ShiftIndex, if we have an EquipmentState that has a duration across multiple shifts. So if we use this input dataset for example:

StateDateTimeEquipmentStateShiftIndex

2020/08/24 05:50:00	Running	5
2020/08/25 06:15:00	ShutDown	8

Your Solution gives us this output because of the FillDown on ShiftIndex:

StateDateTimeEquipmentStateShiftIndex

2020/08/24 05:50:00	Running	5
2020/08/24 05:59:00	Running	5
2020/08/24 06:00:00	Running	5
2020/08/24 17:59:00	Running	5
2020/08/24 18:00:00	Running	5
2020/08/25 05:59:00	Running	5
2020/08/25 06:00:00	Running	5
2020/08/25 06:15:00	ShutDown	8
2020/08/25 17:59:00	ShutDown	8
2020/08/25 18:00:00	ShutDown	8

Where we need the following in terms of ShiftIndex:

StateDateTimeEquipmentStateShiftIndex

2020/08/24 05:50:00	Running	5
2020/08/24 05:59:00	Running	5
2020/08/24 06:00:00	Running	6
2020/08/24 17:59:00	Running	6
2020/08/24 18:00:00	Running	7
2020/08/25 05:59:00	Running	7
2020/08/25 06:00:00	Running	8
2020/08/25 06:15:00	ShutDown	8
2020/08/25 17:59:00	ShutDown	8
2020/08/25 18:00:00	ShutDown	8

Thanks again for the time and help on this!

mahoneypat

Microsoft Employee

6 years ago

The M code has been updated to add a ShiftIndexIncrement to address your first point. I will re-read and try to address the second point later (time to "go" to work).

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc9ND4IwDAbgv2J2JmErzCBnNPHgRbwtHBadugQqki768x1gRD4OS5r1Sd9WKZaTJpP5d7KVYQHbPp2tK4PUNfxHfrdX2uPFvFkRKJaEACFw4CsuU8lbQLohV/tKTMU6Fa04OkSLN1/BRAhIo1YctEUyqPFsllTcq522pWsWRdKLISmai1h25zjKHi/8J9FvXT4+KJ4LGMdMhdh8ZwwxkhXFBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StateDateTime = _t, EquipmentState = _t, ShiftIndex = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"StateDateTime", type datetime}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"StateDateTime"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Other Columns",{{"StateDateTime", type date}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type2"),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "ShiftList", each {#time(5,59,59), #time(6,0,0), #time(17,59,59), #time(18,0,0)}),
    #"Expanded ShiftList" = Table.ExpandListColumn(#"Added Custom", "ShiftList"),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded ShiftList", {{"StateDateTime", type text}, {"ShiftList", type text}}, "en-US"),{"StateDateTime", "ShiftList"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"StateDateTime.1"),
    #"Changed Type" = Table.TransformColumnTypes(#"Merged Columns",{{"StateDateTime.1", type datetime}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"StateDateTime.1", "StateDateTime"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "ShiftIndexIncrement", each if Time.Hour([StateDateTime]) = 6 or Time.Hour([StateDateTime]) =18 then 1 else 0, Int64.Type),
    #"Appended Query" = Table.Combine({#"Added Custom1", #"Changed Type1"}),
    #"Sorted Rows" = Table.Sort(#"Appended Query",{{"StateDateTime", Order.Ascending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"EquipmentState", "ShiftIndex"}),
    #"Replaced Value" = Table.ReplaceValue(#"Filled Down",null,0,Replacer.ReplaceValue,{"ShiftIndexIncrement"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Replaced Value",{{"ShiftIndex", Int64.Type}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type3", "NewShiftIndex", each [ShiftIndex]+[ShiftIndexIncrement]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"ShiftIndexIncrement", "ShiftIndex"}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Removed Columns",{{"NewShiftIndex", type text}})
in
    #"Changed Type4"

If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.

Regards,

Pat

Forum Discussion

Calculate Equipment State Duration per Shift

Related content

Calculate daily readings from multiple equipments

Durations - Dynamic Formats or Calculation Groups? Or both?

calculating duration

Calculating Stage Duration

calculate duration