Calculate time difference to nearest date by ID

Hi Anonymous,

First new a calculated column:

Duration =
DATEDIFF (
    IF (
        Test2[In 1 / Out 0] = 0,
        CALCULATE (
            MAX ( Test2[Date & Time] ),
            FILTER (
                ALLEXCEPT ( Test2, Test2[ID] ),
                Test2[Date & Time] < EARLIER ( Test2[Date & Time] )
                    && Test2[In 1 / Out 0] = 1
            )
        )
    ),
    Test2[Date & Time],
    HOUR
)

Then, create below measures:

In Count = CALCULATE(COUNT(Test2[In 1 / Out 0]),FILTER(Test2,Test2[In 1 / Out 0]=1))+0

Out Count = CALCULATE(COUNT(Test2[In 1 / Out 0]),FILTER(Test2,Test2[In 1 / Out 0]=0))+0

Total Duration = SUM(Test2[Duration])+0

Use a Table visual o display data.

Best regards,

Yuliana Gu