Forum Discussion
Calculate time difference to nearest date by ID
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 |
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
2 Replies
v-yulgu-msftMicrosoft Employee
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