cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
davidturk
Frequent Visitor

Need help removing/ignoring double counting of data.

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:

OutageServiceStartEnd
1s16/7/21 12:00:006/7/21 13:00:00
2s16/7/21 12:30:006/7/21 13:00:00
3s16/7/21 12:22:006/7/21 12:44:00
4s26/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.

1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
Community Support

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

 

v-kelly-msft_0-1623404526292.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

 

View solution in original post

3 REPLIES 3
v-kelly-msft
Community Support
Community Support

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

 

v-kelly-msft_0-1623404526292.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

 

View solution in original post

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:

 

OutageServiceGroupStartEnd
1s1g16/7/21 12:00:006/7/21 13:00:00
2s1g16/7/21 12:30:006/7/21 13:00:00
3s1g16/7/21 12:22:006/7/21 12:44:00
4s1g16/9/21 13:00:006/9/21 14:00:00
5s2g16/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
Super User
Super User

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.

  

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!