Conversion UTC to local time date

[ElizabethTachji]

Hi,

I am not able to convert UTC time to local time in a dataflow.  I can convert UTC to local time in PBIX file.  Has anybody solved this problem.  I am beginning to think the using local in the dataflow does not work.

For example first table the code was created in the PBIX file, not the time difference in the Created Date UTC and Created Date

Whereas in the second table the code was created in the dataflow, Note there is no time difference in the UTC and Aust time.

 Conversion in PBIX

Thanks in advance.

Elizabeth

[v-alq-msft]

Hi, @ElizabethTachji 

Power BI is a cloud service, and that means Power BI files are hosted somewhere. Some DAX functions such as Date/Time functions work on system date/time on the server their file is hosted on. So If you use DAX functions such as TODAY() or NOW() you will not get your local date/time, You will fetch server’s date/time.

You may modify the datetime by DAX or Power Query as below.

DAX:

LocalDatetime = Table[UTC Datetime]-7/24

Power Query:

Custom column = DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),-7,0)

For further information, you refer the following links.

Solving DAX Time Zone Issue in Power BI

Daylight Saving Time And Time Zones In M

Best Regards

Allan

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

[frano72]

Hi, i have another table the stores the UTCOffsetMin for each day.  There are smarter ways of doing this - there are web API's you can subscribe to which claim to be across the daylight saving of each region of the world....

  #"Added LoadTimeLocal" = Table.AddColumn(#"Removed columns", "LoadTimeLocal", each [LoadTimeUTC] + #duration(0, 0, [UTCOffsetMin], 0)),

  #"Transform LoadTimeLocal" = Table.TransformColumnTypes(#"Added LoadTimeLocal", {{"LoadTimeLocal", type datetime}}),

(you might be able to do the column typing in the one statement)

[ElizabethTachji]

After testing many options all day, I ended up creating a custom column and adding 10 hours to the UTC.  The 10 hours is the time different.  Basically a manual calculation instead of relying on Power BI Dataflow to do the converstion.  I ignored daylights saving.  I hour difference is Immaterial in this instance.  

[frano72]

yep - thats pretty much what mine is.  in most cases the utcoffset in mins is 600