Time Zone Conversions not working

Hi All,

Thanks in advance for your help!

I'm working on a report that I want to see when an action is done throughout my company where we have locations in each time zone. Our current system uses UTC.

I've located a measure which works, however once I add a new column the adjusted column goes blank. I tried a different way by store number, however this only gives me the value for if it's not actually changing, but gives me "12/30/1899 3:00:00 AM" for Pacific time as an example. This way would be the most ideal due to some states having locations in 2 different time zones.

Along with this, I can't pull just the time out of the value to allow me to filter if the action is done after a specified time.

Wondering if anyone knows of a way to help get this to work?

Attempt 1 - Hour Conversion, converts the state to the hour behind EST time (works but dissapears):

"New DateTime =
VAR UTCDateTime = SELECTEDVALUE(OPS_PULL_LIST_DATA[FIRST_ACTION_TAKEN_DTTM])
VAR TimeZone = SELECTEDVALUE(OPS_PULL_LIST_DATA[STATE_ABBR])
VAR UTCOffset = SWITCH(TimeZone, "CA", 3, "WA", 3, "NV", 3, "AZ", 2, "NM", 2, "CO", 2, "MN", 2, "WI", 2, "IL", 1, "MO", 1, "KS", 1, "TX", 1, "LA", 1, "MA",0, "NY", 0, "CT", 0, "MD", 0, "NJ", 0, "DE", 0, "NC", 0, "VA", 0, "GA", 0, "SC", 0, "FL", 0, "TN", 0, "KY", 0, "IN", 0, -1)
RETURN
SWITCH(
TRUE(),
UTCOffset > -1, UTCDateTime - TIME(UTCOffset,0,0),
UTCDateTime)"

Attempt 2 - 

Hour Conversion measure is built off of adjusting the store number to the number of hours behind the time zone is from EST:

New DateTime measure (works for only non adjusted times):

Attempted to pull out the Time based off of the New DateTime measure, but use the original data if the New DateTime was blank, but this didn't work:

"Time = IF([New DateTime] <> "", FORMAT([New DateTime],"hh:mm:ss"), FORMAT(SELECTEDVALUE(DateTime), "hh:mm:ss"))"

 

 

What I see with Attempt 1:

Eastern Time and Central Time locations:

 

When a new column is added:

 

What I see with Attempt 2:

Easter Time (not changed in measure):

 

Pacific Time attempted adjustment:

 

Thanks again for your all's help!