cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Calculated DateDiff between various rows

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.

1 ACCEPTED SOLUTION
Community Support

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.

2 REPLIES 2
Community Support

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.

Super User

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

Proud to be a Super User!