powerbi - change Code in Power BI : holiday from Saturday & Sunday to be only Friday - Stack Overflow

Could any one help me in below Dax code , I need to change the holiday from Saturday & Sunday to be only Friday.

Business Hours Number Format =
  VAR _WorkStart = TIME( 8, 00, 0 )
  VAR _WorkFinish = TIME( 17, 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] < 6 && [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 ) < 6,
      ( MAX( _WorkFinish, _StartDateTime ) - MAX( _StartDateTime, _WorkStart ) ) * 24,
      0
    )
  VAR _LastDayWorkingHour =
    IF(
      WEEKDAY( _EndDate, 2 ) < 6,
      ( MIN( _WorkFinish, _EndDateTime ) - MIN( _EndDateTime, _WorkStart ) ) * 24,
      0
    )
  VAR _Duration =
  IF(
    _StartDate = _EndDate&&WEEKDAY(_StartDate,2) < 6,
    MAX(( MIN( _WorkFinish, _EndDateTime ) - MAX( _WorkStart, _StartDateTime ) ) * 24, 0),
    _WorkingDaysBetweenStartFinish * _1DayWorkingTime + _Day1WorkingHour + _LastDayWorkingHour
  )
  RETURN  _Duration

Could any one help me in below Dax code , I need to change the holiday from Saturday & Sunday to be only Friday.

Business Hours Number Format =
  VAR _WorkStart = TIME( 8, 00, 0 )
  VAR _WorkFinish = TIME( 17, 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] < 6 && [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 ) < 6,
      ( MAX( _WorkFinish, _StartDateTime ) - MAX( _StartDateTime, _WorkStart ) ) * 24,
      0
    )
  VAR _LastDayWorkingHour =
    IF(
      WEEKDAY( _EndDate, 2 ) < 6,
      ( MIN( _WorkFinish, _EndDateTime ) - MIN( _EndDateTime, _WorkStart ) ) * 24,
      0
    )
  VAR _Duration =
  IF(
    _StartDate = _EndDate&&WEEKDAY(_StartDate,2) < 6,
    MAX(( MIN( _WorkFinish, _EndDateTime ) - MAX( _WorkStart, _StartDateTime ) ) * 24, 0),
    _WorkingDaysBetweenStartFinish * _1DayWorkingTime + _Day1WorkingHour + _LastDayWorkingHour
  )
  RETURN  _Duration

• powerbi

Share Improve this question Follow edited Jan 3 at 1:07 Sam Nseir 12.6k22 gold badges55 silver badges2121 bronze badges asked Jan 2 at 13:54 Asem omarAsem omar 111 bronze badge 1

• could you pls provide some sample data and expected output? – Ryan Commented Jan 3 at 0:41

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

WEEKDAY( _StartDate, 2 ) < 6 = Mon to Fri
Replace this with WEEKDAY( _StartDate, 2 ) <> 5 ie not Fri.

Same for WEEKDAY( _EndDate, 2 ) < 6, replace with WEEKDAY( _EndDate, 2 ) <> 5.

And replace [DayN] < 6 to [DayN] <> 5