Calculate time difference to nearest date by ID

[Anonymous]

hey peeps, 

How do I calculate the time duration by the same ID over difference dates? 

Transaction Date	Date & Time	ID	In 1 / Out 0
9/5/2018	9/5/2018 5:18:20 PM	22985	1
6/2/2017	6/2/2017 12:00:00 PM	1572	1
7/2/2017	7/2/2017 4:00:00 PM	1572	0
8/2/2017	8/2/2017 10:00:00 AM	1572	1
11/2/2017	11/2/2017 12:00:00 PM	1572	0

Expected Output as below

Example

1572 clocks in on 6/2 12pm, and clocks out on 7/2 4pm, = 24 + 4 = 28 hours 

1572 clocks in on 8/2 10am, and clocks out on 11/2 12pm, = 24 + 24 +24 + 4 = 78 hours

Total for 1572 = 28 + 78 = 106 hours

22985 clocks in once on 9/5, but there's no nearest clock out, hence = 0. 

FIN	In Count	Out Count	Total Duration
1572	2	2	28 + 74 = 102 hours
22985	1	0	0 cause no nearest out

[v-yulgu-msft · Accepted solution]

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

[Anonymous]

Brilliant!!!