cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## 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

1 ACCEPTED SOLUTION
Super User

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,

Owen Auger
Blog
2 REPLIES 2
Super User

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,

Owen Auger
Blog
Regular Visitor

worked amazing, thanks for your help.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors