Help with Durations/TimSpans

[Beefheart]

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.

[v-stephen-msft · Accepted solution]

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.

[Beefheart]

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

[OwenAuger]

Hi @Beefheart 

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