Calculate duration of non-overlapping time

[AnujaJ]

I am calculating service availability for a month. For that I have outage start time and outage end time. I want to know out of total time in minutes for a month, how much percentage of service was disrupted. 

E.g Service Availability = Total time - Total_outage_time / Total time. 

However, I am not able to exclude the overlapping outage time. I do not want to calculate the overlapping duration. Can someone help? 

Outages	Start time	End time
Outage 1	2019-11-04 13:04:00	2019-11-05 16:57:34
Outage 2	2019-10-05 11:20:00	2019-10-05 02:18:03
Outage 3	2019-10-20 16:19:00	2019-10-21 02:18:29
Outage 4	2019-10-30 02:29:00	2019-10-31 06:46:56

[OllieClarke · Accepted solution]

Hi @AnujaJ is this what you're after?

This workflow calculates the % of minutes per month where there wasn't any outage.

Hope that helps, if so please could you mark it as a solution to help the community 🙂

Ollie

[AnujaJ]

Thank you Ollie. This works perfect! However, could you also explain the logic you used. I am not able to understand the logic. 

[OllieClarke · Accepted solution]

@AnujaJ Sure!

Firstly I generate every minute between the start time and end time for each outage using the generate rows tool:

I then find the month for each minute of outage.

Using this month I count how many distinct minutes of outage there are in each month.

I then calculate how many minutes there are in each month. To do this I add 1 month to the month, then minus 1 minute, then count the minutes between the month and this value.

Finally I can use your formula to calculate the service availability. 

Does that make things clearer?

Ollie

[AnujaJ]

Wow! Extremely clear now. thank you so much,