Forum Discussion

jereaallikko's avatar
jereaallikko
Helper III
5 years ago

Calculating date/hour difference from separate rows

Hi all,

 

I am having a messy problem with calculating time difference (DD,HH,MM) of separate rows. 

I have dataset of tasks, containing ALL the revision history. So basically, all the steps, modifications, updates etc. done for each work task (based on ID). Illustrated as below:

 

 

 

 

 

 

 

 

 

I am trying to calculate the response time for EVERY Information request. So, time difference between "sent" and "responded" info request. The difficulty is to form pairs of them, since there is no label or ID to track them. I have created conditional column "Type" for each "sent." = "Request" and "responded." = "Response".

 

I created two table visuals (Requests & Responses) to illustrate: Both are filtered by type. In addition, an item of "Information request was responded. Team changed." of Task 13310 was filtered out, since it is not an actual response to request. Therefore, one request is not yet responded and is "without pair":

 

Is there a way to use DAX and calculate the duration difference between each of the pairs? 

 

 

Difficult to explain the problem, but hopefully someone could help me out. If there is more information needed, don't hesitate to ask! Thanks.

 

BR,

Jere

 

Need Help
Tips and Tricks

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    jereaallikko , Try a new measure



    averageX(values(Table[Task ID]), datediff(calculate(Min(Table[revision Date]), filter(Table, Table[Type] ="Request")),calculate(Max(Table[revision Date]), filter(Table, Table[Type] ="Response")), hour))

     

    or

     



    sumX(values(Table[Task ID]), datediff(calculate(Min(Table[revision Date]), filter(Table, Table[Type] ="Request")),calculate(Max(Table[revision Date]), filter(Table, Table[Type] ="Response")), hour))

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    5 years ago

    Hi jereaallikko 

     

    I guess I understand your requirement but still hope to confirm some points first. 

    1. For a task with multiple pairs like 43212, a pair should be a info request event and the first info responded event happened after it, right?

    2. Do you need other non-pair rows for other use in the report? Can we filter out them to reduce the rows in the model or you want to keep them?

    3. You hope to deal with this with DAX method or also welcome some M in Power Query Editor with some data transformation?

     

    Regards,
    Community Support Team _ Jing