Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MSuser5
Helper II
Helper II

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.

 

sla11.JPGsla1.JPG

 

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 :

sla13.JPG 

 

Kindly help on this 

Thanks,

MS

1 ACCEPTED SOLUTION

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)"

 

yingyinr_0-1661843193778.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-yiruan-msft
Community Support
Community Support

Hi  @MSuser5,

You can refer the following links to get it:

How To Get The Duration Between Two Datetime Or Date Fields In Power BI

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
MSuser5
Helper II
Helper II

Hi Folks,

Could anyone fix this issue in using calculated column DAX

 

Thanks,

MS

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)"

 

yingyinr_0-1661843193778.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.