Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GilesWalker
Skilled Sharer
Skilled Sharer

How 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:

 

ConsignmentDocketDate LoadedDepotWagonProductGrossTareNetOvercarry
RA17513RA003242193/06/2016 2:40010YTHFY006-B1306866.821.6545.152.5
RA17513RA003242203/06/2016 2:41010YTHFY007-K1306866.3521.5544.82.35
RA17513RA003242213/06/2016 2:46010YTHFY017-X1306866.8522.5544.33.55
RA17513RA003242223/06/2016 2:47010YTHFY005-P1306866.4521.145.352.2
RA17513RA003242233/06/2016 2:53010YTHFY004-G1306866.9521.945.052.75
RA17513RA003242243/06/2016 2:54010YTHFY012-J1306866.722.1544.553
RA17513RA003242253/06/2016 2:59010YTHFY0021306866.9521.945.051.55
RA17513RA003242263/06/2016 3:00010YTHFY015-N1306866.8521.645.252.6
RA17513RA003242273/06/2016 3:06010YTHFY0011306866.822.2544.554.05
RA17513RA003242283/06/2016 3:07010YTHFY010-Y1306866.7520.8545.91.95
RA17513RA003242303/06/2016 3:13010YTHFY008-T1306866.920.7546.151.85
RA17513RA003242313/06/2016 3:14010YTHFY003-U1306866.820.546.31.5
RA17513RA003242323/06/2016 3:20010YTHFY018-R1306866.852145.851.8
RA17513RA003242333/06/2016 3:21010YTHFY016-W1306866.9521.445.552.4
RA17513RA003242343/06/2016 3:26010YTHFY011-K1306866.8520.85461.7
RA17513RA003242353/06/2016 3:28010YTHFY009-F1306866.8521.0545.82

 

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

 

 

1 ACCEPTED SOLUTION
samdthompson
Memorable Member
Memorable 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

// if this is a solution please mark as such. Kudos always appreciated.

View solution in original post

3 REPLIES 3
samdthompson
Memorable Member
Memorable 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

// if this is a solution please mark as such. Kudos always appreciated.

@samdthompson - 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

No worries mate, its just one of those fresh eyes things.

// if this is a solution please mark as such. Kudos always appreciated.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.