cancel
Showing results for
Did you mean:
Regular Visitor

## Calculate dates difference between different rows AND columns

Hello all,

I need jedis' mind.

Consider I have following data:

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

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:

==>

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Calculate dates difference between different rows AND columns

Hey,

here you will find a little tutorial on how to create Calculated Columns in Power BI

https://docs.microsoft.com/en-us/power-bi/desktop-tutorial-create-calculated-columns

This describes how to use variable in DAX
https://powerbi.tips/2017/05/using-variables-within-dax/

This describes the DAX function DATEDIFF

https://msdn.microsoft.com/en-us/query-bi/dax/datediff-function-dax

Regards,

Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
5 REPLIES 5
Super User

## Re: Calculate dates difference between different rows AND columns

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 =
return
CALCULATE(
MAX('Table1'[Date_Completed])
,FILTER(
ALL(Table1)
)
) ```

```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

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Regular Visitor

## Re: Calculate dates difference between different rows AND columns

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!

Super User

## Re: Calculate dates difference between different rows AND columns

Hey,

here you will find a little tutorial on how to create Calculated Columns in Power BI

https://docs.microsoft.com/en-us/power-bi/desktop-tutorial-create-calculated-columns

This describes how to use variable in DAX
https://powerbi.tips/2017/05/using-variables-within-dax/

This describes the DAX function DATEDIFF

https://msdn.microsoft.com/en-us/query-bi/dax/datediff-function-dax

Regards,

Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Regular Visitor

## Re: Calculate dates difference between different rows AND columns

Thank you, I will investigate that!

Highlighted
Regular Visitor

## Re: Calculate dates difference between different rows AND columns

Hello everyone here again,

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

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!