Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
46 | |
39 | |
19 | |
19 |