Forum Discussion

LSHagger's avatar
LSHagger
Regular Visitor
3 years ago
Solved

Service Duration in seconds Calculation in DAX

Hi,

 

I have this code in excel that calculates the service duration (in seconds) of a Support ticket, excluding bank holidays, weekends and out of office hours.

Excel Code - =((NETWORKDAYS.INTL(A2,B2,1,$H$2:$H$11)-1)*("18:00"-"7:00")+IF(NETWORKDAYS.INTL(B2,B2,1,$H$2:$H$11),MEDIAN(MOD(B2,1),"7:00","18:00"),"18:00")-MEDIAN(NETWORKDAYS.INTL(A2,A2,1,$H$2:$H$11)*MOD(A2,1),"7:00","18:00"))*86400

 

My Dax code so far - Measure.ServiceHours =

        VAR _StartDate = SELECTEDVALUE(TICKET_MASTER[TICKETSUBMITDATE])
        VAR _EndDate = SELECTEDVALUE(TICKET_MASTER[CLOSEDTIME])
    RETURN
        ((NETWORKDAYS(_StartDate, _EndDate,1,BankHolidayDates)-1)*("18:00"-"7:00")+IF(NETWORKDAYS(_EndDate,_EndDate,1,BankHolidayDates),MEDIAN(mod(_EndDate,1),"7:00","18:00"),"18:00")-MEDIAN(NETWORKDAYS(_StartDate,_StartDate,1,BankHolidayDates)*MOD(_StartDate,1),"7:00","18:00"))*86400

 

Unfortunately Median dax code works differently to excel, can anyone help me convert this into DAX?

 

Example of excel code working below

 

 

duration
Median
Networkdays
Working Hours
  • OwenAuger's avatar
    OwenAuger
    3 years ago

    Hi there LSHagger 

     

    On your immediate issue of calculating the median of three values in DAX, you can use MEDIANX for this (since MEDIAN can only be used with a column reference).

     

    For example, you can change:

     

    MEDIAN(mod(_EndDate,1),"7:00","18:00")

     

    to

    MEDIANX ( { MOD ( _EndDate, 1 ), "7:00", "18:00" }, [Value] )

    This works by constructing a single-column table containing the three values (with an automatic column name "Value") and then finding the median of that single column.

     

    I also had a further look at the DAX expression, and you may want to consider using variables to avoid some repeated calculations and improve readability. I haven't tested the below code, but it is an initial attempt at rewriting slightly. It also avoids casting strings as date/time values:

    Measure.ServiceHours =
VAR _StartDate =
    SELECTEDVALUE ( TICKET_MASTER[TICKETSUBMITDATE] )
VAR _EndDate =
    SELECTEDVALUE ( TICKET_MASTER[CLOSEDTIME] )
VAR _TimeStart =
    TIME ( 7, 0, 0 )
VAR _TimeEnd =
    TIME ( 18, 0, 0 )
VAR _DayLength = _TimeEnd - _TimeStart
VAR _NetWorkdaysStartEnd =
    NETWORKDAYS ( _StartDate, _EndDate, 1, BankHolidayDates )
VAR _NetWorkdaysStart =
    NETWORKDAYS ( _StartDate, _StartDate, 1, BankHolidayDates )
VAR _NetWorkdaysEnd =
    NETWORKDAYS ( _EndDate, _EndDate, 1, BankHolidayDates )
VAR _SecondsPerDay = 86400
RETURN
    (
        ( _NetWorkdaysBase - 1 ) * _DayLength
            + IF (
                _NetWorkdaysEnd,
                MEDIANX ( { MOD ( _EndDate, 1 ), _TimeStart, _TimeEnd }, [Value] ),
                _TimeEnd
            )
            - MEDIANX (
                { _NetWorkdaysStart * MOD ( _StartDate, 1 ), _TimeStart, _TimeEnd },
                [Value]
            )
    ) * _SecondsPerDay

    Regards,

2 Replies

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    3 years ago

    Hi there LSHagger 

     

    On your immediate issue of calculating the median of three values in DAX, you can use MEDIANX for this (since MEDIAN can only be used with a column reference).

     

    For example, you can change:

     

    MEDIAN(mod(_EndDate,1),"7:00","18:00")

     

    to

    MEDIANX ( { MOD ( _EndDate, 1 ), "7:00", "18:00" }, [Value] )

    This works by constructing a single-column table containing the three values (with an automatic column name "Value") and then finding the median of that single column.

     

    I also had a further look at the DAX expression, and you may want to consider using variables to avoid some repeated calculations and improve readability. I haven't tested the below code, but it is an initial attempt at rewriting slightly. It also avoids casting strings as date/time values:

    Measure.ServiceHours =
VAR _StartDate =
    SELECTEDVALUE ( TICKET_MASTER[TICKETSUBMITDATE] )
VAR _EndDate =
    SELECTEDVALUE ( TICKET_MASTER[CLOSEDTIME] )
VAR _TimeStart =
    TIME ( 7, 0, 0 )
VAR _TimeEnd =
    TIME ( 18, 0, 0 )
VAR _DayLength = _TimeEnd - _TimeStart
VAR _NetWorkdaysStartEnd =
    NETWORKDAYS ( _StartDate, _EndDate, 1, BankHolidayDates )
VAR _NetWorkdaysStart =
    NETWORKDAYS ( _StartDate, _StartDate, 1, BankHolidayDates )
VAR _NetWorkdaysEnd =
    NETWORKDAYS ( _EndDate, _EndDate, 1, BankHolidayDates )
VAR _SecondsPerDay = 86400
RETURN
    (
        ( _NetWorkdaysBase - 1 ) * _DayLength
            + IF (
                _NetWorkdaysEnd,
                MEDIANX ( { MOD ( _EndDate, 1 ), _TimeStart, _TimeEnd }, [Value] ),
                _TimeEnd
            )
            - MEDIANX (
                { _NetWorkdaysStart * MOD ( _StartDate, 1 ), _TimeStart, _TimeEnd },
                [Value]
            )
    ) * _SecondsPerDay

    Regards,