Forum Discussion
Define Dynamic Time Values Into Shifts
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.
3 Replies
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/339586.
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 - __PreviousGreg_Deckler - that's a nice pattern. They should turn that into a quick measure. 😉
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
