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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Rodrigo_fig15
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,

 

Rodrigo

5 REPLIES 5
v-stephen-msft
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 ) )
RETURN
    ( _rangeend - _rangestart ) * 24

vstephenmsft_0-1674183189622.png

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)


Example:

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:

Rodrigo_fig15_0-1674654015097.png

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: https://drive.google.com/drive/folders/1Ny4NI1HykgZM7V64S7diEwaa5AWuzwqh


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!

 

chris88
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:
 
chris88_0-1674167235094.png

 

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,

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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