topic Re: Calculating availability in DAX Commands and Tips

Calculating availability

Azucrinador — Tue, 13 Sep 2022 19:48:16 GMT

Hi!
I'm trying to calculate the availability of service on DAX, I was able to do it by adding the running time (minutes), deducting 43200 and dividing by 43200 (30 days). It gives me the availability for 30 days only.

What I need is to be able to calculate the availability monthly, as example.

February has 28 days, so it would be running time -40320/4320.

Is there any way I can get the sum of the running time (in minutes), deduct the accordingly month time (in minutes) and divide by it?
Or if you have any other way to calculate the service availability based on the monthly time (in minutes).

Thank you very much!

Re: Calculating availability

Anonymous — Wed, 14 Sep 2022 03:14:22 GMT

Hi Azucrinador ,

You can refer the following links to get it:

Service Availability: Calculations and Metrics, Five 9s, and Best Practices

Calculating Availability (duration as a percentage of time range)

date range = VAR startdate = MIN ( 'Date'[Date] ) VAR enddate = MAX ( 'Date'[Date] ) RETURN DATEDIFF ( startdate, enddate, DAY ) + 1 percentage = VAR startdate = MIN ( 'Date'[Date] ) VAR enddate = MAX ( 'Date'[Date] ) VAR lostdate = SELECTEDVALUE ( data[lostservice] ) VAR regaineddate = SELECTEDVALUE ( data[regainedservice] ) RETURN IF ( lostdate >= startdate && regaineddate <= enddate, DATEDIFF ( lostdate, regaineddate, DAY ), IF ( lostdate >= startdate && lostdate <= enddate && regaineddate >= enddate, DATEDIFF ( lostdate, enddate, DAY ), IF ( lostdate < startdate && regaineddate <= enddate && regaineddate >= startdate, DATEDIFF ( startdate, regaineddate, DAY ), IF ( lostdate < startdate && regaineddate > enddate, DATEDIFF ( startdate, enddate, DAY ), BLANK () ) ) ) ) / [date range]

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Re: Calculating availability

amitchandak — Wed, 14 Sep 2022 03:17:09 GMT

Azucrinador ,

In any Date you can add minutes like

[Date] + Time(0,[Minutes column], 0)

but can you explain this

Is there any way I can get the sum of the running time (in minutes), deduct the accordingly month time (in minutes) and divide by it?

In case you days in month

day(eomonth([Date],0)) will give last day of month

Re: Calculating availability

Azucrinador — Wed, 14 Sep 2022 16:52:55 GMT

Hi Amitchandak, thank you for the reply, here is a sample of what I've already done which gives me the availability time within 30 days,

Here is the formula used "

SLA = ((SUM(Enel[TEMPO ABERTO])-43200)/43200)*-1" Tempo aberto = The period the service was having a failure.

In my case, we assume that the service should be running 24/7, and we deduct the time it had a failure (in minutes) to get the service availability, example below:

This is the information I get by using the formula, which gives me the availability for 30 days, but I need to be able to make it calculate month by month, like, February has 28 days, so the availability will be calculated in this period.

Can it be done in a single formula?

Re: Calculating availability

Azucrinador — Wed, 14 Sep 2022 16:54:57 GMT

Hi Rena, good afternoon! Thank you for the reply, here is a sample of what I've already done which gives me the availability time within 30 days,

In your case above you use the difference between 2 dates to calculate the availability, right? In my case, the date where the service was recovered is not the correct information, needing to be calculated with the "tempo aberto" which is the period the service was off.

Here is the formula used "