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 II
Super User II

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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

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

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors