Forum Discussion

MSuser5's avatar
MSuser5
Icon for Helper III rankHelper III
4 years ago
Solved

calculate SLA between start - end dates except weekends,holidays

Hi Folks,

i have two dates with time (start date , end date) need to calculate how long it tooks complete the activity except weekends and holidays in calender table working day, holiday column is available . Ex. 22 july  - 26 july  ( here middle 23,24 july as fall in  weekend hence we need to exclude) how to get as expected result attached here.

 

 

i tried below cal. column but counting days, total hours as well kindly correct me this DAX:

VAR start_date = 'Table 1’[start date]

VAR end_date = 'Table 1’[end date]

VAR T1 = CALENDAR(DATE(2022,01,01),DATE(2022,12,31))

VAR T2 =FILTER(calender,calender[IS_Workingday] = "Working day" && calender[IS_Holiday] = "Working Day" && calender[Date] >=start_date && calender[Date] <= end_date)

VAR nodays =COUNTROWS(T2)

VAR Timediff = TIMEVALUE(end_date-start_date)

VAR noHrs = HOUR(Timediff)

VAR noMIN =MINUTE(Timediff)

VAR noSEC = SECOND(Timediff)

Return

nodays & " Day(s) " & noHrs & ":" & noMIN & ":" & noSEC

 

 wrong output on above code example :

 

 

Kindly help on this 

Thanks,

MS

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi MSuser5 ,

    I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a calculated column as below:

     

    Activity took = 
VAR _sdate =
    DATE ( YEAR ( 'Table 1'[start date] ), MONTH ( 'Table 1'[start date] ), DAY ( 'Table 1'[start date] ) )
VAR _edate =
    DATE ( YEAR ( 'Table 1'[end date] ), MONTH ( 'Table 1'[end date] ), DAY ( 'Table 1'[end date] ) )
VAR _sstart =
    DATEVALUE ( ( _sdate + 1 ) & " " & TIME ( 0, 0, 0 ) )
VAR _eend =
    DATEVALUE ( ( _edate ) & " " & TIME ( 0, 0, 0 ) )
VAR _wehdates =
    CALCULATETABLE (
        VALUES ( 'calender'[Date] ),
        FILTER (
            'calender',
            'calender'[IS_Workingday] = "Weekend"
                || 'calender'[IS_Holiday] = "Holiday"
        )
    )
VAR _s1duration =
    IF (
        _sdate IN _wehdates,
        0,
        DATEDIFF ( 'Table 1'[start date], _sstart, SECOND )
    )
VAR _e1duration =
    IF (
        _edate IN _wehdates,
        0,
        DATEDIFF ( _eend, 'Table 1'[end date], SECOND )
    )
VAR _duration =
    IF (
        _sdate = _edate,
        DATEDIFF ( 'Table 1'[start date], 'Table 1'[end date], SECOND ),
        _s1duration + _e1duration
    )
VAR _minutes =
    INT ( _duration / 60 )
VAR _RemainingSeconds =
    MOD ( _duration, 60 )
VAR _hours =
    INT ( _minutes / 60 )
VAR _RemainingMinutes =
    MOD ( _minutes, 60 )
VAR _days =
    INT ( _hours / 24 )
VAR _RemainingHours =
    MOD ( _hours, 24 )
VAR _bdates =
    CALCULATE (
        DISTINCTCOUNT ( 'calender'[Date] ),
        FILTER (
            'calender',
            'calender'[IS_Workingday] = "Working day"
                && 'calender'[IS_Holiday] = "Working day"
                && 'calender'[Date] > _sstart
                && 'calender'[Date] < _eend
        )
    )
RETURN 
    IF ( _sdate = _edate, 0, ( _bdates + _days ) ) & " Day(s) " & _RemainingHours & " hr(s) " & _RemainingMinutes & " min(s) " & _RemainingSeconds & " sec(s)"

     

    Best Regards

3 Replies

  • MSuser5's avatar
    MSuser5
    Icon for Helper III rankHelper III
    4 years ago

    Hi Folks,

    Could anyone fix this issue in using calculated column DAX

     

    Thanks,

    MS

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi MSuser5 ,

      I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a calculated column as below:

       

      Activity took = 
VAR _sdate =
    DATE ( YEAR ( 'Table 1'[start date] ), MONTH ( 'Table 1'[start date] ), DAY ( 'Table 1'[start date] ) )
VAR _edate =
    DATE ( YEAR ( 'Table 1'[end date] ), MONTH ( 'Table 1'[end date] ), DAY ( 'Table 1'[end date] ) )
VAR _sstart =
    DATEVALUE ( ( _sdate + 1 ) & " " & TIME ( 0, 0, 0 ) )
VAR _eend =
    DATEVALUE ( ( _edate ) & " " & TIME ( 0, 0, 0 ) )
VAR _wehdates =
    CALCULATETABLE (
        VALUES ( 'calender'[Date] ),
        FILTER (
            'calender',
            'calender'[IS_Workingday] = "Weekend"
                || 'calender'[IS_Holiday] = "Holiday"
        )
    )
VAR _s1duration =
    IF (
        _sdate IN _wehdates,
        0,
        DATEDIFF ( 'Table 1'[start date], _sstart, SECOND )
    )
VAR _e1duration =
    IF (
        _edate IN _wehdates,
        0,
        DATEDIFF ( _eend, 'Table 1'[end date], SECOND )
    )
VAR _duration =
    IF (
        _sdate = _edate,
        DATEDIFF ( 'Table 1'[start date], 'Table 1'[end date], SECOND ),
        _s1duration + _e1duration
    )
VAR _minutes =
    INT ( _duration / 60 )
VAR _RemainingSeconds =
    MOD ( _duration, 60 )
VAR _hours =
    INT ( _minutes / 60 )
VAR _RemainingMinutes =
    MOD ( _minutes, 60 )
VAR _days =
    INT ( _hours / 24 )
VAR _RemainingHours =
    MOD ( _hours, 24 )
VAR _bdates =
    CALCULATE (
        DISTINCTCOUNT ( 'calender'[Date] ),
        FILTER (
            'calender',
            'calender'[IS_Workingday] = "Working day"
                && 'calender'[IS_Holiday] = "Working day"
                && 'calender'[Date] > _sstart
                && 'calender'[Date] < _eend
        )
    )
RETURN 
    IF ( _sdate = _edate, 0, ( _bdates + _days ) ) & " Day(s) " & _RemainingHours & " hr(s) " & _RemainingMinutes & " min(s) " & _RemainingSeconds & " sec(s)"

       

      Best Regards