Lookup for the closest date from another table (time zones)

[gp10]

Hi community,

I'm trying to create a measure or column that will change a UTC Date/Time into a local Date/Time depending on the time zone.

I have a Time Zone table with columns like:

Time Zone	Date/Time (UTC)	Offset (hours)
America/New_York	3/13/2022 7:00:00 AM	-4
America/New_York	11/6/2022 6:00:00 AM	-5

And another Table with Events.

Event	Time Zone	Date/Time (UTC)	Offset (hours)	Local Date/Time
1	America/New_York	10/9/2022 12:00:00 PM	-4	10/9/2022 08:00:00 AM
2	America/New_York	11/30/2022 12:00:00 PM	-5	11/30/2022 07:00:00 AM

I want to get the last 2 columns, Offest and Local Date/Time.

The date/time of the Event table needs to match to the closest lowest date/time of the Time Zone table in order to return the correct offset.

Thanks.

[Ashish_Mathur · Accepted solution]

Hi,

Write these calculated column formulas in the Events table

Offset hours = LOOKUPVALUE(Time_zone[Offset (hours)],Time_zone[Date/Time (UTC)],CALCULATE(MAX(Time_zone[Date/Time (UTC)]),FILTER(Time_zone,Time_zone[Time Zone]=EARLIER(Events[Time Zone])&&Time_zone[Date/Time (UTC)]<EARLIER(Events[Date/Time (UTC)]))),[Time Zone],[Time Zone])

Local date/time = =Events[Date/Time (UTC)]+TIME(Events[Offset hours],0,0)

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

Write these calculated column formulas in the Events table

Offset hours = LOOKUPVALUE(Time_zone[Offset (hours)],Time_zone[Date/Time (UTC)],CALCULATE(MAX(Time_zone[Date/Time (UTC)]),FILTER(Time_zone,Time_zone[Time Zone]=EARLIER(Events[Time Zone])&&Time_zone[Date/Time (UTC)]<EARLIER(Events[Date/Time (UTC)]))),[Time Zone],[Time Zone])

Local date/time = =Events[Date/Time (UTC)]+TIME(Events[Offset hours],0,0)

Hope this helps.

[gp10]

Hi @Ashish_Mathur ,
thanks for your help,

I'm getting the following error for the Local/Event time:
"An argument of function 'TIME' has the wrong data type or the result is too large or too small."

Any thoughts on how to solve this?

[gp10 · Accepted solution]

No worries, the issue can be solved by this:

Local Date/Time = Event[Date(UTC)]+Event[Offset Hours]/24