Forum Discussion

k943's avatar
k943
Icon for Helper I rankHelper I
3 years ago
Solved

Calculating email response time (customer service KPI)

I am trying to calculate the time difference between email from customer (which is min("Email message date") first highlighted in yellow) and email from customer service  ( which is the first occurence of True value in the column "From xxx to Customer", assuming "Email message date" is in ascending order). This is the second highlighted row. The time difference should be 1hr and 14 min (74 min). Thank you very much in advance.

 

 

Need Help
  • Aburar_123's avatar
    Aburar_123
    3 years ago

    Hi k943 ,

     

    You can write a calculated column like below,

     

     

    If this post helps then please accept it as a solution. Thanks.

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi k943 ,

     

    I think you can try this code to create a measure.

    Email response time = 
VAR _email_from_customer =
    CALCULATE (
        MIN ( 'Table'[Email Message Date] ),
        FILTER ( 'Table', 'Table'[From xxx to Customer] = FALSE () )
    )
VAR _email_from_customer_service =
    CALCULATE (
        MIN ( 'Table'[Email Message Date] ),
        FILTER ( 'Table', 'Table'[From xxx to Customer] = TRUE () )
    )
RETURN
    DATEDIFF ( _email_from_customer, _email_from_customer_service, MINUTE )

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • k943's avatar
      k943
      Icon for Helper I rankHelper I
      3 years ago

      Hi, thank you for the measure. It works. But is it possible to make a custom column ? in that case, how do I make it? so for each case ID, it will have response time in minutes. In the example above, the column will show 74 minutes for all the rows for this specific case ID. Thank you!

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi k943 ,

         

        My Sample:

        Whole M Code:

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdExC8JADAXgvxJuLvTl5a5eM4vg7lY6OLg5Kf5/FUTB9nq3ho+8kDdN4bjX0AWC7JF7qGhy0IHX9HC+3i9h7gpqqKvB9aNOt0cZWaysoqi6cXuViUa31ILGSp69r8KuopJg/N5eCsyC7KYNKNoyj4u3E01qpZx/9StnW621w5Z2WGlnfgI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Email Message Date" = _t, #"From xxx to Customer" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Email Message Date", type datetime}, {"From xxx to Customer", type logical}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"ID", Order.Ascending}, {"Email Message Date", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"ID", "From xxx to Customer"}, {{"Rows", each _, type table [ID=nullable text, Email Message Date=nullable datetime, From xxx to Customer=nullable logical]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Rows],"Index",1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Email Message Date", "Index"}, {"Custom.Email Message Date", "Custom.Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Custom.Email Message Date", "Email Message Date"}, {"Custom.Index", "Index"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Email response time", each let 
_ID = [ID]
in  
let
_FalseDate =   
List.First(
Table.SelectRows(#"Renamed Columns", each _ID = [ID] and[From xxx to Customer] = false and [Index] = 1)[Email Message Date]),
_TrueDate =
List.First(
Table.SelectRows(#"Renamed Columns", each _ID = [ID] and[From xxx to Customer] = true and [Index] = 1)[Email Message Date])
in
Duration.TotalMinutes(_TrueDate - _FalseDate)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Rows", "Index"}),
    #"Sorted Rows1" = Table.Sort(#"Removed Columns",{{"ID", Order.Ascending}, {"Email Message Date", Order.Ascending}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Sorted Rows1",{{"Email response time", Int64.Type}})
in
    #"Changed Type1"

        Result is as below.

         

        Best Regards,
        Rico Zhou

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

         

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    You have not shared complete information.  Isn't there some kind of an ID in the table as well?  Share some data to work with and show the expected result.  Also, do you want a measure or a calculated column formula solution?

    • k943's avatar
      k943
      Icon for Helper I rankHelper I
      3 years ago

      I would like to make a custom column for each case ID. The picture only shows one case ID. so the result should be a column that says 74 minutes for this case ID. Thank you very much for your help.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        3 years ago

        Hi,

        This should ideally be a measure solution.  In a Table ID's should appear in the row labels and duration (Measure) should show 74 (for ID1).  If you are amenable to my solution, then share data in a format that can be pasted in an MS Excel file.

  • Aburar_123's avatar
    Aburar_123
    Icon for Solution Supplier rankSolution Supplier
    3 years ago

    Hi k943 ,

     

    You can write a calculated column like below,

     

     

    If this post helps then please accept it as a solution. Thanks.