Generating Average time to resolution excluding weekends in last 30 days

[Radhika_Kanaka]

Hi,

  I am trying to create a report that displays the average time to reolve a ticket in the last 30 days. This must exclude weekends.

How can I achieve this with DAX?

[Anonymous · Accepted solution]

Hi @Radhika_Kanaka ,

You will need to create a weekday column.

_weekday = WEEKDAY('Table'[date])

Then create a rank column.

ranks =

var _rank = RANKX(FILTER('Table','Table'[_weekday]<6),'Table'[date],,DESC,Skip)

return

IF('Table'[_weekday]<6,_rank,99999)

Then create the average column.

_average = CALCULATE(AVERAGE('Table'[value]),FILTER('Table','Table'[ranks]<EARLIER('Table'[ranks])+30&&'Table'[ranks]>=EARLIER('Table'[ranks])))

Best Regards,

Jay

[Radhika_Kanaka]

When I tried to invoke the function with Datetime datatype columns. The resultant custom column is displaying the below error:

Expression.Error: We cannot convert the value #datetime(2022, 3, 22, 17, 34, 0) to type Date.
Details:
Value=3/22/2022 5:34:00 PM
Type=[Type]

[Tutu_in_YYC]

if this the custom function

(InitialDate as date, FinalDate as date ) as number => 
let
DaysBetweenDates = Duration.Days(FinalDate-InitialDate),
DaysList = List.Dates(List.Min({InitialDate,FinalDate}),Number.Abs(DaysBetweenDates)+1, Duration.From(1)),
WeekDaysList = List.Select(DaysList, each (Date.DayOfWeek(_, Day.Monday) < 5) ),
WorkingDays = (if DaysBetweenDates < 0 then -1 else 1) * List.Count(WeekDaysList)
in
WorkingDays

You might want to consider InitialDate as datetime, FinalDate as datetime

[Tutu_in_YYC]

Also check this tutorial out. I hope it helps

[Radhika_Kanaka]

@Tutu_in_YYC  Thank you for your quick response.

I managed to get M code that displays resolution time in days excluding weekends. My requirement is to get the resolution time (difference between created date and resolved date)in hours, minutes, seconds. M code is new to me. Would you be able to help on this?? Attaching the M code for reference. Appreciate your response.

(InitialDate as date, FinalDate as date ) as number => 
let
DaysBetweenDates = Duration.Days(FinalDate-InitialDate),
DaysList = List.Dates(List.Min({InitialDate,FinalDate}),Number.Abs(DaysBetweenDates)+1, Duration.From(1)),
WeekDaysList = List.Select(DaysList, each (Date.DayOfWeek(_, Day.Monday) < 5) ),
WorkingDays = (if DaysBetweenDates < 0 then -1 else 1) * List.Count(WeekDaysList)
in
WorkingDays

[Tutu_in_YYC]

Hi Radhika,
There are so many options, it will depends on the data that you have and how you want to model it. If you have some sample data that you are working on, we can probably figure out a direction and try to leverage the function DATEDIFF.

But the general idea is to use time-intelligence functions to generate the metrics you want to track. Have a look at these DAX patterns