Solved: Calculate Equipment State Duration per Shift - Page 3

EricSteynMMD · ‎08-22-2020

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!

Anonymous · ‎08-25-2020

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

View solution in original post

Calculate Equipment State Duration per Shift

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024