Average Time Error

[Anonymous]

Hey guys,

I'm getting a error in the average time which I added with the help of average line option in further analysis. I have a datetime column and I converted it to seconds and then into hh:mm:ss format[DAX below]. When I'm adding Average line for End time of a process over a month it is giving me error in the minutes values eg : 05:73:36. Don't know why is it passing the 60 minutes value. anyone knows this error ? I'm getting correct values for all other lines . Maybe something happening due to the jumps in line?
 

DAX to convert the Datetime column

// Step 1: Convert the datetimestamp "hh:mm:ss into seconds (number format)

Var T2Seconds = MAX('Table'[END_DATE])*86400

// Step 2: We start with a duration in number of seconds

VAR Duration = INT(T2Seconds)

// Step 3: Get date as there are 86,400 seconds in an day

VAR Days = INT ( Duration / 86400)

// Step 4: Get hour as There are 3600 seconds in a hour

VAR Hours = INT ( MOD( Duration - ( Days * 86400 ),86400 ) / 3600)

// Step 5: Get minute as there are 60 seconds in a minute

VAR Minutes = INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60)

// Step 5: Get seconds as remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours

VAR Seconds = ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0) // We round up here to get a whole number

RETURN

// Step 6: Final ouput: We put the hours, minutes and seconds into the proper "place"

Hours * 10000 + Minutes * 100 + Seconds

[amitchandak]

@Anonymous , do you need sum of duration , refer some old solutions

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#M389