cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GilesWalker Established Member
Established Member

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

Accepted Solutions
samdthompson Established Member
Established Member

Re: How to calculate the time difference

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 3
samdthompson Established Member
Established Member

Re: How to calculate the time difference

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

GilesWalker Established Member
Established Member

Re: How to calculate the time difference

@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

samdthompson Established Member
Established Member

Re: How to calculate the time difference

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

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 27 members 1,031 guests
Please welcome our newest community members: