WOT - Schedule start, Estimated End, Scheduled Travel start time zone!

[prosenjitsarkar]

Hi,

I am trying to make a report in Power BI where I want to see an Assigned to Engineer Hourly Planned forecast for each day. End goal is I need to find out for how much an FSE is available in a day. Wanted to know in which time zone format SNOW saves these dates. A resource Manager in US handling APAC FSEs will see dates and time based on his/her user (Resource manager USA) profile time Zone and the FSE sees their APAC time zone on the same WOT.

How to make a report considering which time zone ?

As per Power BI - cast ( expected_start at TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time'  AS DATETIME2) as expected_start, 

[SalmatO · Accepted solution]

Hello, can you try below 

1. Pull data as-is (in UTC) from ServiceNow

Make sure you do not auto-convert dates when extracting via API or import—just get them as UTC.

 

2. Use a time zone mapping table

Create a mapping table in Power BI like:

Assigned To (FSE)	Time Zone
John Doe	SE Asia Standard Time
Jane Smith	Eastern Standard Time
...	...

3. Convert UTC to local time in Power BI

Use DateTimeZone.SwitchZone() in Power BI's Power Query (M) or SQL cast like you referenced (for DirectQuery):

sql

CopyEdit

CAST(expected_start AT TIME ZONE 'UTC' AT TIME ZONE 'SE Asia Standard Time' AS DATETIME2) as expected_start_local

Note: SQL support for AT TIME ZONE depends on your data source. If using Power BI with Azure SQL, it works. If importing, use Power Query.

4. Calculate Daily Hours

Group data by:

• Local Date (e.g., Date.From(expected_start_local))

• FSE

• Sum planned_duration or similar field