Time Difference between two timestamps in same column DAX

Question

I have a dataset where I am trying to calculate the user's first response time to every dealer's comment to a case file using the comment date-time. I've calculated this in hive using lag, lead, and min window functions, but I am trying to calculate this in Power BI.

Any help would be greatly appreciated.

Sorry to put this in the wrong format, when I try to paste my data from excel it's taking as an image

Case#   Comment date_time      lastuse   action_type          note       
4815    2020-09-02 10:08:26.85  NULL    DEALER_COMMENT    i360 Update - Your    
4815    2020-09-02 17:17:08.987 USER1   USER_COMMENT      Bodyshop please ad 
4815    2020-09-05 09:20:27.673 NULL    DEALER_COMMENT    i360 Update - Than    
4815    2020-09-09 16:08:54.44  USER2   USER_COMMENT      Spoke with Robert     
4815    2020-09-15 10:08:30.023 USER3   USER_COMMENT      Called the body sh    
4815    2020-09-17 11:47:28.327 NULL    DEALER_COMMENT    i360 Update - Than    
4815    2020-09-17 16:09:38.44  USER4   USER_COMMENT      Spoke with Robert     
4815    2020-09-22 12:07:32.877 USER5   USER_COMMENT      Spoke with Brian a    
4815    2020-10-01 09:17:44.543 USER6   USER_COMMENT      Called the body sh    
4815    2020-10-01 14:06:21.807 USER7   USER_COMMENT      Spoke with Rick in    
4815    2020-10-06 14:37:24.457 NULL    DEALER_COMMENT    Per Brian, repairs    
4815    2020-10-12 12:10:14.377 NULL    DEALER_COMMENT    Per Robert, the ba    
4815    2020-10-13 08:16:23.117 USER8   USER_COMMENT      Spoke with Robert     
4815    2020-10-13 15:17:31.12  NULL    DEALER_COMMENT    Per Robert, wires     
4815    2020-10-15 09:53:16.16  USER9   USER_COMMENT      Spoke with Michael

I've added a response time column which I manually calculated in excel.

I am expecting datediff between the timestamps that i highlighted below output like below

The dealer was commented on this case on 2020-09-02 at 10:08:02 and my user1 responded to him at 17:17:08 and again dealer added a comment on 2020-09-05 at 09:20:27 and my user2 responded back to him on 2020-09-09 at 16:08:54.

like I said I am trying to calculate user every first response time to dealer comment.

I've created the column to get the dealer update time.

I am trying to get this same type of first user response timestamp to dealer comments to calculate the response time

Dealer Update Time =
CALCULATE (
    MIN ( Query1[Comment Date_Time] ),
    FILTER (
        ALLEXCEPT ( Query1, Query1[Case#], Query1[action_type], Query1[note] ),
        Query1[action_type] IN { "DEALER_COMMENT" }
    )
)

Provide your data in tabular format rather then sharing the image. Is tis your sample data? or Expected output? Also explain the logic. — mkRabbani
If I apply order by on datetime column, will your data still in correct/expected order? — mkRabbani
What is the breakdown for 102.89? they are HH:MM? and Finally, how difference between 2020-09-02 10:08:26.85 and 2020-09-02 17:17:08.987 is 7.14? — mkRabbani
@R_R Yes response time will in hours, hours diff between 2020-09-02 10:08:26.85 and 2020-09-02 17:17:08.987 is 7.145. — Rahul
It's a metric of how quickly users were responding back to the dealer comment. — Rahul

mkRabbani mkRabbani · Accepted Answer · 2020-10-22T16:08:02

Go to Power Query Editor and add a Index Column started from 1. The output will be as below-

Now get back to report by clicking the Close & Apply button and create this below Measure-

hour_difference = 

VAR current_action_type = MIN(your_table_name[action_type])
VAR current_row_index = MIN(your_table_name[Index])
VAR current_row_comment_date_time = MIN(your_table_name[Comment date_time])

VAR previous_row_action_type = 
CALCULATE(
    MAX(your_table_name[action_type]),
    FILTER(
        ALL(your_table_name),
        your_table_name[Index] = current_row_index - 1
    )
)

VAR previous_max_user_comment_index = 
CALCULATE(
    MAX(your_table_name[Index]),
    FILTER(
        ALL(your_table_name),
        your_table_name[Index] < current_row_index
        && your_table_name[action_type] = "USER_COMMENT"
    )
)

VAR previous_dealer_comment_index =
CALCULATE(
    MIN(your_table_name[Index]),
    FILTER(
        ALL(your_table_name),
        your_table_name[Index] > previous_max_user_comment_index
        && your_table_name[action_type] = "DEALER_COMMENT"
    )
)

VAR previous_dealer_comment_date_time =
CALCULATE(
    MIN(your_table_name[Comment date_time]),
    FILTER(
        ALL(your_table_name),
        your_table_name[Index] = previous_dealer_comment_index
    )
)

RETURN
IF(
    current_action_type <> "USER_COMMENT" || previous_row_action_type = current_action_type,
    "",
    DATEDIFF(previous_dealer_comment_date_time,current_row_comment_date_time,MINUTE)/60
)

Here is the final output-

Time Difference between two timestamps in same column DAX

1 Answers