Forum Discussion
Skilled SharerHow to calculate the time difference
Hi everyone,
This one has been driving me crazy for a couple days and I am sure I am just over thinking it. Hope someone can assist with this issue:
I have a few thousand rows of data which consist of the below data:
| Consignment | Docket | Date Loaded | Depot | Wagon | Product | Gross | Tare | Net | Overcarry |
| RA17513 | RA00324219 | 3/06/2016 2:40 | 010Y | THFY006-B | 13068 | 66.8 | 21.65 | 45.15 | 2.5 |
| RA17513 | RA00324220 | 3/06/2016 2:41 | 010Y | THFY007-K | 13068 | 66.35 | 21.55 | 44.8 | 2.35 |
| RA17513 | RA00324221 | 3/06/2016 2:46 | 010Y | THFY017-X | 13068 | 66.85 | 22.55 | 44.3 | 3.55 |
| RA17513 | RA00324222 | 3/06/2016 2:47 | 010Y | THFY005-P | 13068 | 66.45 | 21.1 | 45.35 | 2.2 |
| RA17513 | RA00324223 | 3/06/2016 2:53 | 010Y | THFY004-G | 13068 | 66.95 | 21.9 | 45.05 | 2.75 |
| RA17513 | RA00324224 | 3/06/2016 2:54 | 010Y | THFY012-J | 13068 | 66.7 | 22.15 | 44.55 | 3 |
| RA17513 | RA00324225 | 3/06/2016 2:59 | 010Y | THFY002 | 13068 | 66.95 | 21.9 | 45.05 | 1.55 |
| RA17513 | RA00324226 | 3/06/2016 3:00 | 010Y | THFY015-N | 13068 | 66.85 | 21.6 | 45.25 | 2.6 |
| RA17513 | RA00324227 | 3/06/2016 3:06 | 010Y | THFY001 | 13068 | 66.8 | 22.25 | 44.55 | 4.05 |
| RA17513 | RA00324228 | 3/06/2016 3:07 | 010Y | THFY010-Y | 13068 | 66.75 | 20.85 | 45.9 | 1.95 |
| RA17513 | RA00324230 | 3/06/2016 3:13 | 010Y | THFY008-T | 13068 | 66.9 | 20.75 | 46.15 | 1.85 |
| RA17513 | RA00324231 | 3/06/2016 3:14 | 010Y | THFY003-U | 13068 | 66.8 | 20.5 | 46.3 | 1.5 |
| RA17513 | RA00324232 | 3/06/2016 3:20 | 010Y | THFY018-R | 13068 | 66.85 | 21 | 45.85 | 1.8 |
| RA17513 | RA00324233 | 3/06/2016 3:21 | 010Y | THFY016-W | 13068 | 66.95 | 21.4 | 45.55 | 2.4 |
| RA17513 | RA00324234 | 3/06/2016 3:26 | 010Y | THFY011-K | 13068 | 66.85 | 20.85 | 46 | 1.7 |
| RA17513 | RA00324235 | 3/06/2016 3:28 | 010Y | THFY009-F | 13068 | 66.85 | 21.05 | 45.8 | 2 |
The consignment number will always be unique and then the Docket numbers will be unique to the distinct consignment number. There are hundreds of consignment numbers. All I am trying to work out is the seconds between the last row (in example docket RA0032435) and the first row. The seconds will then be converted to minutes.
I created this measure to calculate the seconds:
Time test = SUMX('DMS files',HOUR('DMS files'[Date time])*3600+MINUTE('DMS files'[Date time])*60+SECOND('DMS files'[Date time]))
Then I tried using the CALCULATE function as follows:
Difference = CALCULATE([Time test],FILTER('DMS files',MAX('DMS files'[Docket.2]))) - CALCULATE([Time test],FILTER('DMS files',MIN('DMS files'[Docket.2])))
This then just gives 0 as the min and max is done on a row by row basis.
Docket.2 is a custom column where the RA in the docket column is removed thus providing me with numbers. The file name is DMS files.
Hope someone can help with this.
Thanks,
Giles
Here you go:
docket load time = (calculate(MAX(Table1[Date Loaded]),allexcept(Table1,Table1[Consignment]))-calculate(min(Table1[Date Loaded]),allexcept(Table1,Table1[Consignment])))*3600
3 Replies
samdthompsonMemorable Member
Here you go:
docket load time = (calculate(MAX(Table1[Date Loaded]),allexcept(Table1,Table1[Consignment]))-calculate(min(Table1[Date Loaded]),allexcept(Table1,Table1[Consignment])))*3600
GilesWalkersamdthompson - thank you for helping with this. I had tried using the all except on consignments but hadnt done the min and max on the date. Thanks for your help with this.
Giles
- samdthompson
No worries mate, its just one of those fresh eyes things.