Convert UTC to local time zone in DAX

[gjadal]

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

[okellyc]

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]

add a new column:

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

adds 10 hours

[jjudas_CPAdv]

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]

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.

[shanj]

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

[mickjaeger]

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 

[Agee]

🤣🤣🤣

[Anonymous]

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]

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]

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

[Virtual_Ames]

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]

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.

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

Best Regards,
Qiuyun Yu

[RichardL]

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

[JEROPS]

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]

@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

[Ry009]

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

[NikNithiy]

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]

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

Cheers!

[maartjedutchy]

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?

[mocca3000]

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?