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

Availability ratio of applications

Hello,

I have an issue trying to compute the availability ratio of different applications.
The basic equation for the availability ratio of an application is the following : 
(selected period - app downtime)/selected period.

Here are the tables I have : 

The first is the alerts table, with the periods when the applications were down.

AlertApplicationGroupStart dateEnd dateVille
AA2BG210/02/2021 09:3510/02/2021 17:31ROME
AA3CG314/01/2021 09:5214/01/2021 23:34BEIJING
AA4DG214/03/2021 08:0014/03/2021 22:00PARIS
AA5EG117/03/2021 09:1401/04/2021 10:12ROME


The second is a table with the opening hours of the applications (most of which are turned down during the night - so the parts of the alerts happening during the night should be ignored) : 

ApplicationMonday MorningMonday EveningTuesday MorningTuesday eveningWednesday morningWednesday eveningThursday morningThursday EveningFriday morningFriday eveningSaturday MorningSaturday eveningSunday morningSunday evening
A0:0023:590:0023:590:0023:590:0023:590:0023:590:0023:590:0023:59
B9:0021:009:3015:009:0021:009:3015:009:0021:009:3015:009:0021:00
C9:0018:009:3015:009:0018:009:3015:009:0018:009:3015:009:0012:00
D9:0022:009:3015:009:0022:009:3015:009:0022:009:3015:009:0012:00
E9:0018:009:3015:009:0018:009:3015:009:0018:009:3015:009:0012:00
F10:0017:009:3015:0010:0017:009:3015:0010:0017:009:3015:0010:0012:00


The two other tables are a table with a location hierarchy (City-Country-Region) and a date table.
The idea would be that the report user can select a date range and it would compute the average availability ratio for the selected period on all the apps & app groups & geography perimeters selected.

So the idea that depending on the date range selected in the date table :
- The measure would check the alerts table and the application opening hours to select only the appropriate period of downtime for every alert (without the closing hours and without the part out of the selected period)

- Add all of these downtimes together to have the complete downtime for a selected app

- Use this downtime to calculate the availability ratio for this app, and then be able to give the average value if multiple apps are selected, or if we select one geographical perimeter or one app group in particular...

It's a tricky one ! I have uploaded the .pbix here if you want to have a look.
I would be really grateful for any help you could offer ! 

Best regards, 

R.

1 ACCEPTED SOLUTION
stevedep
Super User I
Super User I

Hi,

I have a solution for you although some minor tweaking is needed (will be explained later on). The result looks like this:

stevedep_0-1616399353663.png

So per application in the selected period (or region) the availability percentage is calculated (p.s. I gave A the close times of B for easier testing). 

 

Some steps were required:

1:  generate close hours for every week in the year (2021). 

a: Transform your table (basically unpivot)

b: Generate the close dates for the years using DAX

2:  add measures to calculate the nett downtime time (down time that is within the open hours / outside of the close hours. 

 

Here we go:

1a: transform data:

stevedep_1-1616399568125.png

Please note: I gave A the close hours of  B.

1b: Generate the dates:

CloseHoursPerYear = VAR _tblclosehours = CloseHours
   // FILTER ( CloseHours, [Application] = "B" )
VAR _tblclosehourswithddayvar =
    ADDCOLUMNS ( _tblclosehours, "dayvar", [D] - 1 )
VAR _tblweeks =
    //weeks
    ADDCOLUMNS (
        GENERATE (
            GENERATESERIES ( 0, 51, 1 ),
            //weeks
            ROW ( "StartDateTime", DATEVALUE ( "01/04/2021" ) )
        ),
        "StartDate",
            [StartDateTime] + ( [Value] * 7 )
    )
VAR _tblweekdays =
    ADDCOLUMNS (
        GENERATE ( _tblweeks, _tblclosehourswithddayvar ),
        "StartDateInWeek", [StartDate] + [dayvar]
    )
RETURN
    SELECTCOLUMNS (
        ADDCOLUMNS (
            GENERATE (
                SELECTCOLUMNS ( GENERATESERIES ( 1, 2, 1 ), "DayPart", [Value] ),
                _tblweekdays
            ),
            "StartTime",
                IF ( [DayPart] = 1, [StartDateInWeek], [StartDateInWeek] + [1] ),
            "EndTime",
                IF ( [DayPart] = 1, [StartDateInWeek] + [0], [StartDateInWeek] + 1 )
        ),
        "Application", [Application],
        "StartClose", [StartTime],
        "EndClose", [EndTime]
    )

 

2: Add the measures:

Minutes in downtime period (gross down time):

MinutesInPeriod = VALUE( SELECTEDVALUE(Alerts[End date]) - SELECTEDVALUE(Alerts[Start date])) * 24 * 60

Minutes in closing period for the alert:

CloseMinutesInPeriod = 
         VAR _selSD =
            SELECTEDVALUE ( Alerts[Start date] )
        VAR _selED =
            SELECTEDVALUE ( Alerts[End date] )
        VAR _selapp =
            SELECTEDVALUE ( Alerts[Application] )
        RETURN
            SUMX (
                ADDCOLUMNS (
                    ADDCOLUMNS (
                        FILTER (
                            CloseHoursPerYear,
                            [Application] = _selapp
                                && [EndClose] > _selSD
                                && [StartClose] < _selED
                        ),
                        "NewSC", IF ( _selSD > [StartClose], _selSD, [StartClose] ),
                        "NewEC", IF ( _selED < [EndClose], _selED, [EndClose] )
                    ),
                    "duration",
                        VALUE ( [NewEC] - [NewSC] ) * 24 * 60
                ),
                [duration] //  "SC: " & [NewSC] & " EC: " & [NewEC] & " duration : " & [duration] & "
                //  "
            )

Please note this is only covering the below scenario:

stevedep_2-1616399873350.png

Perhaps some exceptions need to be added. Please do some testing to verify..

Net downtime per app:

NettDownTime = SUMX(SUMMARIZE(Alerts,Alerts[Application], Alerts[Alert], Alerts[Start date], Alerts[End date]), ([MinutesInPeriod]-[CloseMinutesInPeriod]))

Per application calc the close mintutes per app in the selected period: 

CloseMinutesInDateRange = 
         VAR _selSD =
           CALCULATE( MIN('Date'[Date]), ALLSELECTED('Date'))
        VAR _selED =
            CALCULATE(MAX('Date'[Date]), ALLSELECTED('Date'))
        VAR _selapp =
            SELECTEDVALUE ( Alerts[Application] )
        RETURN
            SUMX (
                ADDCOLUMNS (
                    ADDCOLUMNS (
                        FILTER (
                            CloseHoursPerYear,
                            [Application] = _selapp
                                && [EndClose] > _selSD
                                && [StartClose] < _selED
                        ),
                        "NewSC", IF ( _selSD > [StartClose], _selSD, [StartClose] ),
                        "NewEC", IF ( _selED < [EndClose], _selED, [EndClose] )
                    ),
                    "duration",
                        VALUE ( [NewEC] - [NewSC] ) * 24 * 60
                ),
                [duration] //  "SC: " & [NewSC] & " EC: " & [NewEC] & " duration : " & [duration] & "
                //  "
            )

From there calc the amount of minutes the application should be running given the selected date range. 

OpenMinutesInDateRange = (VALUE( MAX('Date'[Date]) - MIN('Date'[Date])) * 24 * 60) - [CloseMinutesInDateRange]

From there calculate the availability ratio:

AvailabilityRatio = 1 - ([NettDownTime] / [OpenMinutesInDateRange])

 

File is attached. p.s. I will make a blog article out of this one because this keeps popping up as a question and its a quite tough one. 

 

Kind regards, Steve. 

 

View solution in original post

5 REPLIES 5
stevedep
Super User I
Super User I

Hi,

I have a solution for you although some minor tweaking is needed (will be explained later on). The result looks like this:

stevedep_0-1616399353663.png

So per application in the selected period (or region) the availability percentage is calculated (p.s. I gave A the close times of B for easier testing). 

 

Some steps were required:

1:  generate close hours for every week in the year (2021). 

a: Transform your table (basically unpivot)

b: Generate the close dates for the years using DAX

2:  add measures to calculate the nett downtime time (down time that is within the open hours / outside of the close hours. 

 

Here we go:

1a: transform data:

stevedep_1-1616399568125.png

Please note: I gave A the close hours of  B.

1b: Generate the dates:

CloseHoursPerYear = VAR _tblclosehours = CloseHours
   // FILTER ( CloseHours, [Application] = "B" )
VAR _tblclosehourswithddayvar =
    ADDCOLUMNS ( _tblclosehours, "dayvar", [D] - 1 )
VAR _tblweeks =
    //weeks
    ADDCOLUMNS (
        GENERATE (
            GENERATESERIES ( 0, 51, 1 ),
            //weeks
            ROW ( "StartDateTime", DATEVALUE ( "01/04/2021" ) )
        ),
        "StartDate",
            [StartDateTime] + ( [Value] * 7 )
    )
VAR _tblweekdays =
    ADDCOLUMNS (
        GENERATE ( _tblweeks, _tblclosehourswithddayvar ),
        "StartDateInWeek", [StartDate] + [dayvar]
    )
RETURN
    SELECTCOLUMNS (
        ADDCOLUMNS (
            GENERATE (
                SELECTCOLUMNS ( GENERATESERIES ( 1, 2, 1 ), "DayPart", [Value] ),
                _tblweekdays
            ),
            "StartTime",
                IF ( [DayPart] = 1, [StartDateInWeek], [StartDateInWeek] + [1] ),
            "EndTime",
                IF ( [DayPart] = 1, [StartDateInWeek] + [0], [StartDateInWeek] + 1 )
        ),
        "Application", [Application],
        "StartClose", [StartTime],
        "EndClose", [EndTime]
    )

 

2: Add the measures:

Minutes in downtime period (gross down time):

MinutesInPeriod = VALUE( SELECTEDVALUE(Alerts[End date]) - SELECTEDVALUE(Alerts[Start date])) * 24 * 60

Minutes in closing period for the alert:

CloseMinutesInPeriod = 
         VAR _selSD =
            SELECTEDVALUE ( Alerts[Start date] )
        VAR _selED =
            SELECTEDVALUE ( Alerts[End date] )
        VAR _selapp =
            SELECTEDVALUE ( Alerts[Application] )
        RETURN
            SUMX (
                ADDCOLUMNS (
                    ADDCOLUMNS (
                        FILTER (
                            CloseHoursPerYear,
                            [Application] = _selapp
                                && [EndClose] > _selSD
                                && [StartClose] < _selED
                        ),
                        "NewSC", IF ( _selSD > [StartClose], _selSD, [StartClose] ),
                        "NewEC", IF ( _selED < [EndClose], _selED, [EndClose] )
                    ),
                    "duration",
                        VALUE ( [NewEC] - [NewSC] ) * 24 * 60
                ),
                [duration] //  "SC: " & [NewSC] & " EC: " & [NewEC] & " duration : " & [duration] & "
                //  "
            )

Please note this is only covering the below scenario:

stevedep_2-1616399873350.png

Perhaps some exceptions need to be added. Please do some testing to verify..

Net downtime per app:

NettDownTime = SUMX(SUMMARIZE(Alerts,Alerts[Application], Alerts[Alert], Alerts[Start date], Alerts[End date]), ([MinutesInPeriod]-[CloseMinutesInPeriod]))

Per application calc the close mintutes per app in the selected period: 

CloseMinutesInDateRange = 
         VAR _selSD =
           CALCULATE( MIN('Date'[Date]), ALLSELECTED('Date'))
        VAR _selED =
            CALCULATE(MAX('Date'[Date]), ALLSELECTED('Date'))
        VAR _selapp =
            SELECTEDVALUE ( Alerts[Application] )
        RETURN
            SUMX (
                ADDCOLUMNS (
                    ADDCOLUMNS (
                        FILTER (
                            CloseHoursPerYear,
                            [Application] = _selapp
                                && [EndClose] > _selSD
                                && [StartClose] < _selED
                        ),
                        "NewSC", IF ( _selSD > [StartClose], _selSD, [StartClose] ),
                        "NewEC", IF ( _selED < [EndClose], _selED, [EndClose] )
                    ),
                    "duration",
                        VALUE ( [NewEC] - [NewSC] ) * 24 * 60
                ),
                [duration] //  "SC: " & [NewSC] & " EC: " & [NewEC] & " duration : " & [duration] & "
                //  "
            )

From there calc the amount of minutes the application should be running given the selected date range. 

OpenMinutesInDateRange = (VALUE( MAX('Date'[Date]) - MIN('Date'[Date])) * 24 * 60) - [CloseMinutesInDateRange]

From there calculate the availability ratio:

AvailabilityRatio = 1 - ([NettDownTime] / [OpenMinutesInDateRange])

 

File is attached. p.s. I will make a blog article out of this one because this keeps popping up as a question and its a quite tough one. 

 

Kind regards, Steve. 

 

View solution in original post

Thank you a lot !!!!!

stevedep
Super User I
Super User I

Hi,

I think you need to calculate something like a nett duration, so subtract hours that are outside of operating hours. 

Perhaps the following post will guide you in the right direction. 

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Time-Measure-Productive-hours-with-duplicates... 

 

Kind regards, Steve. 

daxer
Solution Sage
Solution Sage

@RobinWittstock 

 

This problem does not clearly state the rules of calculation. If you want to calculate the ratio for a set of applications, you have to state what exactly you mean by the availability ratio. It's easy to understand for just one app but there could be many different definitions for a set of apps.

Hi @daxer ,

Thank you for your reply, 
If many apps are selected, the average of their individual availability ratios ((selected period-downtime)/selected period) would be great. Same goes for geographical perimeter or app groups : that would be just filtering the alerts and compute the average of the availability ratios for the remaining apps.

Best regards, 

R.


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

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors