Forum Discussion

Beefheart's avatar
Beefheart
Helper I
4 years ago
Solved

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.
 
DateTime Math
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      Beefheart
      Helper I
      4 years ago

      Hello Anonymous,

       

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

       

      HelloOwenAuger,

       

      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's avatar
    OwenAuger
    Super User
    4 years ago

    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