Forum Discussion
Time Values Distributed Into Shifts
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.
Hi 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?
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.
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=0markefrody , please, use Date column from the Date table in your visual.
If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
Hi ERD,
Have now applied the Date column in the Data table but I am getting 0 hours in most of the days.
As per checking, there is production hours (Table 2) for these days.