Forum Discussion

paulosbrumbos's avatar
paulosbrumbos
Frequent Visitor
5 years ago
Solved

Duration In Business Hours Only

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

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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-Night/m-p/1717573#M52114 

    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

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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-Night/m-p/1717573#M52114 

    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's avatar
    Smauro
    Solution Sage
    5 years ago

    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,