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: Who Task Average of JobLen Median Of JobLen Bill A 39:12 40:12...
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.

     

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