Duration In Business Hours Only

[paulosbrumbos]

Hi There, I am struggling to do the following, I have tried several different approaches, but nothing yet worked.

I am trying to prepare a bi report to show the time to respond to our IT tickets.

I have the following in my query already, DateTime of ticket created and 1st response, thats easy.

But, in our company people can log tickets at any time of the day or day of the weekm, however I want to only consider duration between. Mon - Fri 8am - 5pm.

So some examples.

User Logs ticket on Friday 5pm We respond Monday 8.30am the duration would be ( hh:mm ) - 00:30

User Logs a Ticket on Monday 8am we respond 5pm the Same day the duration would be - 09:00

User Logs a Ticket on Monday 8am we respond 9am the Following day the duration would be - 10:00

User Logs a Ticket on Friday 4:45pm we respond 8:15 the following Monday duration would be 00:30

Is this possible in Query Editor or Dax?

Many Thanks

[Vera_33 · Accepted solution]

Hi @paulosbrumbos 

If you don't count other holidays, and tickets raised on Friday always be responded next Monday, then try it, similar to https://community.powerbi.com/t5/Power-Query/Custom-Column-to-calculate-total-Early-Morning-and-Late...

paste it in Advanced Editor to see the custom column

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8vRzNjAwMFTSUTIw1je01DcyMDJUMDS3MjCACBkZQYQMLKyMDZRidWA6jDCk0XVADEHoMMatwxgqZImqwwTNVWZWJqYYhhiaKsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Ticket number" = _t, #"Log date" = _t, #"Respond date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ticket number", type text}, {"Log date", type datetime}, {"Respond date", type datetime}}),
    NonWorkingTime = List.Transform(List.Numbers(0,15*60), each #time(17,0,0)+#duration(0,0,_,0)),
    CustomColumn = Table.AddColumn(#"Changed Type", "Total Minutes", each 
    [a=Date.DayOfWeekName( Date.From([Log date])),
    b = [Respond date]-[Log date],
    c = if a = "Friday" then b-#duration(2,0,0,0) else b,
    d= List.Count( List.Difference(  List.Transform(List.DateTimes([Log date], Duration.TotalMinutes(c), #duration(0,0,1,0)), each Time.From(_)), NonWorkingTime))][d])
in
    CustomColumn

[Smauro]

Hi @paulosbrumbos ,

I wrote this function some time ago.
You'll need a separate list of the weekends for it to work in your case, just a separate query with something like:
List.Select(List.Dates(AStartDate, Duration.TotalDays(AnEndDate-AStartDate) + 1, #duration(1,0,0,0)), each Date.DayOfWeek(_,Day.Monday) > 4)

Cheers,

[MattAllington]

I wrote 2 articles about this. Here is the first one. https://exceleratorbi.com.au/calculating-business-hours-using-dax/

make sure you read the second one too, as the DAX is beautiful 🤩. The link is at the end of the first one.