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 state change occurs, hence if a specific state runs across 2 shifts, it becomes a challenge to determine how long the equipment was in that state, for the current shift, as the entry for the state might have been captured during the previous shift, and not the current shift. Shifts are 12 hour durations, and run from 06:00-18:00 each day as Day shift and 18:00-06:00 the enxt day, as Night shift

Here is a snip of the data:

StateDateTime	EquipmentState	ShiftIndex
2020/08/22 05:50: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	Fail 1	2
2020/08/22 18:30:00	Running	3

We want to be able to report, that for ShiftIndex = 2 for example, we have the following EquipmentState Durations and as a percentage of the TotalShiftTime

EquipmentState	Duration in Minutes	Percentage of Shift
StartUp	10	13,8
Running	380	52,7
Maintenance	120	16,7
Fail 1	210	29,2

Any help would be greatly appreciated!

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

26 Replies

Anonymous

Not applicable

6 years ago

the following code is based only on the data you have provided, but to be of more general validity you should give more information on the different situations that can occur.

How do you select the group of lines of interest for each shift

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLSN7DQNzIwMlAwMLUyNVDSUQouSSwqKS0AsgyVYnVQlZhZGYKUBJXm5WXmpQNZRuhKDI2sjEFKfBMz80pS8xLzklOxKjOBKHNLzMxRMMSqwgKiAmGXsVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [StateDateTime = _t, EquipmentState = _t, ShiftIndex = _t]),
    ct = Table.TransformColumnTypes(Source,{{"StateDateTime", type datetime}, {"EquipmentState", type text}, {"ShiftIndex", Int64.Type}}),
    pos = let idx2=List.PositionOf(ct[ShiftIndex], 2,Occurrence.All) in {List.Min(idx2)-1}&idx2&{List.Max(idx2)+1},

    ai = Table.AddIndexColumn(ct, "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(ai, "duration", each try Duration.TotalMinutes(List.Min({ai[StateDateTime]{[Index]+1},#datetime(2020,8,22,18,0,0)})-List.Max({[StateDateTime],#datetime(2020,8,22,6,0,0)}))otherwise null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "pc", each [duration]/List.Sum(#"Added Custom"[duration])),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"pc", Percentage.Type}})
in
    #"Changed Type"

EricSteynMMD

Frequent Visitor

6 years ago

Thank you!

Yes, for the specific scenario I set out where ShiftIndex = 2, the solution does work. However as you rightfully mentioned, we need to paint out more scenarios. The current limitation is that I need to be able to select a varity of Date's to report on. So we should be able to not only select ShiftIndex=2, but also ShiftIndex=3, or select a different DatTime, like Week, Month, year etc. and we should be able to calculate the Duration of Each State, for the selected Duration.

I've included an expanded dataset, based on your solution below:

StateDateTime	EquipmentState	ShiftIndex	Index	Duration	PC
2020/08/22 05:50:00	Startup	1	0	10	-1.23%
2020/08/22 06:10:00	Running	2	1	380	-46.63%
2020/08/22 12:30:00	Maintenance	2	2	120	-14.72%
2020/08/22 14:30:00	Failure	2	3	210	-25.77%
2020/08/22 18:30:00	Running	3	4	-30	3.68%
2020/08/22 18:45:00	ShutDown	3	5	-45	5.52%
2020/08/23 06:00:00	Startup	4	6	-720	88.34%
2020/08/23 06:20:00	Running	4	7	-740	90.80%
2020/08/23 19:00:00	ShutDown	5	8	null	null

For ShiftIndex=2, the calculation is correct, but if we look at ShiftIndex=3, we should have an entry that Failure=30min, Running=15min, ShautDown=675min.

As for ShiftIndex=4, we would have Startup=20min, Running=700min. and for ShiftIndex=5 we would have Runing=60min and ShutDown = 700min (if we assume we have no more entries for ShiftIndex=5)

mahoneypat
Microsoft Employee
6 years ago
You can probably generate the table you'll need in M (precalcualted for each shift), but could also be solved with DAX. To do that, you'll also need a disconnected Shifts table that has the start and stop times for each shift. Also, you'll want to split your DateTime column into Date and Time (in the query) to enable the calculation you'll need (it will not be a simple DAX expression though, as you'll need to compare each statechange time to the start/stop time of the shift).

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

Regards,

Pat

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

Anonymous
Not applicable
6 years ago
hi EricSteynMMD

have you tried the code on the complete dataset?
how many rows the input table?
how many the output one?
what is the execution time?

Forum Discussion

Calculate Equipment State Duration per Shift

26 Replies

Related content

Calculate daily readings from multiple equipments

Durations - Dynamic Formats or Calculation Groups? Or both?

calculating duration

Calculating Stage Duration

calculate duration