Calculated DateDiff between various rows

[Gillard_2]

I need a calculated column that will give me the duration between various rows:

The table above will always have either one or two records for each unique code.

It will always have when the record was created and if a certain action has been completed it will show a second row of date.

I need a calculated column that will show the DateDiff between the two records that have the same code in the left column.

The versions will always be 1 for the first record but the second record could be anything.

The above table only have two records that have both lines of data so I would expect to see two of the rows in the new column with data and the rest would be blank as the action has not been completed yet.

Hope this makes sense but I am not sure how I can do this.

Thanks in advance

[v-yalanwu-msft · Accepted solution]

Hi, @Gillard_2 ;

According to your description, you also could try to create a column as follows:

datediff =
IF (
    CALCULATE ( DISTINCTCOUNT ( [TIME] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) ) = 2
        && CALCULATE ( MAX ( [TIME] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) ) = [TIME],
    DATEDIFF (
        CALCULATE ( MIN ( [TIME] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) ),
        CALCULATE ( MAX ( [TIME] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) ),
        DAY
    )
)

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu]

@Gillard_2 

Is this what you want?

Column = 
var LAST=MAXX(FILTER('Table','Table'[ID]=EARLIER('Table'[ID])&&'Table'[TIME]<EARLIER('Table'[TIME])),'Table'[TIME])
RETURN IF(ISBLANK(LAST),BLANK(),DATEDIFF(LAST,'Table'[TIME],DAY))

pls see the attachment below