Understanding Machine Utilization based on On and Off Times

[Cells]

Hello,

First post! 🙂 I have a bit of a challenge that is proving quite burdensome. I can take the very long way for each in the traditional DATA section but am sure there is a smoother way to achieve my goal.

Part 1:

I am looking to measure machine use based on when it is turned on and off by calculating the number of minutes that fall in to each 15 minute category across the day (all 96 windows). 

Machine On	Machine Off	Duration (minutes)	0930-0945	0945-1000	1000-1015
09:33 AM	10:09 AM	36	12	15	9
09:51 AM	09:59 AM	8	0	8	0

Part 2:

After placing the minutes, I would then look to understand the activity within specified times of day. In the example above, 0930-1015, and would want to know how many minutes were used of the 45 available:

36/45=80%

8/45=18%

Initially I thought this would be something I could achieve but as I mentioned previously it has given me quite a few fits.

With Gratitude,

C

[mahoneypat]

Please see these articles and video that cover several topics that should be helpful to you.

Calculate and Format Durations in DAX – Hoosier BI

Simple Model, Simple DAX - Line Time Example - YouTube

Time Tables in Power BI – Hoosier BI

Pat

[ronrsnfld · Accepted solution]

I worked with the data in Excel, but you can have your data in any source.  Just change the Source line to whatever your actual source is.

For Part 1, I assumed each row represents a different machine, and that the times off and on do not extend past midnight.

You can then use a combination of List.Generate and List.Accumulate to break down the number of minutes in each segment:

let

//change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table11"]}[Content],

//create list of all time segments
    #"Time Segments" = List.Buffer(List.Times(#time(0,0,0), 96, #duration(0,0,15,0))),
    colHeaders = List.Transform(#"Time Segments", each Time.ToText(_,"HHmm-") & Time.ToText(_+#duration(0,0,15,0),"HHmm")),

//set data types
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Machine", type text}, {"On", type time}, {"Off", type time}}),

//create Lists of all the durations for each row
    Durations = List.Generate(
        ()=>[m=List.Accumulate(
                    #"Time Segments",
                    {}, (state, current)=>
                    state & {List.Max({0,Duration.Minutes(List.Min({#"Changed Type"[Off]{0}, current + #duration(0,0,15,0)}) - 
                    List.Max({#"Changed Type"[On]{0},current}))})}),
                    idx=0],
            each [idx] < Table.RowCount(#"Changed Type"),
            each [m=List.Accumulate(
                    #"Time Segments",
                    {}, (state, current)=>
                    state & {List.Max({0,Duration.Minutes(List.Min({#"Changed Type"[Off]{[idx]+1}, current}) - 
                    List.Max({#"Changed Type"[On]{[idx]+1},current - #duration(0,0,15,0)}))})}),
                    idx=[idx]+1],
            each [m]),

//create new table,
    #"Duration Records" = List.Transform(Durations, each Record.FromList(_, colHeaders)),
    newTable = Table.FromColumns(
        Table.ToColumns(#"Changed Type") & {#"Duration Records"},
        type table[Machine=text, On=time, Off=time, Results=record]
    ),
    #"Expanded Results" = Table.ExpandRecordColumn(newTable, "Results", colHeaders),
    #"Type new Columns" = Table.TransformColumnTypes(#"Expanded Results", List.Transform(colHeaders, each {_, Int64.Type}))
in
    #"Type new Columns"

Results