Forum Discussion
Calculate duration
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
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 _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
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-using-dax
https://social.technet.microsoft.com/wiki/contents/articles/33644.powerbi-aggregating-durationtime-in-dax.aspx
https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?GroupId=547&MessageKey=814a2cb4-3cca-4cd1-a620-c467adeaaaf6&CommunityKey=b35c8468-2fd8-4e1a-8429-322c39fe7110&tab=digestviewer
https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389its doent work for me , i dont under stand what im missing here .. would be glad for help
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 _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
