cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
davidturk
New Member

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

2 REPLIES 2
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

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 Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

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.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors