Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
gjadal
Microsoft Employee
Microsoft Employee

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

25 REPLIES 25
okellyc
Advocate I
Advocate I

It's mind blowing how many litttle tiny things that you would expect to be easy and out of the box, require a WAY over engineered DAX expression.  Even just sorting a table how I want requries an hour of reserarch and another 30 min of writing DAX.  

 

Why in the world would PBI Service not be configured to show the local time that is in the report?

Anonymous
Not applicable

add a new column:

AdjustedDateTime = OriginalDateTime + Time(10,0,0)

 

adds 10 hours

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.

Anonymous
Not applicable

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.

This does not appear to be allowed for tables using direct query.

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
Advocate I
Advocate 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
Resident Rockstar
Resident Rockstar

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.

RichardL
Microsoft Employee
Microsoft Employee

@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

Sorry really dumb question here, but how do I format this without the seconds included?

HI Any idea how we change this dynamically, means i should add a slicer and select different time zone and the dax should work according

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?

 

 

 

 

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.