Need help removing/ignoring double counting of data.

[davidturk]

I'm trying to calculate the availability of a service as: Total Time / ( Total Time + Outage Time)

I have data like this, where you can possibly have multiple "outages" on a single service at one time:

Outage	Service	Start	End
1	s1	6/7/21 12:00:00	6/7/21 13:00:00
2	s1	6/7/21 12:30:00	6/7/21 13:00:00
3	s1	6/7/21 12:22:00	6/7/21 12:44:00
4	s2	6/7/21 12:00:00	6/7/21 13:00:00

Services s1 and s2 should have the same Outage Time (60 minutes) since both were unavailable from 12:00-13:00. The issue is that in this instance it will calculate service s1 as having 1 hour and 52 minutes of downtime, but in reality, the service was just down for the hour.

How I think I'd like to solve the issue:

I'm thinking of using a measure and the CALENDAR function to create miniature tables for each outage, with a row for each minute the outage is active. Then I can put all the tables together and take the distinct values. For the above data, I would get 60 rows for outage 1, 30 rows for outage 2, and 22 rows for outage 3. The distinct rows would be the 60 rows, and then I could DATEDIFF the earliest and latest entries to get a number of minutes or seconds for my availability calculation above. I'm not sure how to accomplish this in Power BI, any help is appreciated.

[v-kelly-msft · Accepted solution]

Hi @davidturk ,

Create a calculated column as below:

time duration = 
var _mindatetime=CALCULATE(MIN('Table'[Start]),FILTER('Table','Table'[Service]=EARLIER('Table'[Service])))
var _maxdatetime=CALCULATE(MAX('Table'[End]),FILTER('Table','Table'[Service]=EARLIER('Table'[Service])))
Return
DATEDIFF(_mindatetime,_maxdatetime,HOUR)

And you will see

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[davidturk]

This is close, but I see now that my data was insufficient to illustrate the issue. Your calculation is fine if all the times line up perfectly, but what if they don't:

Outage	Service	Group	Start	End
1	s1	g1	6/7/21 12:00:00	6/7/21 13:00:00
2	s1	g1	6/7/21 12:30:00	6/7/21 13:00:00
3	s1	g1	6/7/21 12:22:00	6/7/21 12:44:00
4	s1	g1	6/9/21 13:00:00	6/9/21 14:00:00
5	s2	g1	6/7/21 12:00:00	6/7/21 13:00:00

I believe now, your calculation will show downtime all the way from 6/7/21 12:00:00 through 6/9/21 14:00:00 when 's1' has only been down for two hours total. Further, now let's say we want to call these two services a group. I like to be able to also report availability / non-overlapping total down time of the group as well as each individual service.

[HotChilli]

A few steps in Power Query should get you there.

Add a new column (SubMinutes) :

Duration.TotalMinutes([End] - [Start])

then add another column:

List.DateTimes([Start], [SubMinutes], #duration(0, 0, 1, 0))

that will create a List on each row which you can 'expand to new rows'.

When you test you might want to decide how to treat the boundaries e.g. 12:00-13:00 , is that 59 minutes or 60 for your purposes.

Let me know how you get on.