Subtotal Earliest time and latest time difference

[fever003]

Hi,

I am trying to calculate the duration between the earliest and last timestamps in a day for each person.

Group Name Login Time Logout Time
1 Ben 2/5/2015 8:00 2/5/2015 9:00
1 Ben 2/5/2015 13:00 2/5/2015 18:00
2 Amy 2/5/2015 8:00 2/5/2015 18:00

Formulas

First Login:=MIN(table1[Login Time])
Last Logout:=MAX(table1[Logout Time])

Duration:=1*SUMX(table1,[Last Logout]-[First Login])

The results I am getting Ben in the pivot is

Group Name First Login Last Logout Duration
1 Ben 2/5/2015 8:00 2/5/2015 18:00 6:00:00
2 Amy 2/5/2015 8:00 2/5/2015 18:00 8:00:00
Grand Total 14:00:00

The results I am expecting is,

Group Name First Login Last Logout Duration
1 Ben 2/5/2015 8:00 2/5/2015 18:00 8:00:00
2 Amy 2/5/2015 8:00 2/5/2015 18:00 8:00:00
Grand Total 16:00:00

The concern now is the duration and grand total part.

Any help would be appreciated.

Thanks

 

[scottsen]

I'm super confused by Amy. 8 -> 18 sounds like 10hr duration... not 8!?

Anyway, I think the problem is that you are doing SUMX(table1, ...) which is looking at each row, 1 at a time. You want to look at 1 PERSON at a time...

Try... SUMX(values(table[Login]), ...)

 

[fever003]

Ooops, im sorry. You are right, it should be 10 hours for Amy. Will give it a try, but will that formula you suggested give the duration?