Forum Discussion

rogerthat's avatar
rogerthat
Icon for Helper I rankHelper I
4 years ago

Calc To Include Saturday Working Hours

Hello,
 
I think I posted in the wrong forum, so reposting this in the correct one, but I would like to calculate working hours, the below seems to be working. However, I'm trying to include Saturday working hours and having a hard time understanding where this would fit.
So the calculation should be 
 
M-F 6:00AM TO 6:00P
SATURDAY 6:00A TO 5:00P
 
 
Business Hours Number Format =
VAR _WorkStart =
TIME( 6, 00, 0 )
VAR _WorkFinish =
TIME( 18, 00, 0 )
VAR _1DayWorkingTime =
DATEDIFF( _WorkStart, _WorkFinish, HOUR )
VAR _StartDate =
DATEVALUE([Start (Date/Time)] ) // Set the start Date column here
VAR _EndDate =
DATEVALUE([Finish (Date/Time)] ) // Set the End Date column here
VAR _StartDateTime =
TIMEVALUE([Start (Date/Time)] ) // Set the start Time column here
VAR _EndDateTime =
TIMEVALUE([Finish (Date/Time)] ) // Set the End Time column here
VAR _DaysBetweenStartFinish =
ADDCOLUMNS( 'Date Table', "DayN", WEEKDAY( [Date], 2 ) ) // Use the Date table here
VAR _WorkingDaysBetweenStartFinish =
COUNTX(FILTER(_DaysBetweenStartFinish,
[Date] > _StartDate && [Date] < _EndDate && [DayN] < 7 && [Holiday Yes/No]="No"),
[DayN]) // Sunday and Saturday are weekend days in this calculation // Set [Holiday = No] to exclude those days from the calculation
VAR _Day1WorkingHour =
IF(WEEKDAY( _StartDate, 2 ) < 7,
( MAX( _WorkFinish, _StartDateTime ) - MAX( _StartDateTime, _WorkStart ) ) * 24,
0)
VAR _LastDayWorkingHour =
IF(WEEKDAY( _EndDate, 2 ) < 7,
( MIN( _WorkFinish, _EndDateTime ) - MIN( _EndDateTime, _WorkStart ) ) * 24,
0)
VAR _Duration =
IF(_StartDate = _EndDate&&WEEKDAY(_StartDate,2)<7,
MAX(( MIN( _WorkFinish, _EndDateTime ) - MAX( _WorkStart, _StartDateTime ) ) * 24,
0),
_WorkingDaysBetweenStartFinish * _1DayWorkingTime + _Day1WorkingHour + _LastDayWorkingHour
)
RETURN
_Duration

 

 

 

 

11 Replies

  • Stachu's avatar
    Stachu
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Can you add sample tables (in format that can be copied to PowerBI) from your model with anonymised data? Like this (just copy and paste into the post window).

    Column1Column2
    A1
    B2.5

    This is a calculated column, correct?

    • rogerthat's avatar
      rogerthat
      Icon for Helper I rankHelper I
      4 years ago

      Stachu - correct, its a calculated column. 

      MONDAY THRU FRIDAY- 6:00AM TO 6:00P

      SATURDAY- 6:00A TO 5:00P   
      Start (DATETIME)End (DATETIME)Expected Working Hours
      10/9/2021 4:00:00  PM10/11/2021 8:00:00 AM3
      10/23/2021 4:00:00 PM10/23/2021 6:00:00 PM1
      10/29/2021 5:00:00 PM10/30/2021 8:00:00 AM3

       

      • Stachu's avatar
        Stachu
        Icon for Community Champion rankCommunity Champion
        4 years ago

        can you add sample that would have holiday day in? or in such case there wouldn't be any entries overlapping with the holidays?

    • rogerthat's avatar
      rogerthat
      Icon for Helper I rankHelper I
      4 years ago

      VahidDM - Actually used this method, however I'm trying to implement different Work Hours for saturday which is an hour less than M-F.

       

      M-F 6:00AM TO 6:00P

      SATURDAY 6:00A TO 5:00P