cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gjadal
Microsoft
Microsoft

Convert UTC to local time zone in DAX

I have a column that contains datetime in UTC.

On my report, I am getting the max of it. I want to convert this UTC date to local time zone in DAX.

Any pointers.?

18 REPLIES 18
sreenathv
Solution Sage
Solution Sage

Why you are insisting to do the conversion in DAX? Instead, an efficient solution will be to do it while importing the data using the function DateTimeZone.SwitchZone

 

https://docs.microsoft.com/en-us/powerquery-m/datetimezone-switchzone

 

Even if you are not familiar with Power Query, the actual power query will be generated in the background, you can do this in the UI itself.

Anonymous
Not applicable

This code should be modified as follows to avoid errors:  Column 4 = FORMAT(Table1[UTC],"MM/dd/YYYY") & "  " & REPLACE(FORMAT(Table1[UTC],"HH:mm:ss"),1,2,IF(HOUR(Table1[UTC])<8,HOUR(Table1[UTC])+4,HOUR(Table1[UTC])-8)

datamodel
Helper I
Helper I

Can we all vote on this one! I would give a 1000 votes if I could 🙂

 

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/8628484-support-for-visualization...

 

Burningsuit
Resolver III
Resolver III

Ken Puls has addressed this problem in his blog. It's good reading for anyone who wants to get a "last refreshed" date and time onto a dashboard....

http://www.excelguru.ca/blog/2016/06/08/display-last-refreshed-date-in-power-bi/

 

hope this helps

 

Stuart

Stuart,

 

Yep, that is the exact issue well defined.

 

I'm looking to solve this in a single measure/widget.  His approach appears to be a good way to start, but he ends with:

"Probably the most frustrating thing to me is that I was trying to find a solution that will show it correctly in BOTH Power BI Desktop and Power BI Online.  There’s nothing worse than working with a report you know is broken depending on where you look at it."

 

I'm looking at variations that may provide the solution.... better yet, someone has this solved and can post the best approach.

 

Much appreciated.

 

Brian

 

v-qiuyu-msft
Community Support
Community Support

Hi @gjadal,

 

In DAX, there is no OOTB function for us to get local time zone. So we need to calculate the UTC datetime value use known time zone. Suppose the local time zone is UTC-8, to convert the UTC value to local datetime value, we can specify the DAX expression like below:

 

Column 4 = FORMAT(Table1[UTC],"MM/dd/YYYY") & "  " & REPLACE(FORMAT(Table1[UTC],"HH:mm:ss"),1,2,HOUR(Table1[UTC])-8)

 

Then change this calculated column data type as Date/Time.

 

a2.PNG

 

If you have any question, please feel free to ask.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-qiuyu-msft - I tried your DAX equation:

 

Column 4 = FORMAT(Table1[UTC],"MM/dd/YYYY") & "  " & REPLACE(FORMAT(Table1[UTC],"HH:mm:ss"),1,2,HOUR(Table1[UTC])-8)

 

but I got this error:  "Cannot covert value '12/07/2016 -8:49:41' of type Text to type Date."

 

It appears your equation fails to handle edge cases.

What is the data type of your column.  From the error it reads like you trying to convert a text column, convert to a date/time column first then try the DAX formula.

@JEROPS - Coverting to date/time column first works, but the equation fails to correct the date after subtracting 8 hours.  I came up with this simpler equation, which works by subtracting 8 hours from the date/time column and automatically adjusts the date if necessary: 

 

DateTimeLocal = FORMAT(Table[DateTimeUTC] - TIME(8,0,0), "General Date")

 

For example, 12/16/2016 1:09:23 AM -> 12/15/2016 5:09:23 PM

Thanks that works and it's simplier. 

Anonymous
Not applicable

Thank you, Much better and simpler than my DAX calender with PST.

Cheers!

This is perfect Richard. Saved me today.

I was just wondering if you know how I add this as a custom column in query editor?

 

 

Thank you,

I get an error stating that it cannot convert value when I try to convert my UTC time to AEST (+{10)

 

My DAX is:

SeenTimeAEST = FORMAT(Meraki_Observation[SeenTime],"MM/dd/YYYY") & "  " & REPLACE(FORMAT(Meraki_Observation[SeenTime],"HH:mm:ss"),1,2,HOUR(Meraki_Observation[SeenTime])+10)

 

My Error is:

Cannot convert value '09/22/2016 24:02:49' of type Text to type Date.

 

Does anyone know how to fix this?

 

 

 

 

 

As of my analysis your column is in data type: text, change it to data type: date and time.

Two questions:

 

1) This approach does not account for daylight savings time (in the US), does it?

 

2) If #1 above is true, then wouldn't it be easier to simply subtract (8/24) from the date/time column?

Hi Qiuyun,

 

Thanks for this post - could solve a problem I'd like to address.

 

I would like to show the "Last Refreshed DateTime" in my reports. I want this to provide the time in PST.  The report is currently either refreshed via the Desktop (local to PST), or Online.

 

If I use NOW() in my report, I will get different times depending on whether where the refresh takes place.  If via the desktop - NOW() is PST.  If I use the schedule refresh, NOW() comes back in UTC time. M code using DateTime.LocalNow() has the expected same effect.

 

Is there M code to generate UTC time, then I can use your formula above to show the PST refresh time in the report?

 

Thanks!

 

Brian

 

I have the same situation here... How to guarantee that the refresh time is the same among power BI desktop, app.powerbi or published web? Should I use other formula than NOW()?

Hi @Virtual_Ames,

 

Please try to set the locale both in Power BI Desktop and Service. See: Supported languages and countries/regions for Power BI.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.