Convert Seconds to DD:HH:MM:SS

[michael_knight]

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 = 
var vSeconds=AVERAGE(Sheet1[Seconds])
var vMinutes=int( vSeconds/60)
var vRemainingSeconds=MOD(vSeconds, 60)
var vHours=INT(vMinutes/60)
var vRemainingMinutes=MOD(vMinutes,60)
var vDays=INT(vHours/24)
var vRemainingHours=MOD(vHours,24)
return
  vDays&" Days & "&
  vRemainingHours&" Hours & "&
  vRemainingMinutes&" Minutes & "& 
  ROUND(vRemainingSeconds,0)& " Seconds"

This is the outcome from the measure

When I try to use a Calculate formula with USERELATIONSHIP, it just straight up doesn't work

Does anyone have alternatives, or ways I can work around this issue?

He's the PBIX: https://www.dropbox.com/s/raye0i6xfvr0h9y/dates.pbix?dl=0

Cheers,

Mike

[lbendlin · Accepted solution]

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]

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]

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)?

[ERD]

Hi @michael_knight ,

You can use this measure if you need an average value:

#d:h:m:s = 
VAR avgNum = AVERAGE(T[Sec])
VAR res = 
INT(avgNum/86400) & ":" &                         //Days
RIGHT("0" & INT(MOD(avgNum/3600,24)),2) & ":" &    //Hours 
RIGHT("0" & INT(MOD(avgNum/60,60)),2) & ":" &       //Minutes
RIGHT("0" & INT(MOD(avgNum,60)),2)                //Seconds
RETURN res

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

[michael_knight]

Hi,

That measure kinda works. My issue is that it is a text datatype so it's no possible to visualise it. 

I'd want to be able to display the average over Months on Months 

[lbendlin]

Remember that datetime values are actually numbers with the integer part being the days and the fraction part being , well the fractions of days.

You can thus take your seconds value, multiply by 86400 and you got your datetime value. 

Alternatively you can use #duration()  and specify only the seconds value.  It will convert to days/hours/minutes/seconds automatically.

[michael_knight]

Hi @lbendlin 

I tried using the duration and I had no luck. Are you able to elaborate on what I can do with the seconds? 

Cheers