Forum Discussion

markefrody's avatar
markefrody
Post Patron
4 years ago
Solved

Time 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 wor...
Need Help
  • ERD's avatar
    ERD
    4 years ago

    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
    hh

    Just 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.

ERD's avatar
ERD
Community Champion
4 years ago

markefrody , 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.

markefrody's avatar
markefrody
Post Patron
4 years ago

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. 





  • ERD's avatar
    ERD
    Community Champion
    4 years ago

    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
    hh

    Just 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.

    • markefrody's avatar
      markefrody
      Post Patron
      4 years ago

      ERD, thank you for your assistance.  It's now working.