Split time value column in DAX

[chavanr]

Hi there,

I have created a new calculated column with "concatenate" function  & need to split the same new column as per the "-" seperator.

I need the time value which is on the right side as my Output, for which I have used RIGHT function but it is not quite useful as the space length is giving a extra letter from the concatenate function for some values if I adjust the length then I miss the one letter (please see below)

 

I was wondering if we anyone has solution to fix this issue.

Many thanks !!

[Anonymous]

Hello @chavanr 
You can try this.

New Time = RIGHT( Recordings[Time], 11)
After calculating change to time.

[AnkitKukreja]

Hi @chavanr 

I hope I got your reuirement right, please try below dax

Time in New2 =

     RIGHT( 'Time Split'[TIme in new] , 10)

If still it doesn't work create another column and use Trim function to remove extra spaces.

Thanks,
Ankit Kukreja
www.linkedin.com/in/ankit-kukreja1904

[chavanr]

Hi @AnkitKukreja 

I am already using the RIGHT function & also tried to use the trim function(to remove spaces) as well but not useful as it is converting the data type 'time' to 'text' & removes the seconds value for the time stamp

[amitchandak]

@chavanr , Try a new column like this in DAX

right([time in new] , len([time in new]) - search("-",[time in new],,0)+1)

[chavanr]

Hi @amitchandak 

Facing a issue with format now, after concatenate, the "Time In New" column is converted to text.

Error message"Cannot convert value '9:32:30 AM-' of type text to type number.

I did use the FORMAT for the "Time In New" column but is still gives in text format

Is there a workaround for this one?

Many thanks!!

[chavanr]

Thanks @amitchandak  will try !!