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

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
    hh

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