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....
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