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.

 

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
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!