Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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 =
Unfortunately Median dax code works differently to excel, can anyone help me convert this into DAX?
Example of excel code working below
Solved! Go to Solution.
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,
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,
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
25 | |
17 | |
12 | |
12 | |
10 |
User | Count |
---|---|
33 | |
25 | |
16 | |
14 | |
13 |