cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Regular Visitor

## Calculating availability hours of equipment (maintenance starting in different months)

Hi there!

I'm building an asset management report and I need to calculate the availability of the equipment. I'm relatively new in Power BI and searched for this on the community but didn't find anything explaining my doubt. For this case, I need the duration (in hours) when the machine is unavailable.

For example, considering that I filtered March on my report, I want to look to all the time that machine was not available, but only on the month that I selected (March). I've seen four scenarios of different occurences:

1. Maintenance that started on March and ended on March.
2. Maintenance that started on February and ended on March.

3. Maintenance that started on March and ended on May (or still going on).

4. Maintenance that started on February and endend on May (or still going on).

I put some examples on the table below and if you need more information or any more explanation, please feel free to ask.

 How the Data looks What I need to see when I select March Example Equipment Start date of Maintenance End date of Maintenance Eq. 1 Unavailability (hours) 1 Eq. 1 3/3/22 19:30 15/3/22 19:30 Eq. 1 288 2 Eq. 1 7/2/22 4:30 7/3/22 7:30 Eq. 1 175,5 3 Eq. 1 20/3/22 5:10 18/5/22 12:55 Eq. 1 744 4 Eq. 1 1/2/22 0:15 Eq. 1 744

Rodrigo

5 REPLIES 5
Community Support

Based on your description, it's recommeded to use measures instead of calcualted columns because of dynamic results based on slicer's selection.

The measure is as follows.

``````Unavailability (hours) =
VAR _max =
MAX ( 'Calendar'[Date] )
VAR _min =
MIN ( 'Calendar'[Date] )
VAR _start =
MAX ( 'Table'[Start date of Maintenance] )
VAR _end =
MAX ( 'Table'[End date of Maintenance] )
VAR _rangestart =
IF ( _start >= _min, _start, _min )
VAR _rangeend =
IF ( ISBLANK ( _end ), _max, IF ( _end >= _max, _max, _end ) )
RETURN
( _rangeend - _rangestart ) * 24
``````

You will find that the results are different from what you provided, and I will explain my results.

288.00 hours=Thu, 03 Mar 2022 19:30:00 to Tue, 15 Mar 2022 19:30:00

151.5.00 =Tue, 01 Mar 2022 00:00:00 to Mon, 07 Mar 2022 07:30:00

258.83 = Sun, 20 Mar 2022 05:10:00 to Thu, 31 Mar 2022 00:00:00

720.00 = Tue, 01 Mar 2022 00:00:00 to  Thu, 31 Mar 2022 00:00:00

If your logic is not like this, please provide a more detailed description.

Best Regards,

Stephen Tao

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

Regular Visitor

Hi @v-stephen-msft ! Thanks for the reply and sorry for the delay!

I've been trying other ways to do this and tried your solution as well but it didn't work and I found another problem.

Here is an example of my case. I have two tables, a fact (fMaintenance), which shows all the events of the machine and the Calendar dimension.
What I need is: whatever date filter I applied on the slicer, it needs to show the hours that the machine was down and the total time for this period (these two measures will be used in other measures in the future)

Example:

Asset Machine Operative_state Start_Date End_Date Start_hour End_hour

 Asset 1 2 Unavailable 2021-04-16 2021-04-21 19:38:00 20:36:00 Asset 1 1 Unavailable 2021-03-12 2021-06-07 15:02:00 10:20:00 Asset 1 1 Unavailable 2021-03-02 2021-03-12 16:55:00 15:02:00

Considering these three maintenance events in my database, when I applied the filter the result is blank:

But since the machine was not available in the period from March 2 to March 10, the result was supposed to be:
Machine_1 Unavailable_hours Total_Hours
Active 1      192                            192

I tried to attach a pbix file but it wasn't possible so I put a bigger example from my database below

Sorry if this message is double posted, but I had issues replying this post.

Asset Machine Operative_state Start_Date End_Date Start_hour End_hour

 Asset 1 1 Unavailable 2021-03-02 2021-03-12 16:55:00 15:02:00 Asset 1 1 Unavailable 2021-03-12 2021-06-07 15:02:00 10:20:00 Asset 1 2 Unavailable 2021-04-16 2021-04-21 19:38:00 20:36:00 Asset 1 2 Unavailable 2021-04-30 2021-04-30 15:45:00 15:53:00 Asset 1 2 Unavailable 2021-05-05 2021-05-07 19:47:00 13:30:00 Asset 1 2 Available 2021-05-07 2021-05-31 13:30:00 14:23:00 Asset 1 2 Unavailable 2021-05-07 2021-05-07 14:23:00 14:58:00 Asset 1 2 Available 2021-05-07 2021-05-07 14:58:00 17:16:00 Asset 1 2 Unavailable 2021-05-07 2021-05-10 17:16:00 10:50:00 Asset 1 2 Available 2021-05-10 2021-05-10 10:50:00 11:07:00 Asset 1 2 Unavailable 2021-05-10 2021-05-10 11:07:00 17:15:00 Asset 1 2 Available 2021-05-10 2021-05-10 17:15:00 18:21:00 Asset 1 2 Unavailable 2021-05-10 2021-05-11 18:21:00 13:30:00 Asset 1 2 Available 2021-05-11 2021-05-11 13:30:00 19:27:00 Asset 1 2 Unavailable 2021-05-11 2021-05-12 19:27:00 10:20:00 Asset 1 2 Available 2021-05-12 2021-05-12 10:20:00 10:48:00 Asset 1 2 Unavailable 2021-05-12 2021-05-14 10:48:00 17:08:00 Asset 1 2 Available 2021-05-14 2021-05-14 17:08:00 21:07:00 Asset 1 2 Unavailable 2021-05-14 2021-05-14 21:07:00 22:31:00 Asset 1 2 Available 2021-05-14 2021-05-15 22:31:00 00:07:00 Asset 1 2 Unavailable 2021-05-15 2021-05-15 00:07:00 18:12:00 Asset 1 2 Available 2021-05-15 2021-05-17 18:12:00 15:20:00 Asset 1 2 Unavailable 2021-05-17 2021-05-29 15:20:00 16:49:00 Asset 1 2 Available 2021-05-29 2021-05-29 16:49:00 18:20:00 Asset 1 2 Unavailable 2021-05-29 2021-05-29 18:20:00 23:00:00 Asset 1 2 Available 2021-05-29 2021-05-30 23:00:00 00:15:00 Asset 1 2 Unavailable 2021-05-30 2021-05-31 00:15:00 16:17:00 Asset 1 2 Available 2021-05-31 2021-06-01 16:17:00 05:03:00 Asset 1 1 Unavailable 2021-06-07 2021-06-07 10:20:00 18:37:00 Asset 1 1 Unavailable 2021-06-07 2021-10-15 18:37:00 08:42:00 Asset 1 2 Available 2021-06-08 2021-06-14 18:44:00 10:03:00 Asset 1 2 Unavailable 2021-10-13 2021-10-13 15:00:00 15:20:00 Asset 1 2 Available 2021-10-14 2021-10-14 00:16:00 10:05:00 Asset 1 1 Unavailable 2021-10-16 2022-09-10 12:19:00 09:30:00

Frequent Visitor

Hi,

Assuming your date table is not connected to this table. The below method would give you the maintainance hours for the selected time period (in this case March). There is probably a cleaner way to do it but this will work. It will not work for totals though, only on a row by row basis.

Maintainance hours =

var startofmaintainance = MIN('Example (2)'[Start date of Maintenance])
var endofmain = MAX('Example (2)'[End date of Maintenance])
var mindate = MIN(Dates[Date])
var maxdate = MAX(Dates[Date])+1

var startdateused = IF(startofmaintainance>mindate , startofmaintainance,mindate)
var enddateused = IF(endofmain<maxdate , endofmain,maxdate)

return (enddateused - startdateused)*24

The result will be:

Regular Visitor

Hi @chris88 ! Thanks for the reply and sorry for the delay!

I have a date table connected to this table. I put more informations on my other reply on this repost, appreciate all of your help!

Best regards,

Frequent Visitor

Hi,

How is your date table connected. Which field is the active relationship to, that might be where you are seeing a problem. Can you send across the PBIX, I'll PM you.

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors