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.?

19 REPLIES 19
jjudas_CPAdv
Frequent Visitor

Ran across this sort of issue in a project I'm working and in scanning the several response ideas, why not just add a "UTC adjust" column to the Date Table in your model and populate it accordingly for your timezone and or daylight savings impacts ( e.g. 4/24 for standard and 5/24 for DST)?

 

Then reference that column value in any calcs as needed.

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.

Great idea!  Too bad Microsoft's documentation is so lame.  Good luck reading this help page and figuring out what you're supposed to do to make this work.  

for example doing my best to decipher the MS help doc: 

DateTimeZone.SwitchZone(#datetimezone(2021, 09, 16, 00, 01, 15),-7)

Fail ... syntax error 

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
Solution Supplier
Solution Supplier

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

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 Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors