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

[sreenathv]

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]

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

[diego_Sancho]

Thanks that works and it's simplier. 

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

[jayavardhan]

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

[dkay84_PowerBI]

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?

[Virtual_Ames]

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

[eserapicos]

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()?

[v-qiuyu-msft]

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