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.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

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.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors