Convert Times from UTC to local at a specific point in time

[BIsteht]

I have read for hours on how to convert UTC to local time. Most (maybe all) of the solutions are for a 'Last Refresh' type datapoint. I need a way to convert a date/time from UTC to local at the record level. That 'stamp' should then never change.

For instace, I have a record with a createdDate of 2/11/18 1:56 AM UTC. This is 2/10/18 7:56 PM localTime. What I need is a way to add a column with a 'stamp' of the local time and for that to stay accurate when DST strikes again. So, the localTime timestamp should not change based on the current offset but on the offset at the time/date that createdDate was set. Any ideas?

[v-shex-msft]

Hi @BIsteht,

>>I need a way to convert a date/time from UTC to local at the record level. That 'stamp' should then never change.

I'd like to create a dim table to store location timezone, you can use current row contents to find out user, then you can use user to find out his location and correspond timezone.

After above steps, you only need to consider with timezone diff and utc datetime to get specific user's local date time.

Regards,

Xiaoxin Sheng

[BIsteht]

v-shex - Thanks for the response. However, I likely wasnt very clear as this is not the scenario at play. A couple point of clarification:

1. I am always converting from UTC to CT. This needs to account for daylight savings.
2. The key is that I need to convert to the correct time on the day of createdOn date/time.

For example:

CreatedOn_UTC: 8/8/2017 8:30PM

CreatedOn_CT: 8/8/2017 3:30PM (context for conversion in this case is CDT (-5) not today (-6)

CreatedOn_UTC: 2/14/2017 8:30PM

CreatedOn_CT: 2/14/2017 2:30PM (context for conversion in this case is CST (-6) not today (-5)

I'm looking for a solution that will keep the DST context correct based on the UTC date. It's not feasible to manually handle this. It looks like maybe scraping a table similar to the one below and then performing calcs and adjustments on UTC date from there?

https://www.timeanddate.com/time/change/usa/st-louis

[v-shex-msft]

Hi @BIsteht,

Unfortunate, current power bi not support these datetime convert features.
In my opinion, I'd like to suggest you to find some third party datatime analysis api as source table.

Regards,

Xiaoxin Sheng