Cannot convert value '::' of type Text to type Date.

[Anonymous]

Hey

I want to convert a whole number (measured in seconds) to the datatype 'time' like in the table below.

time_diff	duration
0	00:00:00
0	00:00:00
134	00:02:14
0	00:00:00
2	00:00:02

I use the following code for that:

duration =

VAR _hrs = QUOTIENT ( event[time_diff] , 60 )

VAR _mins = INT ( event[time_diff] - _hrs * 60 )

VAR _sec = MOD ( event[time_diff] , 1.0 ) * 60

RETURN

FORMAT(_hrs,"00")&":"&FORMAT(_mins,"00")&":"&FORMAT(_sec,"00")

But I, however, get the following error:
Cannot convert value '::' of type Text to type Date.

What have I done wrong, and is there another way of converting it?

Thank you

[Anonymous · Accepted solution]

Hi @Anonymous ,

You want the duration column to be time type instead of text type, right?

You should modify your calculated column as

duration = 
VAR _hrs = QUOTIENT ( event[time_diff] , 60 )
VAR _mins = INT ( event[time_diff] - _hrs * 60 )
VAR _sec = MOD ( event[time_diff] , 1.0 ) * 60
RETURN
TIME(_hrs,_mins,_sec)

 If the duration column is a Date/Time type after entering it, you can manually change it to the Time type.

TIME function (DAX) - DAX | Microsoft Learn

Best Regards,

Stephen Tao

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

[zzzsharepoint]

it could be that one of the columns used in the FILTER function in the measure, is not formatted as a date data type. Power BI is trying to compare this column with the values from the slicer or today's date, but it is not able to because the data type is not correct.

To resolve this issue, you will need to ensure that the date column used in the FILTER function is formatted as a date data type. You can do this by going to the "Modeling" tab in Power BI Desktop and selecting the date column. From the "Data Type" dropdown menu, select "Date".

Another cause could be that the date columns in the different tables are not in the same format, So you might want to check and make sure that the date columns in all tables are in the same format.

It's also possible that the date column in the source data might be in a format that Power BI is not able to interpret as a date. In that case you can use the power query editor to change the format of the date column to a format that Power BI can interpret as a date.