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.?
v-qiuyu-msft's avatar
v-qiuyu-msft
Icon for Community Support rankCommunity Support
10 years ago

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

mocca3000's avatar
mocca3000
Regular Visitor
9 years ago

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's avatar
    jayavardhan
    New Member
    9 years ago

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