cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Working hours with UTC times

Hello experts!

I have two tables:
Table 1 has all the users list, which includes the time zone where each user lives.  Table 2 has a completion date for each user. Many completions per user, in UTC format.   Tables are linked one-to-many through the UserID.

Table 1 (one):

 UserID User Name Time Zone 1 John Doe EST 2 Jane Doe CST 3 Jack Doe PST

Table 2 (many):

 UserID Completed Date 1 5/9/2023 3:30:11 AM 2 6/13/2023 8:15:25 PM 3 8/12/2022 7:08:10 PM

I need to calculate, based on the user timezones, which completed dates happened after working hours for each user, consdering the end of the day at 6:00 pm.

Examples:

- UserID 2, lives in CST time (UTC -6). The user completed the task on 6/13/2023 at 8:15 PM (UTC),  which means the user completed the task at 2:15 pm (CST), which falls in working hours for the user.

- UserID 1, lives in PST time (UTC -8). The user completed the task on 5/9/2023 at 3:30 AM (UTC),  which means the user completed the task at 7:30 PM (PST of the previous day), which falls out of working hours for the user.

My challenge is to calculate which completed dates fall out of working hours.

1 ACCEPTED SOLUTION
Community Support

Hi @rgalvez

1.You can create a new table(TimeZone) as follows:

2.Then create a one-many relationship between timezone table and table 1

3.Then create a calculated column in table 2

``````IfOvertime =
VAR a =
RELATED ( 'TimeZone'[Timedifference] )
VAR b =
CONVERT (
FORMAT ( 'Table 2'[Completed Date] - TIME ( a, 0, 0 ), "General Date" ),
DATETIME
)
VAR c =
CONVERT ( DATEVALUE ( b ) & " " & TIME ( 18, 0, 0 ), DATETIME )
RETURN
IF ( b > c, "Yes", "No" )
``````

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 REPLIES 2
Frequent Visitor

Hi Yolo!  This worked like a charm.  Thank you so much!

Community Support

Hi @rgalvez

1.You can create a new table(TimeZone) as follows:

2.Then create a one-many relationship between timezone table and table 1

3.Then create a calculated column in table 2

``````IfOvertime =
VAR a =
RELATED ( 'TimeZone'[Timedifference] )
VAR b =
CONVERT (
FORMAT ( 'Table 2'[Completed Date] - TIME ( a, 0, 0 ), "General Date" ),
DATETIME
)
VAR c =
CONVERT ( DATEVALUE ( b ) & " " & TIME ( 18, 0, 0 ), DATETIME )
RETURN
IF ( b > c, "Yes", "No" )
``````

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors