Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
ExampleEquipmentStart date of MaintenanceEnd date of Maintenance Eq. 1Unavailability (hours)
1Eq. 13/3/22 19:3015/3/22 19:30 Eq. 1288
2Eq. 17/2/22 4:307/3/22 7:30 Eq. 1175,5
3Eq. 120/3/22 5:1018/5/22 12:55 Eq. 1744
4Eq. 11/2/22 0:15  Eq. 1744


Appreciate all of your help,



Community Support
Community Support

Hi @Rodrigo_fig15 ,


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 ) )
    ( _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.                      

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)


Asset Machine Operative_state Start_Date End_Date Start_hour End_hour

Asset 12Unavailable2021-04-162021-04-2119:38:0020:36:00
Asset 11Unavailable2021-03-122021-06-0715:02:0010:20:00
Asset 11Unavailable2021-03-022021-03-1216:55:0015: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

Edit: I'm sharing this link that the pbix is available to download:

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 11Unavailable2021-03-022021-03-1216:55:0015:02:00
Asset 11Unavailable2021-03-122021-06-0715:02:0010:20:00
Asset 12Unavailable2021-04-162021-04-2119:38:0020:36:00
Asset 12Unavailable2021-04-302021-04-3015:45:0015:53:00
Asset 12Unavailable2021-05-052021-05-0719:47:0013:30:00
Asset 12Available2021-05-072021-05-3113:30:0014:23:00
Asset 12Unavailable2021-05-072021-05-0714:23:0014:58:00
Asset 12Available2021-05-072021-05-0714:58:0017:16:00
Asset 12Unavailable2021-05-072021-05-1017:16:0010:50:00
Asset 12Available2021-05-102021-05-1010:50:0011:07:00
Asset 12Unavailable2021-05-102021-05-1011:07:0017:15:00
Asset 12Available2021-05-102021-05-1017:15:0018:21:00
Asset 12Unavailable2021-05-102021-05-1118:21:0013:30:00
Asset 12Available2021-05-112021-05-1113:30:0019:27:00
Asset 12Unavailable2021-05-112021-05-1219:27:0010:20:00
Asset 12Available2021-05-122021-05-1210:20:0010:48:00
Asset 12Unavailable2021-05-122021-05-1410:48:0017:08:00
Asset 12Available2021-05-142021-05-1417:08:0021:07:00
Asset 12Unavailable2021-05-142021-05-1421:07:0022:31:00
Asset 12Available2021-05-142021-05-1522:31:0000:07:00
Asset 12Unavailable2021-05-152021-05-1500:07:0018:12:00
Asset 12Available2021-05-152021-05-1718:12:0015:20:00
Asset 12Unavailable2021-05-172021-05-2915:20:0016:49:00
Asset 12Available2021-05-292021-05-2916:49:0018:20:00
Asset 12Unavailable2021-05-292021-05-2918:20:0023:00:00
Asset 12Available2021-05-292021-05-3023:00:0000:15:00
Asset 12Unavailable2021-05-302021-05-3100:15:0016:17:00
Asset 12Available2021-05-312021-06-0116:17:0005:03:00
Asset 11Unavailable2021-06-072021-06-0710:20:0018:37:00
Asset 11Unavailable2021-06-072021-10-1518:37:0008:42:00
Asset 12Available2021-06-082021-06-1418:44:0010:03:00
Asset 12Unavailable2021-10-132021-10-1315:00:0015:20:00
Asset 12Available2021-10-142021-10-1400:16:0010:05:00
Asset 11Unavailable2021-10-162022-09-1012:19:0009:30:00

Appreciate all of your help!


Frequent Visitor



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:


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,



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. 

Helpful resources


Fabric certifications survey

Certification feedback opportunity for the community.


Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.