How convert seconds in format dd.hh:mm:ss?

[aignn]

Hi all!

How convert seconds in format dd.hh:mm:ss?

I have table and matrix with column seconds and i want convert this to format dd.hh:mm:ss

How can i do this?

Thank you advanced

[AlB]

@aignn 

I don't understand what you need. Please give me an example with the number of seconds and the expected result

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

@aignn 

That's because you are aggregating (sum) the seconds column in the visual, while the Duration calculated column has the result for each individual row, not the aggregation. Plus in your file you are showing the Duration column aggregated (Last).  So that will obviously not work.

If you want to do something like that, you need a measure, not a calculated column:

Duration measure =
VAR sectToDays_ = SUM(Table1[Seconds])/60/60/24 
RETURN
FORMAT(FLOOR(sectToDays_,1),"00:") & FORMAT(sectToDays_, "hh:mm:ss")

Place the measure in the visual. And check if you want the Sum aggregation on the seconds column in the visual

See it all at work in the attached file.

By the way it is best to remove the Auto Date time feature. See https://communitypowerbii.comt55/Desktop/How-to-remove-Date-Hierarchytdd-p/647711

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[aignn]

@AlB 

Oh that's what i need thank you!!!

There is another question on the topic.

Can i (if necessary) clean up days in this case?

To get the format HOURS:MINUTES:SECONDS.

Even if it looks like 1234:56:12

[v-yingjl · Accepted solution]

Hi @aignn ,

If you want to get hh:mm:ss format, you can create measure like this:

Measure = 
VAR ss =
    SUM ( 'test'[seconds] )
VAR mm =
     ( ss - MOD ( ss, 60 ) ) / 60
VAR hh =
     ( mm - MOD ( mm, 60 ) ) / 60
VAR mmm =
    mm - MOD ( mm, 60 )
VAR res = ss - hh * 3600
VAR rem =
     ( res - MOD ( res, 60 ) ) / 60
VAR ress = ss - rem * 60 - hh * 3600
RETURN
    hh & ":" & rem & ":" & ress

Attached the sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

This works great. Thanks!

[AlB]

@aignn 

the file shared requires logging into google. Either remove that requirement or share it from another platform (tinyupload.com, etc.)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[aignn]

@AlB 

Sorry! Try this http://s000.tinyupload.com/index.php?file_id=09949671527885370936

[AlB]

Hi @aignn 

Create a new calculated column in  your table. Note the output is type text:

Column = 
VAR sectToDays_ = Table1[Seconds]/60/60/24 
RETURN
FORMAT(FLOOR(sectToDays_,1),"00:") & FORMAT(sectToDays_, "hh:mm:ss")

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[aignn]

Hi @AlB !

I did this, but I have a filter by date and it is incorrectly counted in the duration column 😞

Me demo pbix: https://drive.google.com/file/d/1J4hcF_U4krEHBrxjHIaEX8bdeUVQ6kTJ/view?usp=sharing

[amitchandak]

@aignn , if this a duration then you have function power query

https://docs.microsoft.com/en-us/powerquery-m/duration-seconds

or  in Dax

left([col],2)*24*60*60 , mid([col],4,2) *60*60 + mid([col],7,2) *60 + right([col],2)