Forum Discussion

CMoppet's avatar
CMoppet
Helper IV
2 years ago

Duration Calculation only within specified hours

Hello,  I'm trying to calculate a customer response time in hours, only counting hours between 08:30 and 17:00 on working days.

The following script is working perfectly for jobs where the time of creation and start is within working hours.  However, if jobs are created and/or started outside of 08:30-17:00, the clock shouldn't keep ticking.   So, if a job is created at 21:00, the clock shouldn't begin counting until 08:30 the following day.  If we create a job at 21:00 and start at 07:00, it should have zero hours response time.    Please can someone help me figure out why I'm not getting correct results in these scenarios?   

Here is the script, and a screenshot of actual results and expected results:

 

UK Response Hours =
VAR _MinDate = 'WO Raw Data'[Created Date & Time]
VAR _MaxDate = 'WO Raw Data'[Arrive Date (Formatted)]
VAR _TimeMinDate = TIME( HOUR( _MinDate ), MINUTE( _MinDate ), 0 )
VAR _TimeMaxDate = TIME( HOUR( _MaxDate ), MINUTE( _MaxDate ), 0 )
VAR _Fail = ( _MinDate > _MaxDate ) || ISBLANK( _MinDate ) || ISBLANK( _MaxDate )
VAR __TableHolidays = DISTINCT('UK Holiday'[Date])
RETURN
    IF( _Fail , 0,
            VAR _DatesToExclude = __TableHolidays
            VAR _ValidDates = NETWORKDAYS( _MinDate, _MaxDate, 1, _DatesToExclude )
            VAR _Time1 = TIME( 8, 30, 0 )
            VAR _Time2 = TIME( 12, 30, 0 )
            VAR _Time3 = TIME( 13, 30, 0 )
            VAR _Time4 = TIME( 17, 00, 0 )
            VAR _Test1 = _ValidDates > 0
            VAR _Minus = _Time2 - MIN( _Time2, _TimeMinDate ) + _Time4 - MAX( _Time3, _TimeMinDate )
            VAR _Plus = MIN( _Time2, _TimeMaxDate ) - _Time1 + MAX( _Time3, _TimeMaxDate ) - _Time3
            VAR _Calc = ((( _ValidDates - 1 * _Test1 ) * 8.5 * 60 )
                - ( 8.5 * 60 - ( HOUR( _Minus ) * 60 + MINUTE( _Minus ) ))
                + ( HOUR( _Plus ) * 60 + MINUTE( _Plus ) ))
                / 60
            RETURN _Calc )
 
Many Thanks

 

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi CMoppet ,

     

    I can't quite understand your measure because of the unmentioned data fields and how you define these operations.

     

    Best regards,
    Community Support Team_ Scott Chang