cancel
Showing results for
Did you mean:
New Member

## Help with Durations/TimSpans

Hello Everyone,

Please can anyone help with a problem I'm having with durations?

I'd like to have output like this:

 Who Task Average of JobLen Median Of JobLen Bill A 39:12 40:12 Bill B 37:37 37:49 John A 30:20 30:05 John B 26:42 26:39

My table, called Jobs looks like this:

 Who Task Start End Bill A 15/09/2021 12:54 17/09/2021 00:57 Bill A 20/09/2021 01:26 21/09/2021 17:39 Bill A 11/10/2021 00:25 12/10/2021 17:48 Bill B 11/09/2021 20:56 13/09/2021 09:25 Bill B 29/08/2021 19:49 31/08/2021 09:37 Bill B 29/08/2021 19:46 31/08/2021 09:37 Bill B 11/10/2021 03:23 12/10/2021 17:47 John A 04/10/2021 21:04 06/10/2021 01:39 John A 13/09/2021 11:41 14/09/2021 16:39 John A 11/09/2021 18:33 13/09/2021 01:44 John A 30/09/2021 01:26 01/10/2021 10:05 John B 02/08/2021 09:49 03/08/2021 12:19 John B 30/08/2021 09:33 31/08/2021 12:12 John B 30/09/2021 11:30 01/10/2021 14:28

My thoughts were that I'd have a Column for the duration that is a DateTime:

JobLen = Jobs[End] - Jobs[Start]

Then I'd use a Measure for the Average, that gets the average, gets the total seconds from that average and then formats the output the way I want it:

AvgJobLenTxt =
VAR avgJobLen = AVERAGE(Jobs[JobLen])
VAR seconds = -- I don't know how to convert to seconds 😞

VAR totalHours = INT(seconds / 3600)
VAR totalMinutes = MOD(INT(seconds / 60), 60)

RETURN RIGHT("0000" & totalHours, 4) & ":" & RIGHT("00" & totalMinutes, 2)

As you can see, I don't know how to work out the total number of seconds from the result of the average function, I imagine I'll have the same problem with the Median function as well.
Does anyone know how to do this, or am I approaching this from completely the wrong angle?

I apologise for the length of the post, but I felt it was needed to give my question context.

Thank you for taking the time to read.
And a double thank you if you take the time to post.

1 ACCEPTED SOLUTION
Community Support

Hi @Beefheart ,

Here's my solution.

1.Create a calculated column to calculate the minutes.

``Minute = DATEDIFF([Start],[End],MINUTE)``

2.Create another calculated column to get the average time.

``````Average of JobLen = var _minute=AVERAGEX(FILTER('Jobs',[Task]=EARLIER(Jobs[Task])&&[Who]=EARLIER(Jobs[Who])),[Minute])
return INT(DIVIDE(_minute,60))&":"&INT(MOD(_minute,60))&":00"``````

3.The column to get the medidan time.

``````Median Of JobLen = var _minute=MEDIANX(FILTER('Jobs',[Task]=EARLIER(Jobs[Task])&&[Who]=EARLIER(Jobs[Who])),[Minute])
return INT(DIVIDE(_minute,60))&":"&INT(MOD(_minute,60))&":00"``````

You can check more details from the attachment.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 REPLIES 3
Community Support

Hi @Beefheart ,

Here's my solution.

1.Create a calculated column to calculate the minutes.

``Minute = DATEDIFF([Start],[End],MINUTE)``

2.Create another calculated column to get the average time.

``````Average of JobLen = var _minute=AVERAGEX(FILTER('Jobs',[Task]=EARLIER(Jobs[Task])&&[Who]=EARLIER(Jobs[Who])),[Minute])
return INT(DIVIDE(_minute,60))&":"&INT(MOD(_minute,60))&":00"``````

3.The column to get the medidan time.

``````Median Of JobLen = var _minute=MEDIANX(FILTER('Jobs',[Task]=EARLIER(Jobs[Task])&&[Who]=EARLIER(Jobs[Who])),[Minute])
return INT(DIVIDE(_minute,60))&":"&INT(MOD(_minute,60))&":00"``````

You can check more details from the attachment.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

New Member

Hello @v-stephen-msft,

Just what I was after, thank you for taking the time to post such a detailed reply.

Hello@OwenAuger,

Thank you also for taking the time to reply, I can see situations where your solution can be used also.

Rest assured, both solutions are now filed away on OneNote for future reference.

Thanks again both for your time,

Beefheart

Super User

There are certainly a few variations on how you could do this.

I would probably convert to hours, so the integer part is hours, then multiply the fraction part by 60 to get minutes.

Something like this (you may want to tweak the formatting in the last line):

``````AvgJobLenTxt =
VAR avgJobLen =
AVERAGE ( Jobs[JobLen] )
VAR HoursDecimal =
avgJobLen * 24
VAR totalHours =
-- truncate decimal
TRUNC ( HoursDecimal )
VAR totalMinutes =
-- round to nearest minute
ROUND ( ( HoursDecimal - totalHours ) * 60, 0 )
RETURN
totalHours & ":" & FORMAT ( totalMinutes, "00" )``````

If you want to return a numerical value but with a number format like this, you would have to resort to a calculation group containing similar code.

Regards,

Owen

Owen Auger

My Blog

Announcements

#### 2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.