Is there Excel time format 37:30:55 in Power BI

[MeghnaZutshi]

I have one excel file with one field having time data type ( 37:30:55 format). Once i import that file in PBI, the time format gets lost( it doesn't consider time greater than 24). Can i have the data from excel as is in PBI? Any solution for having 37:30:55 format in PBI.

[v-xiaotang · Accepted solution]

Hi @MeghnaZutshi 

please try this, change the format to duration,

if hh>24, for example, value= 24:30:55, please change it to 1.0.30.55, value= 37:30:55, please change it to 1.13.30.55,

then create the measure

total = 
var _total= CALCULATE(SUM('Table'[duration - Copy]),ALLEXCEPT('Table','Table'[name])) *24*3600
var _hh=INT(_total/3600)
var _mm=int(mod(_total -(_hh *3600),3600)/60)  
var _ss=roundup(mod(mod( _total-(_hh *3600),3600),60),0)   
return _hh&":"&_mm&":"&_ss

result

Solved: Calculate Duration in hh:mm:ss - Microsoft Power BI Community

Best Regards,

Community Support Team _Tang

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

[amitchandak]

@MeghnaZutshi , Import as text, DAX do not support duration, it will convert it to datetime or time

For operations on durations, you can use

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

[MeghnaZutshi]

I need to perform arithmetic operations like Sum or Average so i cant import it as text.