Hi everybody, I have a production machine that produces goods, and for each item produced, a row is sent to CosmosDB, which I'm using as a data source in PowerBI. Some samples of these rows are as follows (simplified)
| MachineID | Date | Time | Duration |
| S1 | 01.03.2020 | 09:00 | 00:15 |
| S1 | 01.03.2020 | 12:10 | 01:20 |
| S2 | 02.03.2020 | 20:00 | 00:45 |
| S2 | 04.03.2020 | 02:10 | 02:10 |
The machines are operated by staff in shifts. Some days the shifts are the same, but shorter shifts on fridays for example. (But at a regular defined interval)
| Shiftname | Start | Stop | Shift duration (minutes) | Weekday (numeric) |
| Shift1 | 06:00 | 18:00 | 720 | 1,2,3,4 |
| Shift2 | 18:00 | 02:00 | 480 | 1,2,3,4 |
| Shift3 | 06:00 | 15:00 | 540 | 5 |
| Shift4 | 15:00 | 21:00 | 360 | 5 |
Now I want to calculate how efficient one shift on a specific day is. If the sum of the duration for a specific shift on a particular day is half of the duration of the shift, the calculated value should be 50%.
I assuming I have to make a new table where I enters the shift periods, and make a new column in the "machine data" that picks up which shift each row belongs to.
I hope someone can point me in the right direction for:
- How to lookup shift from another table based on the timestamp.
- How and in what part of PowerBI I can sum the duration of all the rows for a particular shift.
