Calculate Uptime (in %) of a System, based on Downtime Events

[EduardoMagalhae]

Hi there

I'm new to this, so I hope I am doing it right! 🙂 

I have a table with Downtime events of my system. It looks like this:

Now I want to disply the Uptime in % based on a time reference, using a filter for dates or fixed period for example:

With a data filter from 08.11.21 and 12.12.21 the Uptime was XX.X %

Or, on a table:

Oct: 21: xx%

Nov 21: xx%

Dec 21: xx% 

Or...

2021: xx%

2022: xx%

Or...

Calendarweek 34: xx%

Calendarweek 35: xx%

Calendarweek 36: xx%

I already have a Table (for other purposes) with Timeslots divided by hour, if this help. It looks like this:

This table is autogenerated with a python script from 01.01.21 up to Today + 5 days. 

I think I have to use DAX for it but I'm not sure how.... 

[lbendlin · Accepted solution]

This was actually a fun challenge - thank you.  See my proposal attached.  You can modify the calendar table as needed.

Edit:  I think the value across towers is incorrect but I am not sure how you want this represented.  If  one of the seven towers is down do the other ones still report 100% uptime?

[lbendlin · Accepted solution]

This formula might be slightly "better":

Uptime% = 
var t = countrows(ALLSELECTED('Table'[tower_id]))
return if ( hasonevalue('Table'[tower_id])
,DIVIDE([DaysinPeriod]*1440-sum('Table'[down_minutes]),[DaysinPeriod]*1440,1)
,DIVIDE([DaysinPeriod]*1440*t-sum('Table'[down_minutes]),[DaysinPeriod]*1440*t,1))

[EduardoMagalhae]

This is really good. The only issue, when it a 100% instead of getting 100% I get 100.76%... I think is a rounding error. HOw'd go about to rounding it down? 

[lbendlin]

I don't see that in the sample data. Please provide sanitized sample data that fully covers your issue. 

[EduardoMagalhae]

DAX, if possible and Minute granularity is perfect. 

[lbendlin · Accepted solution]

This was actually a fun challenge - thank you.  See my proposal attached.  You can modify the calendar table as needed.

Edit:  I think the value across towers is incorrect but I am not sure how you want this represented.  If  one of the seven towers is down do the other ones still report 100% uptime?

[AmberJane]

This was awesome and solved my issue. I worked for a software company and we are creating a report for uptime by SLA categories. I was worried when adding various filters (specific client, product, severity etc etc)  that the uptime % would be incorrect. I used your example provided and it has worked almost entirely across the board. One minor issue I am still trying to figure out but otherwise so helpful!! 

[EduardoMagalhae]

Sorry for the delay. Cought Covid. 😞

This looks really good already, but I am having trouble replicating it in my main table. First I create the Table Dates (and it creates the Date column automatically). When I try to create the Month, Quarter, Week and Year Table it gives me the error:  A single value for column 'Date' in table 'Dates' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Do you know why it would tell me that?

[lbendlin]

My proposed solution already has a Calendar table.  Modify it as needed, don't create a new one.

[EduardoMagalhae]

Just Did above. 

[lbendlin]

You want the solution in Power Query or in DAX?

[EduardoMagalhae]

Here you go! 

Tower is the ID of the facility, or machine that is donw. Start and End is the slot that got mesured. The down time show  how many minute in that slot the machine was down. 

I have to asume, that, if there is nothing on this table, the tower is online. 

tower_id	TimeSlotStart	TimeSlotEnd	down_minutes
5	09/11/2021 10:00:00	09/11/2021 10:59:00	60
5	09/11/2021 11:00:00	09/11/2021 11:59:00	60
5	09/11/2021 12:00:00	09/11/2021 12:59:00	60
5	09/11/2021 13:00:00	09/11/2021 13:59:00	60
5	09/11/2021 15:00:00	09/11/2021 15:59:00	60
6	14/12/2021 11:00:00	14/12/2021 11:59:00	60
1	08/11/2021 17:00:00	08/11/2021 17:59:00	45
1	09/11/2021 02:00:00	09/11/2021 02:59:00	31
2	08/11/2021 17:00:00	08/11/2021 17:59:00	43
2	18/11/2021 23:00:00	18/11/2021 23:59:00	57
2	12/12/2021 09:00:00	12/12/2021 09:59:00	23
2	12/12/2021 12:00:00	12/12/2021 12:59:00	23
2	12/12/2021 18:00:00	12/12/2021 18:59:00	15
2	13/12/2021 04:00:00	13/12/2021 04:59:00	33
5	08/11/2021 17:00:00	08/11/2021 17:59:00	21
5	09/11/2021 14:00:00	09/11/2021 14:59:00	38
3	08/11/2021 17:00:00	08/11/2021 17:59:00	49
3	09/11/2021 23:00:00	09/11/2021 23:59:00	4
3	22/11/2021 15:00:00	22/11/2021 15:59:00	2
3	23/11/2021 02:00:00	23/11/2021 02:59:00	1
3	23/11/2021 16:00:00	23/11/2021 16:59:00	38
3	25/11/2021 23:00:00	25/11/2021 23:59:00	1
7	14/12/2021 11:00:00	14/12/2021 11:59:00	55
7	14/12/2021 13:00:00	14/12/2021 13:59:00	50
6	14/12/2021 10:00:00	14/12/2021 10:59:00	20
6	14/12/2021 12:00:00	14/12/2021 12:59:00	54
6	14/12/2021 12:00:00	14/12/2021 12:59:00	1
6	14/12/2021 13:00:00	14/12/2021 13:59:00	10
6	14/12/2021 13:00:00	14/12/2021 13:59:00	44
4	08/11/2021 17:00:00	08/11/2021 17:59:00	47
4	09/11/2021 23:00:00	09/11/2021 23:59:00	4
4	22/11/2021 16:00:00	22/11/2021 16:59:00	55
4	23/11/2021 02:00:00	23/11/2021 02:59:00	2
4	23/11/2021 15:00:00	23/11/2021 15:59:00	9
4	25/11/2021 23:00:00	25/11/2021 23:59:00	1

[lbendlin]

Please provide sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services.