datetime year to fraction (3) format

[Daven]

Hi,

How do I adjust below measure if UTC is in datetime year to fraction (3) format? Need this as a measure as using directquery and unable to do any transformations to dataset

i.e UTC format:  2021-01-02T00:03:05.1330000

Local Time =

CONVERT (

FORMAT ( SELECTEDVALUE ( 'Table'[UTC] ), "MM/dd/YYYY" ) & " "

& REPLACE (

FORMAT ( SELECTEDVALUE ( 'Table'[UTC] ), "HH:MM:SS" ),

1,

2,

HOUR ( SELECTEDVALUE ( 'Table'[UTC] ) ) - 8

),

DATETIME

)

@v-yingjl

@UTC to Local as measure

[lbendlin · Accepted solution]

Your "UTC"  timestamp isn't UTC. It's missing the Z at the end. Sloppy.

There is no need to do this as a measure.  Use a calculated column

timestamp = DATEVALUE(left(UTC[UTC],10))+TIMEVALUE(mid(UTC[UTC],12,8))

or better yet, do it in Power Query.

let
Source = Table.FromRows({{"2021-01-02T00:03:05.1330000"}},{"UTC"}),
#"Added Custom" = Table.AddColumn(Source, "timestamp", each DateTime.FromText([UTC]))
in
#"Added Custom"