"Cannot convert value '00:00:00:00' of type Text to type Date"

[AZJohnPowerBI]

I have six columns to convert from decimal value of seconds to dd:hh:mm:ss.
I used the following code below to convert four of my columns.

Logged-In Time = 
var Duration = [Avg Logged-In Time In Seconds]
VAR Days =
	INT ( Duration / 86400)
VAR Hours =
    INT ( MOD( Duration - ( Days * 86400),86400) / 3600)
VAR Minutes =
    INT (MOD (MOD( Duration - (Days * 86400 ),86400 ), 3600 ) / 60)
VAR Seconds =
    ROUNDUP(MOD ( MOD( MOD ( Duration - (Days * 86400 ),86400 ), 3600 ), 60), 0) 
VAR D = 
	IF ( LEN ( Days ) = 1,
		CONCATENATE ( "0", Days ),
		CONCATENATE ( "", Days )
	  )
VAR H =
    IF ( LEN ( Hours ) = 1, 
        CONCATENATE ( "0", Hours ),
        CONCATENATE ( "", Hours )
      )
VAR M =
    IF (
        LEN ( Minutes ) = 1,
        CONCATENATE ( "0", Minutes ),
        CONCATENATE ( "", Minutes )
    )
VAR S =
    IF (
        LEN ( Seconds ) = 1,
        CONCATENATE ( "0", Seconds ),
        CONCATENATE ( "", Seconds )
    )
RETURN
    CONCATENATE (
        D,
        CONCATENATE ( ":", CONCATENATE ( H, CONCATENATE( ":", CONCATENATE (M, CONCATENATE ( ":", S ) ) ) )
    ))

The code worked, however I could not use the data as additives. I need the totals, the sums, and averages.
Furthermore,
Columns 5 and 6 however did not convert from Text to Time and I do not know why.

All six columns I converted are using a decimal format.
The only difference that I can see is that the numbers in the columns 5(Not Ready Time) &6(Logged-In Time) are much bigger.

Any advice would be greatly appreciated.
Goals: I need columns 5&6 converted to Time
I need to be able to use the six columns for their totals, averages and sums.

[PaulDBrown]

"I need to be able to use the six columns for their totals, averages and sums".
You are going to need to apply the formatting you need to the actual measures used in the visuals (the measures are calculated on the numerical fields), and visuals which display values (Clustered column charts, bar charts...) will not work with text fields as values. 

[AZJohnPowerBI]

You're right Paul, that is precisely what I need help doing.
I don't have visuals yet because I need time-formatted data.
Is there a way to format the text values into duration format? Did you see my code?

[PaulDBrown · Accepted solution]

OK, so no need to create the calculated columns using the formatting code. Instead, apply your code to a SUM measure (Or any other aggregation measure) for example:

Then create the measures

Sum Duration measure = SUM('Table'[Duration in seconds])

and using your code...

Formatted measure = 
var Duration = [Sum Duration measure]
VAR Days =
	INT ( Duration / 86400)
VAR Hours =
    INT ( MOD( Duration - ( Days * 86400),86400) / 3600)
VAR Minutes =
    INT (MOD (MOD( Duration - (Days * 86400 ),86400 ), 3600 ) / 60)
VAR Seconds =
    ROUNDUP(MOD ( MOD( MOD ( Duration - (Days * 86400 ),86400 ), 3600 ), 60), 0) 
VAR D = 
	IF ( LEN ( Days ) = 1,
		CONCATENATE ( "0", Days ),
		CONCATENATE ( "", Days )
	  )
VAR H =
    IF ( LEN ( Hours ) = 1, 
        CONCATENATE ( "0", Hours ),
        CONCATENATE ( "", Hours )
      )
VAR M =
    IF (
        LEN ( Minutes ) = 1,
        CONCATENATE ( "0", Minutes ),
        CONCATENATE ( "", Minutes )
    )
VAR S =
    IF (
        LEN ( Seconds ) = 1,
        CONCATENATE ( "0", Seconds ),
        CONCATENATE ( "", Seconds )
    )
RETURN
    CONCATENATE (
        D,
        CONCATENATE ( ":", CONCATENATE ( H, CONCATENATE( ":", CONCATENATE (M, CONCATENATE ( ":", S ) ) ) )
    ))

you can then...

Sample PBIX attached

[jgeddes]

The bit of quick testing I did seems to indicate the issue is BI does not know how to convert the day portion of your text string (DD:hh:nn:ss) to a datetime format.
For a work around can you use measures as opposed to calculated columns and then use the text formatted measures in your visuals?

An example based on your data

Average Logged In Time =

var _average=

AVERAGEX('Table (2)','Table (2)'[avg logged in time seconds])

var _dayPortion =

INT(DIVIDE(_average,86400,0))

var _hourPortion =

format(CONVERT(_average / 86400,DATETIME),"hh")

var _minutePortion =

format(CONVERT(_average / 86400,DATETIME),"nn")

var _SecondPortion =

format(CONVERT(_average / 86400,DATETIME),"ss")

return

_dayPortion & ":" & format(CONVERT(_average / 86400,DATETIME),"hh:nn:ss")

would give an average logged in time of

[AZJohnPowerBI]

So I put in the code you provided above as a measure, however it is giving me the message
"MdxScript(Model)(46, 23) Calculation error in measure 'FACT AGENT'[Average Logged In Time]: The Function AVERAGEX cannot work with values of type String."

[jgeddes]

Yep.
I assumed that the average logged time in seconds was (or could be) formatted as a number. At that point all the math could be done on the seconds (formatted as numbers) then converted to the text format that was desired.