topic Re: Service Duration in seconds Calculation in DAX in DAX Commands and Tips

Service Duration in seconds Calculation in DAX

LSHagger — Thu, 20 Oct 2022 10:14:56 GMT

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

Re: Service Duration in seconds Calculation in DAX

OwenAuger — Thu, 20 Oct 2022 10:48:06 GMT

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

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,

Re: Service Duration in seconds Calculation in DAX

LSHagger — Thu, 20 Oct 2022 11:16:22 GMT

OwenAuger

worked amazing, thanks for your help.