Forum Discussion

gjadal's avatar
gjadal
Icon for Microsoft Employee rankMicrosoft Employee
10 years ago

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.?
RichardL's avatar
RichardL
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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's avatar
JEROPS
Regular Visitor
9 years ago

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's avatar
    RichardL
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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

    • NikNithiy's avatar
      NikNithiy
      Icon for Helper I rankHelper I
      3 years ago

      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

    • maartjedutchy's avatar
      maartjedutchy
      Frequent Visitor
      9 years ago

      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?

       

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

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

      Cheers!