Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 |
Appreciate all of your help,
Rodrigo
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
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 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
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 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 |
Appreciate all of your help!
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.
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.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
108 | |
100 | |
39 | |
35 |
User | Count |
---|---|
149 | |
121 | |
76 | |
74 | |
51 |