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 12:25:02   78/08/18 12:26:08

 

Simplier

 

Task_ID  Date_Created  Date_Completed

 1                    x                x+1

 2                    y                y+1

 3                    z                z+1

 4                    f                 f +1

 

Etc

 

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) ====> y-(x+1)

 

I try to use EARLIER function to return me Date_Completed in a newly calculated column (not measure, I checked), and then calculate the difference normally with DATEDIF. 

 

BUT I constantly get an error that either it "refers to an earlier row context which doesn't exist", or something similar.

 

I need to do it in such a way that than it will calculate this time difference  for the whole column in the same way:

 

Date_Created (task_id = 2) MINUS Date_Completed (task_id=1)

Date_Created (task_id = 3) MINUS Date_Completed (task_id=2)

Date_Created (task_id = 4) MINUS Date_Completed (task_id=3)

 

==> 

 

y-(x+1)

z-(y+1)

f-(z+1)

 

So the cell is not a fixed one but rather a dynamic one (that's why LOOKUPVALUE didn't work for me). I always refer to a date associated with a previous task_id (tas_id-1).

 

 

I kindly ask you for your help because I really cannot find any solution. Thank you in advance.

Need Help
Tutorial Requests

5 Replies

  • 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