Define Dynamic Time Values Into Shifts

[markefrody]

Hi all,

I'm not sure how I can do this in Power BI. Appreciate your kind advice.

I have two tables containing the following:
Table 1: Manager Schedule
Contains the shift schedule and meal (break) time of 2 shift managers per day.

FirstName	LastName	Start Time	Break Start	Break End
Lebron	James	09/28/2021 04:00:24 PM	09/28/2021 08:31:42 PM	09/28/2021 09:01:45 PM
Michael	Jordan	09/28/2021 04:12:07 AM	09/28/2021 08:37:29 AM	09/28/2021 09:10:51 AM
Lebron	James	09/27/2021 05:40:24 PM	09/27/2021 09:12:21 PM	09/27/2021 10:00:55 PM
Michael	Jordan	09/27/2021 04:04:29 AM	09/27/2021 08:36:38 AM	09/27/2021 09:10:23 AM

NOTE: There is no "End Time" in the table.
To get the "End Time", before the “Start Time” of the next shift manager is the “End Time” of the current shift manager.
Just deduct  a second on the start time of the next shift manager to get the current shift manager's "End Time" .

Example:
In September 28, 2021, 
If Michael Jordan’s Start Time is 09/28/2021 04:12:07 AM (current shift manager),
Lebron James’ Start Time is 09/28/2021 04:00:24 PM (next shift manager),
Therefore, Michael Jordan’s End Time would be 09/28/2021 04:00:23 PM

Table 2: Production Schedule
List of products and time when the product was made. Data can be found here:
https://www.dropbox.com/s/7jh2hcycutk0zra/Schedule%20Sample%20Data.xlsx?dl=0 

What I need:
1. Identify which manager does a product fall into:

Product ID	Date and Time Finished	Shift Manager

2. Output wherein it will show:

Date Finished	First Name	Last Name	Total hours	Total minutes	Total seconds
9/28/2021	Lebron	James
9/27/2021	Michael	Jordan

a. Date Finished - is the date the when the product was finished. Production for that day.
b. First Name - Manager who is responsible for that product first name.
c. Last Name – Manager who is responsible for that product last name
d. Total hours finished - Computed by getting the (earliest finished time - latest finished time for that day), and removing the time spent during break time. Value should be in hours.
e. Total minutes finished - Computed by getting the (earliest finished time - latest finished time for that day), and removing the time spent during break time. Value should be in minutes.
f.  Total seconds finished - Computed by getting the (earliest finished time - latest finished time for that day), and removing the time spent during break time. Value should be in seconds.

Thank you in advance for any assistance you can provide me. 

Best regards,
Mark V.

[v-janeyg-msft]

Hi, @markefrody 

Do you still need help?

In your desired result, Why 2021/9/28 is Lebron not Michael? Is there any logic between this?

Best Regards,

Community Support Team _ Janey

[Greg_Deckler]

@markefrody While for a different purpose, you essentially need MTBF: See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous

[KNP]

@Greg_Deckler - that's a nice pattern. They should turn that into a quick measure. 😉