Availability - how to join Calendar table to operating/online hours table?

[moon_walker]

Hello, I am trying to calculate the availability/uptime of applications. But each application has its own online hours.

Essentially, an application in our company does not have to be online 24/7, and an outage is only recorded if it occurred during the app's online hours.

My Online Hours file has the weekly schedule for each app, according to the day of the week.

e.g.

Schedule	Day of Week Num	Day of Week	Start Time	End Time	Online Minutes
App A Schedule	1	Mon	6:00am	12:00am	1080
App A Schedule	2	Tue	6:00am	12:00am	1080
App A Schedule	3	Wed	6:00am	12:00am	1080
App A Schedule	4	Thu	2:00am	4:00am	120
App A Schedule	4	Thu	6:00am	12:00am	1080
App A Schedule	5	Fri	6:00am	12:00am	1080
App A Schedule	6	Sat	8:00am	12:00pm	240
App A Schedule	7	Sun	8:00am	12:00pm	240
App B Schedule	1	Mon	12:00am	12:00am	1440
App B Schedule	2	Tue	12:00am	12:00am	1440
App B Schedule	3	Wed	12:00am	12:00am	1440
App B Schedule	4	Thu	12:00am	12:00am	1440
...	...	...	...	...	...

In the above table, notice how Application A is not expected to be online all the time. And, there are two entries for Thursday; it needs to be available 2am-4am, as well as 6am-midnight like the other days. In contrast, Application B is expected to be online all the time.

I have a Calendar table using CALENDARAUTO(10), with various columns.

Calendar = ADDCOLUMNS(

CALENDARAUTO(10),

"Year", Year([Date]),

"MonthNo", MONTH([Date]),

"Month", FORMAT([Date], "MMM"),

"DayOfWeekNo", WEEKDAY([Date], 2),

"DayOfWeek", FORMAT([Date],"DDD"),

"Day", DAY([Date]),

"MMM-YYYY", FORMAT([Date],"MMM-YYYY")

)

I would like to create time series reports on the individual applications and their availability. I'm thinking that these two tables have to be merged together? But I'm not sure.

I have a third table that has the outages.

If these two tables have to be joined, for any given date in the calendar, the table will have the summed online hours for App A, App B, App C, and so on. 

e.g. for Thursday March 3rd 2022, App A online hours are 1200 minutes (120 + 1080), App B online hours are 1440 minutes.

e.g.

Date	Schedule	Day of Week Num	Day of Week	Online Minutes
Feb 28 2022	App A Schedule	1	Mon	1080
Feb 28 2022	App B Schedule	1	Mon	1440
Mar 1 2022	App A Schedule	2	Tue	1080
Mar 1 2022	App B Schedule	2	Tue	1440
Mar 2 2022	App A Schedule	3	Wed	1080
Mar 2 2022	App B Schedule	3	Wed	1440
Mar 3 2022	App A Schedule	4	Thu	1200
Mar 3 2022	App B Schedule	4	Thu	1440
Mar 4 2022	App A Schedule	5	Fri	1080
Mar 4 2022	App B Schedule	5	Fri	1440
...	...	...	...	...

Is something like this possible? Is this even the right approach? Or should I keep the tables separate to calculate the availability?

Thank you for all your help!

[jdbuchanan71 · Accepted solution]

@moon_walker 

I don't think you can join the schedule to the calendar but we can use TREATAS to move the day of the week filter to get what I think you are looking for.

I have attached my sample file for you to look at.

[moon_walker]

Wow! This does appear to work correclty. Thank you so much! Had no idea about TREATAS.

[littlemojopuppy]

Hi @moon_walker here's a link to an article about how to use date and time tables.  Maybe this'll help 🙂

[moon_walker]

Thank you! This looks pretty interesting, gonna have to study it more. I think it might come in handy.