Time Zone Conversions not working

ccolletti · ‎03-15-2024

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:

"HourDiff =

VAR Store = SELECTEDVALUE(STORE_NUM)

RETURN

IF(

AND(Store > 1100, Store < 1150), 3,

IF(AND(Store > 1200, Store < 1210), 3,

IF(AND(Store > 1400, Store < 1420), 3,

IF(AND(Store > 1000, Store < 1020), 2,

IF(AND(Store > 1300, Store < 1310), 2,

IF(AND(Store > 1600, Store < 1620), 2,

IF(AND(Store > 1900, Store < 1910), 2,

IF(AND(Store > 2300, Store < 2310), 2,

IF(AND(Store > 500, Store < 550), 1,

IF(AND(Store > 1800, Store < 1810), 1,

IF(AND(Store > 2400, Store < 2510), 1,

IF(AND(Store > 2800, Store < 2810), 1,

IF(AND(Store = 937, Store = 2002), 1, 0)))))))))))))"

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

"New DateTime =

VAR UTCDateTime = SELECTEDVALUE(DateTime)

VAR UTCOffset = [HourDiff]

RETURN

SWITCH(TRUE(),

UTCOffset = 3, UTCDateTime - TIME(3,0,0),

UTCOffset = 2, UTCDateTime - TIME(2,0,0),

UTCOffset = 1, UTCDateTime - TIME(1,0,0), UTCDateTime)"

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!

ccolletti · ‎03-15-2024

Hey @Idrissshatila thanks for the message. Unfortunetly with the data I'm using, I do not have access to edit the dataset. I tried doing this through the Report View and couldn't get it to work.

Do you know of any way to do something similar without updating the dataset? Turnaround time is not quick to get those in charge to do it on their end.

Thanks!