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

  • Ry009's avatar
    Ry009
    Icon for Helper II rankHelper II
    2 years ago

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