Forum Discussion

michael_knight's avatar
michael_knight
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

Convert Seconds to DD:HH:MM:SS

Hi,   I'm trying to convert Seconds to DD:HH:MM:SS. I used this article to get close, however this doesn't work with both date slicers (Look at the average for the month, for example) New Time = ...
Need Help
  • lbendlin's avatar
    lbendlin
    5 years ago

    You can use measures to coax the data into this (admittedly weird) format.

     

    d = FORMAT(int(sum('Duration'[Duration])),"0") & FORMAT(sum('Duration'[Duration]),":HH:NN")
     
    Are you sure you want to do that?
     

    but no, you can't have both.

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
5 years ago

Here is an eample using #duration

 

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJQ0lEyNAABpVidaCUjJFEjuKgxkqghXNQEIWpqDhaMBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Seconds = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type date}, {"Seconds", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Duration", each #duration(0,0,0,[Seconds])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Duration", type duration}})
in
    #"Changed Type1"

 

which you can then plot as needed

 

  • michael_knight's avatar
    michael_knight
    Icon for Post Prodigy rankPost Prodigy
    5 years ago

    That's very promising, my only query is that the I have no way of knowing what 1.16 is in Duration terms. Do you know if there anyway of coverting it into DD:HH:MM while still keeping it in a datatype which allows for visualisation (Not Text)?