Sum of minutes in a datetable, when separate table has a datetime within the datetable's datetimes

[TimothyJames]

Hi,

So I work in a factory and I have some data that I cannot figure out how to sum up.

Basically, the intent of this is to get "How many minutes did this person work on this machine out of the full 8 hour shift?"
This will allow me to get a ratio of utilization of our machines compared to what we expect.

We expected our employees to get 420 minutes of utilization per shift. (8 hours minus some breaks/lunch/etc)

So I have the dataset of inspections.

Inspections 

Inspection Ticket	Start Time	End Time	Shift
1	8/3/2021 6:45 AM	8/3/2021 6:55 AM	1st
2	8/3/2021 7:30 AM	8/3/2021 7:35 AM	1st

So on and So Forth.

I also have a datetime column that has this data

Date	Shift	Shift Start Time	Shift End Time	Shift Start Datetime	Shift End Datetime	Max Minutes Available
8/3/2021	1st	12/30/1899 6:30 AM	12/30/1899 2:30 PM	8/3/21 6:30 AM	8/3/21 2:30 PM	420
8/3/2021	2nd	12/30/1899 2:30 PM	12/30/1899 10:30 PM	8/3/21 2:30 PM	8/3/21 10:30 PM	420
8/3/2021	3rd	12/30/1899 10:30 PM	12/30/1899 6:30 AM	8/3/21 10:30 PM	8/4/21 6:30 AM	420

Here's what I would expect to see.

Minutes of Inspection Time =

iferror(
    SUMX ( inspections,datediff(Inspections[inspection_time_in],Inspections[inspection_time_out],MINUTE )
           ),blank()) 

Therefore 15 minutes of inspection time

I would also like to see 420 minutes of inspection time available.
15/420 = .035714 or 3.57% of 420

I have the minutes of inspection time done comfortable, but I cannot identify how I sum up available time if the inspection occured in that window of time.

[v-jayw-msft · Accepted solution]

Hi @TimothyJames ,

Step 1: Create a relationship between Inspections and datetime with shift column.

Step 2: Create a calculate column to Inspections.

_diff = DATEDIFF(Inspections[Start Time],Inspections[End Time],MINUTE)

Step 3: Create a measure.

Measure = SUM(Inspections[_diff])/SUM(datetime[Max Minutes Available])

Best Regrads,

Jay