cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Beefheart
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:

WhoTaskAverage of JobLenMedian Of JobLen
BillA39:1240:12
BillB37:3737:49
JohnA30:2030:05
JohnB26:4226:39

 

My table, called Jobs looks like this:

WhoTaskStartEnd
BillA15/09/2021 12:5417/09/2021 00:57
BillA20/09/2021 01:2621/09/2021 17:39
BillA11/10/2021 00:2512/10/2021 17:48
BillB11/09/2021 20:5613/09/2021 09:25
BillB29/08/2021 19:4931/08/2021 09:37
BillB29/08/2021 19:4631/08/2021 09:37
BillB11/10/2021 03:2312/10/2021 17:47
JohnA04/10/2021 21:0406/10/2021 01:39
JohnA13/09/2021 11:4114/09/2021 16:39
JohnA11/09/2021 18:3313/09/2021 01:44
JohnA30/09/2021 01:2601/10/2021 10:05
JohnB02/08/2021 09:4903/08/2021 12:19
JohnB30/08/2021 09:3331/08/2021 12:12
JohnB30/09/2021 11:3001/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
v-stephen-msft
Community Support
Community Support

Hi @Beefheart ,

 

Here's my solution.

1.Create a calculated column to calculate the minutes.

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

vstephenmsft_0-1638168389625.png

 

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"

vstephenmsft_1-1638168454674.png

 

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"

vstephenmsft_2-1638168892871.png

vstephenmsft_3-1638168908149.png

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.

 

View solution in original post

3 REPLIES 3
v-stephen-msft
Community Support
Community Support

Hi @Beefheart ,

 

Here's my solution.

1.Create a calculated column to calculate the minutes.

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

vstephenmsft_0-1638168389625.png

 

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"

vstephenmsft_1-1638168454674.png

 

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"

vstephenmsft_2-1638168892871.png

vstephenmsft_3-1638168908149.png

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.

 

View solution in original post

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
Super User
Super User

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


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

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.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors