Convert UTC to Users time zone for date column in reports

[Shine_456]

Hi All,

I have query on Time zone isuue.

I have adate column of Created_Date with UTC time. With respective to user I need to convert UTC to their time zone in the report.

(Ex: A client has multiple Users wit different time zones. A User need to see date column as their local time zone. UTC + 5.30 timestamp)

Plz share ur ideas on this and DAX queries for this

Thanks

[ppm1 · Accepted solution]

Here is a measure expression that will dynamically change a UTC datetime to the local datetime. You could use a similar approach in a DAX column, but the user would have to refresh the pbix file for it to recalculate.

DT as Local = var dt = MIN(DateTimes[DateTimeColumn]) // any expression that results in a datetime value

var hrsdiff = DateDiff(UTCNOW(), NOW(), HOUR)

return dt + hrsdiff/24

Pat

[Imrans123]

Assuming you know where the User lives by a country column, I would create another dimension table listing down all countries in one column and their respective difference from UTC zone (in hours) in anoter column. Create a custom column in fact table, use the related value from that dimension table to add/subtract from the UTC time stamp. Name that column Local time or something and use that column where relevant.

It might affect your refresh time since it's data transformation, but because there's no DAX involved, the runtime will be fast.