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 r...
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.

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.