Build a table for tracking time availability with time zones

[LMSReportsHelp]

I need to build data from the ground-up on this project.

I need to track 30-50 people for 6-7 weeks on availability.  I am tracking globally, so for each person I will be setting up a specific table linking "person + time zone" but the table I'm not sure how to build is the days' availability table. 

The things I want to use PBI to do are:

• Confirm I am not scheduling project tasks when an employee is unavailable (this is where time zones matter because if I'm scheduling something 8am-12pm EST, that's 12pm-pm GMT, and 1pm-5pm CET). But I want PBI to tell me if I schedule someone in HKT(Hong Kong) for a 8am EST event (give me an error). Time zones MATTER.
• Shifts are also important because we are dealing with people working adjustable shifts (so Week 1 they may work 1pm-9pm but Week 2 they work 4pm-1am). And see above Time zone concern again.

My instinct (coming in as an Excel-user-learning PBI) is the following. But I see SO MANY PROBLEMS with this. I KNOW this won't work in PBI but I just can't wrap my brain around how to solve for EVERYTHING.

	May 1, 2025	May 2, 2025	May 3, 2025
Employee Name 1	9am-5pm	9am-12pm	1pm-5pm
Employee Name 2	12pm-8pm		2pm-6p, 8pm-10pm

I have no idea how to make entering their time at-all-managable. Either I build columns for every hour of every day (and end up with like 1,200 columns) and make a "yes/no" availability (and I am managing that by hand mind you!!!!) or I can't manage partial days. Example Employee 2 above where they have a meeting in the dead-middle of the day so I can't schedule my project needs during that time!

Basically, I feel like I have no idea how to build this table. This isn't a forever-project, but I really don't want to have to manage thousands of columns/rows/data by hand if I don't have to.  I feel like there must be a smarter way to do this. 

[v-menakakota]

Hi @LMSReportsHelp ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-menakakota]

Hi @LMSReportsHelp  ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-menakakota]

Hi @lucie_rabochova ,

I hope the provided information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you

[Anonymous · Accepted solution]

Hi ,

Based on the information, try creating the data table by entering data manually and using power query editor to add custom column. Then, try using conditional formatting to filter time.

= Table.AddColumn(#"PreStep", "Time(UTC)", each DateTimeZone.SwitchZone([Time(Local)], 0))

Best Regards,

Wisdom Wu