Available running time by monthly days.

[Azucrinador]

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

[v-tangjie-msft · Accepted solution]

Hi @Azucrinador ,

According to your description, I made changes. Here are my steps you can follow as a solution.

(1)This is my test data. I created a table from January to December for the year 2022, containing the unavailable time and date columns for each day of the month.

(2)We can create  a measure.

Running Time =

var _minmonth=MIN('Table'[Date])

var _days=DATEDIFF(_minmonth,EOMONTH(_minmonth,0),DAY)+1

var un_time=SUM('Table'[Unavailable time])

return

DIVIDE(un_time,_days,0)

(3)Then the result is as follows.

If this method does not meet your needs, you can provide us with detailed input and output examples in tabular form so that we can better solve the problem for you.

Best Regards,

Neeko Tang

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

[v-tangjie-msft]

Hi @Azucrinador,

According to your description, you want to calculate the number of minutes for each month that is available divided by thirty days ,right? Here are my steps you can follow as a solution. 

(1)We can create a table and get a date table from January to December 2022 as test data.

Table = CALENDAR(DATE(2022,1,1),DATE(2022,12,31))

(2) Renders the [Date] column in the format of [Date Hierarchy] and filters the month.

(3) We can create  measures.  

Running Time =

var _minmonth=MIN('Table'[Date])

var _days=DATEDIFF(_minmonth,EOMONTH(_minmonth,0),DAY)+1

return

-_days*24*60/43200

(4) Then the result is as follows.  

If this method does not meet your needs, you can provide us with detailed input and output examples in tabular form so that we can better solve the problem for you. 

Best Regards, 

Neeko Tang 

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

[Azucrinador]

Hi @v-tangjie-msft, thanks for the reply. Actually, what I need is to divide the unavailable time by days on a month. Example: 20 minutes unavailable, considering that Februrary has 28 days, it will be divided by 28 days. 

[johnt75]

If your measure will be used in a visual which uses columns from your date table then you can calculate the number of days in the given month with COUNTROWS('Date')