Calculate duration

[raviv]

HI 

im struggeling in calculating duration , i have an excel file and i need to calculate more than 24h and show let say the average time to task , in HH:MM.

i need at the show in a chart the average duration for task .

how im doing it ? 

thanks Raviv

[v-yanjiang-msft · Accepted solution]

Hi @raviv ,

According to your description, here's my solution.

Create a calculated column.

Column =
VAR _P =
    FIND ( ":", 'Table'[time it took] )
VAR _H =
    MID ( 'Table'[time it took], 1, _P - 1 )
VAR _M =
    MID ( 'Table'[time it took], _P + 1, LEN ( 'Table'[time it took] ) - _P )
RETURN
    INT ( DIVIDE ( _H, 24 ) ) & "."
        & MOD ( _H, 24 ) & ":" & _M & ":" & _M & ":" & "00"

Get the result.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[raviv]

its doent work for me , i dont under stand what im missing here .. would be glad for help

[amitchandak]

@raviv , First you need have duration/ diff in second, do aggregation and then convert back to duration

example

Quotient([Total duration ],86400) & ":" Quotient(mod([Total duration ],86400),3600) &":" & Quotient(mod(mod([Total duration ],86400),3600),60) &":" & mod(mod(mod([Total duration ],86400),3600),60)

refer if needed

Duration
https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-usi...
https://social.technet.microsoft.com/wiki/contents/articles/33644.powerbi-aggregating-durationtime-i...
https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?GroupId=547&MessageK...
https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389