Service Availability Help

[davidturk]

I'd like to report on service availability, reposting my late reply as my thread was closed. see the closed thread here: https://community.powerbi.com/t5/Desktop/Need-help-removing-ignoring-double-counting-of-data/m-p/198...

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 the provided 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.

[v-stephen-msft]

Hi @davidturk ,

Why is there only two hours between 6/7/21 12:00:00 and 6/9/21 14:00:00? , Is your number of days wrong? It’s June 7th instead of June 9th.

What is your expected result? Please show the expected results in the form of a table.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[davidturk]

S1 has 1 hour of total outage between 7/21 12:00 and 13:00. There are three outages in this window, but I want to ignore overlapping times in the results to get a "true" outage total time. It has another 1 hour of outage between 6/9 13:00 and 14:00, for a total of 2 hours of total outage time.

S2 has 1 hour of total outage time between 6/7 12:00 and 13:00.

The measure provided in the original thread to me worked fine before I added the row to my example for 6/9, but aftward it reports that the service is experiencing an outage for almost 2 days. Basically, as long as all the outages are clustered and contiguous in time, it works fine, but not if they're spread into different clusters.

I also want to be able to group them and get the correct, non-overlapping outage total time:

Service	OutageTime (seconds)
s1	7200
s2	3600

Group	OutageTime (seconds)
g1	7200

[v-stephen-msft · Accepted solution]

Hi @davidturk ,

1.Create a new calculated column as follows

Date = [Start].[Date]

2.Then create the following calculated column

Actual Seconds by Service = 
VAR _minStart =
    MINX (
        FILTER (
            'Table',
            [Service] = EARLIER ( [Service] )
                && [Date] = EARLIER ( [Date] )
        ),
        [Start]
    )
VAR _minEnd =
    MAXX ( FILTER ( 'Table', 'Table'[Start] = _minStart ), [End] )
RETURN
    SWITCH (
        TRUE (),
        [Start] = _minStart
            && [End] = _minEnd, DATEDIFF([Start],[End],SECOND),
        [Start] >= _minStart
            && [End] <= _minEnd, 0
    )

Actual Seconds by Group = 
VAR _minStart =
    MINX (
        FILTER ( 'Table', [Date] = EARLIER ( [Date] ) && [Group] = EARLIER ( [Group] ) ),
        [Start]
    )
VAR _minEnd =
    MAXX ( FILTER ( 'Table', [Start] = _minStart ), [End] )
VAR _id =
    MINX ( FILTER ( 'Table', [Start] = _minStart && [End] = _minEnd ), [Outage] )
RETURN
    SWITCH (
        TRUE (),
        [Start] = _minStart
            && [End] = _minEnd
            && [Outage] = _id, DATEDIFF ( [Start], [End], SECOND ),
        [Start] >= _minStart
            && [End] <= _minEnd, 0
    )

3.In the report view, you can create table visuals to view

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.