Forum Discussion

Gillard_2's avatar
Gillard_2
Frequent Visitor
5 years ago
Solved

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.

Thanks in advance

Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    5 years ago

    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

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    5 years ago

    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

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    5 years ago

    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.