Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Alert | Application | Group | Start date | End date | Ville |
AA2 | B | G2 | 10/02/2021 09:35 | 10/02/2021 17:31 | ROME |
AA3 | C | G3 | 14/01/2021 09:52 | 14/01/2021 23:34 | BEIJING |
AA4 | D | G2 | 14/03/2021 08:00 | 14/03/2021 22:00 | PARIS |
AA5 | E | G1 | 17/03/2021 09:14 | 01/04/2021 10:12 | ROME |
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) :
Application | Monday Morning | Monday Evening | Tuesday Morning | Tuesday evening | Wednesday morning | Wednesday evening | Thursday morning | Thursday Evening | Friday morning | Friday evening | Saturday Morning | Saturday evening | Sunday morning | Sunday evening |
A | 0:00 | 23:59 | 0:00 | 23:59 | 0:00 | 23:59 | 0:00 | 23:59 | 0:00 | 23:59 | 0:00 | 23:59 | 0:00 | 23:59 |
B | 9:00 | 21:00 | 9:30 | 15:00 | 9:00 | 21:00 | 9:30 | 15:00 | 9:00 | 21:00 | 9:30 | 15:00 | 9:00 | 21:00 |
C | 9:00 | 18:00 | 9:30 | 15:00 | 9:00 | 18:00 | 9:30 | 15:00 | 9:00 | 18:00 | 9:30 | 15:00 | 9:00 | 12:00 |
D | 9:00 | 22:00 | 9:30 | 15:00 | 9:00 | 22:00 | 9:30 | 15:00 | 9:00 | 22:00 | 9:30 | 15:00 | 9:00 | 12:00 |
E | 9:00 | 18:00 | 9:30 | 15:00 | 9:00 | 18:00 | 9:30 | 15:00 | 9:00 | 18:00 | 9:30 | 15:00 | 9:00 | 12:00 |
F | 10:00 | 17:00 | 9:30 | 15:00 | 10:00 | 17:00 | 9:30 | 15:00 | 10:00 | 17:00 | 9:30 | 15:00 | 10:00 | 12: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.
Solved! Go to Solution.
Hi,
I have a solution for you although some minor tweaking is needed (will be explained later on). The result looks like this:
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:
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:
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.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Hi,
I have a solution for you although some minor tweaking is needed (will be explained later on). The result looks like this:
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:
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:
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.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Thank you a lot !!!!!
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.
Kind regards, Steve.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
@Anonymous
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 @Anonymous ,
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.
User | Count |
---|---|
42 | |
26 | |
21 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |