Forum Discussion
Post PatronTime Values Distributed Into Shifts
Hi,
I'm not sure how to do this in Power BI Desktop. Any help you can provide me is greatly appreciated.
I have a table that contains two managers (Manager 1 and Manager 2) with different work shift and break time for each day.
Then another table which contains the finished date and time of a product.
What I need is a similar table like below wherein:
1. Date - is the date the when the product was finished.
2. Manager - Manager who is assigned to that shift when the product was finished.
3. # of Hour Finished - Computed by getting the earliest finished time - latest finished time, and removing the time spent during break time. Value should be in hours.
4. # of Minutes Finished - Computed by getting the earliest finished time - latest finished time, and removing the time spent during break time. Value should be in minutes.
5. # of Seconds Finished - Computed by getting the earliest finished time - latest finished time, and removing the time spent during break time. Value should be in seconds.
If anything is unclear please let me know.
Best regards,
Mark V.
markefrody , sorry, I've missed some details.
hh = VAR timeIn = MIN ( 'Table 1'[Start Time] ) VAR timeOut = MAX ( 'Table 1'[Clock Out] ) VAR timeRange = FILTER ( VALUES ( 'Table 2'[Date and Time Finished] ), 'Table 2'[Date and Time Finished] >= timeIn && 'Table 2'[Date and Time Finished] <= timeOut ) VAR break = DATEDIFF( MIN ( 'Table 1'[Break1 Start] ), MAX ( 'Table 1'[Break1 End] ), SECOND) VAR ss = DATEDIFF ( MINX ( timeRange, 'Table 2'[Date and Time Finished] ), MAXX ( timeRange, 'Table 2'[Date and Time Finished] ), SECOND ) - break VAR mm = ss / 60 VAR hh = mm / 60 RETURN hhJust use what you need in the return (ss / mm / hh) and change the measure name accordingly.
Regards
If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
8 Replies
- ERD
Community Champion
Hi markefrody ,
Next time, please, provide sample data as text, use the table tool in the editing bar.
You can use the measure below to get your hours:
hh = VAR timeIn = HOUR ( MIN ( 'T1'[TimeIn] ) ) VAR timeOut = HOUR ( MAX ( 'T1'[TimeOut] ) ) VAR timeRange = FILTER ( VALUES ( 'T2'[DateTime] ), HOUR ( 'T2'[DateTime] ) >= timeIn && HOUR ( 'T2'[DateTime] ) <= timeOut ) VAR break = HOUR ( MIN ( 'T1'[BreakStart] ) - MAX ( 'T1'[BreakEnd] ) ) VAR hh = HOUR ( MINX ( timeRange, [DateTime] ) - MAXX ( timeRange, [DateTime] ) ) - break RETURN hhPlease, take into account that both tables are connected via a separate Date table by Date column.
If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
markefrodyHi ERD,
Thank you for your solution. When you say that both tables are connected via seperate Date table by Date column, does it mean I need to setup a relationship between Table 1 and Table 2?- ERD
you need to set up relations between
- Date table and Table 1;
- Date table and Table 2.
If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
- markefrody
Hi ERD,
I tried to setup the relationship of the date table to tables 1 and 2 but it seems I am not getting the right hours. I have placed the Power BI file with the tables for reference in the link below:
https://www.dropbox.com/s/2a0k6javqjjw7wx/Production%20Hours%20per%20Manager.pbix?dl=0