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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
LSHagger
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

LSHagger_1-1666260350534.png

 

 

1 ACCEPTED SOLUTION
OwenAuger
Super User
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
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

2 REPLIES 2
OwenAuger
Super User
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
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

@OwenAuger 

worked amazing, thanks for your help.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors