Finding the Sum of Time Duration

[Anonymous]

I am having trouble creating a measure to sum time duration based on shipment number.

I am needing to create a measure to calculate the time duration based on my time data. This will change based on slicers in my report. 

I am wanting to use a card to display the total time that a shipment number was available. For example using the data below, shipment no 65 was available from 2:10pm - 4:09pm. If shipment 65 was selected in the slicer, the card would display 1hr 59 mins. 

Account Name	Shipment No	Time
A	54	5:49:50 PM
X	75	4:19:19 PM
J	85	4:10:51 PM
J	65	4:09:29 PM
C	57	3:52:58 PM
J	85	3:17:52 PM
M	41	3:07:07 PM
J	65	2:16:27 PM
J	65	2:10:29 PM
S	12	2:01:33 PM
X	92	1:49:00 PM

I appreciate any help.

[Anonymous]

Hi @Nathaniel_C , 

This worked perfectly! Thank you!

[Nathaniel_C]

Hi @Anonymous ,
You are very welcome!
Nathaniel

[Nathaniel_C · Accepted solution]

Hi @Anonymous ,
If I understand your question, please try this.

Length = 

var _shipment = MAX(DurationTable[Shipment No])

var _calcMin = CALCULATE(MIN(DurationTable[Time]), FILTER(ALL(DurationTable), DurationTable[Shipment No]=_shipment))

var _calcMax = CALCULATE(MAX(DurationTable[Time]), FILTER(ALL(DurationTable), DurationTable[Shipment No]=_shipment))

var _duration = DATEDIFF(_calcMin,_calcMax,MINUTE)

var _time = ROUNDDOWN(DIVIDE(_duration,60),0) & " Hour(s) " & MOD(_duration,60) & " Minute(s)"

return IF(MAX(DurationTable[Time])= _calcMax, _time)

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel