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.
Solved! Go to Solution.
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.
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.
@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
I need to perform arithmetic operations like Sum or Average so i cant import it as text.