Time Intelegence question

[Anonymous]

I need help with an Availability report and am stuck in one area

"How to create the number of minutes in a given month, as this changes dependant on if 28, 29 (leap year) 30 or 31 days in the month." i need to be able to filter by year and month

Once i can create this, the monthly minutes I will deduct any outage time (create date - restore date)

Final outcome will be the percentage uptime in any given month i.e.

Sept 2022 = 43,200 mins - 125 mins (outage time) = 43,075 mins 0r 99.71% Up-Time

I would appreciate any help possible on the first step only

Kind Regards

Gary Gray

[mangaus1111]

Hi @Anonymous ,

see my pbi file

https://1drv.ms/u/s!Aj45jbu0mDVJiy2W2aAsl9ZKOgFG?e=3wJegv

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

[Anonymous · Accepted solution]

Mangaus,

Thaank you for quick reply, looks good except Feb 2022 (Leap year 29 days)

[Anonymous]

Mangaus,

Sorry it was Feb 2020 🙂

Thanks for help 

Gary

[Greg_Deckler]

@Anonymous EOMONTH should take care of that. See my previous reply.

[Anonymous]

Greg,

Using your code, every month is 1 hour out 😞

Gary

[Thennarasu_R]

Exact Answer I don't Know 

Try with these Dax functions Minute,Hour

[Greg_Deckler]

@Anonymous You could do something like this:

Minutes in Month =
  VAR __Date = MAX('Dates'[Date])
  VAR __FirstOfMonth = DATE(YEAR(__Date),MONTH(__Date),1)
  VAR __EndOfMonth = EOMONTH(__Date,0) + 1/24/60 * 1439
RETURN
  DATEDIFF(__FirstOfMonth, __EndOfMonth, MINUTE)

[Anonymous]

Greg,

Thanks for such a quick response, works well Except one hour out each month (see yellow column)