Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Calculate dates difference between different rows AND columns

Hello all,   I need jedis' mind.   Consider I have following data:   Task_ID  Date_Created          Date_Completed     1          08/08/18 12:05:02   08/08/18 12:06:08   2          07/08/18 1...
Need Help
Tutorial Requests
TomMartens's avatar
TomMartens
Super User
8 years ago

Hey,

 

I created two calculated columns, the 1st returns the "Date_Completed" value from the previous Task_ID (currentID - 1) and the 2nd calculates the difference in seconds.

 

 

Date_Created_prev = 
var currentTaskID = 'Table1'[Task_ID]
return
CALCULATE(
    MAX('Table1'[Date_Completed])
    ,FILTER(
        ALL(Table1)
        ,'Table1'[Task_ID] = currentTaskID - 1
    )
)

 

DateDiff = 
DATEDIFF('Table1'[Date_Created],'Table1'[Date_Created_prev],SECOND)

Based on your sample data (I tweeked it a little, because I thought there were typos) the result looks like this:


Hopefully this is what you are looking for.

 

Regards,

Tom

 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hello,

 

Wow, that was fast. Sorry for a dummy question, but where to I place this in? Is is a query or what? 

 

What does this mean?

var currentTaskID

And this SECOND?

 

DateDiff = 
DATEDIFF('Table1'[Date_Created],'Table1'[Date_Created_prev],SECOND)

  Thanks!

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Thank you, I will investigate that!

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hello everyone here again,

       

       

      I just got additional conditions to the problem stated above. So now I have the following data :

       

       

      Parent_task_ID          Task_ID  Date_Created          Date_Completed  

      888                             1           08/08/18 12:05:02    08/08/18 12:06:08

      888                             2           07/08/18 12:25:02    08/08/18 12:26:08

      999                             3           08/08/18 13:05:02    08/08/18 13:06:08

      999                             4           07/08/18 13:25:02    08/08/18 13:26:08

       

       

      What I need to do is to calculate difference between Date_Completed and Date_Created, but not for row 1, row 2, etc - I would use DATEDIF function, but rather like this: Date_Created (task_id = 2) MINUS Date_Completed (task_id=1) 

       

      Now I need to do this procedure for each Parent_task_ID

       

      Could please anybody here help me with this? Thank you so much!