Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I'm trying to calculate the Utilisation Time between a Servicing Event and a Failure event (for a specific Machine Id).
The dataset currently looks like:
ID | Event Start | Event Finish | Event | Duration (hours) |
123456 | 3/08/2019 6:49 | 5/08/2019 17:31 | Servicing | 47.43 |
123456 | 3/08/2019 17:59 | 3/08/2019 19:38 | Failure | 1.64 |
123456 | 5/08/2019 18:20 | 6/08/2019 11:11 | Utilisation | 13.65 |
123456 | 6/08/2019 13:40 | 7/08/2019 7:01 | Utilisation | 17.35 |
123456 | 7/08/2019 14:11 | 8/08/2019 4:45 | Utilisation | 13.25 |
123456 | 8/08/2019 7:19 | 8/08/2019 19:55 | Utilisation | 12.16 |
123456 | 8/08/2019 19:55 | 9/08/2019 6:30 | Servicing | 10.58 |
123456 | 9/08/2019 23:07 | 10/08/2019 4:11 | Servicing | 5.08 |
123456 | 10/08/2019 18:16 | 11/08/2019 5:52 | Utilisation | 8.94 |
123456 | 11/08/2019 10:56 | 12/08/2019 5:43 | Utilisation | 18.77 |
123456 | 12/08/2019 11:06 | 12/08/2019 21:14 | Failure | 10.14 |
123456 | 13/08/2019 2:42 | 13/08/2019 5:48 | Utilisation | 3.09 |
123456 | 13/08/2019 11:00 | 13/08/2019 12:58 | Servicing | 1.98 |
123456 | 14/08/2019 18:20 | 15/08/2019 4:30 | Utilisation | 10.16 |
123456 | 15/08/2019 6:42 | 15/08/2019 19:00 | Utilisation | 12.30 |
123456 | 16/08/2019 6:22 | 17/08/2019 10:04 | Utilisation | 23.38 |
123456 | 17/08/2019 10:04 | 17/08/2019 17:25 | Utilisation | 7.36 |
123456 | 17/08/2019 19:29 | 18/08/2019 5:29 | Failure | 6.89 |
123456 | 18/08/2019 22:08 | 19/08/2019 5:05 | Utilisation | 5.30 |
I am trying to achieve the following in a table visual in Power BI (the table will only show information if there has been a Failure after a Servicing, not the other way around etc.):
ID | Event Start | Time since servicing | Failure Duration |
123456 | 3/08/2019 6:49 | 0 | 1.64 |
123456 | 9/08/2019 23:07 | 27.71 | 10.14 |
123456 | 13/08/2019 11:00 | 53.21 | 6.89 |
I have tried creating measures, and so far I have created a cumulative duration measure which calculates the cumulative utilisation, but that doesn't really solve anything because the table then looks like this (small subset):
ID | Event Start | Event Finish | Event | Duration (hours) | Cumulative Duration (Utilsation) |
123456 | 13/08/2019 11:00 | 13/08/2019 12:58 | Servicing | 1.98 | |
123456 | 14/08/2019 18:20 | 15/08/2019 4:30 | Utilisation | 10.16 | 10.16 |
123456 | 15/08/2019 6:42 | 15/08/2019 19:00 | Utilisation | 12.30 | 22.47 |
123456 | 16/08/2019 6:22 | 17/08/2019 10:04 | Utilisation | 23.38 | 45.85 |
123456 | 17/08/2019 10:04 | 17/08/2019 17:25 | Utilisation | 7.36 | 53.21 |
123456 | 17/08/2019 19:29 | 18/08/2019 5:29 | Failure | 6.89 |
Any help would be much appreciated!!
Thanks 🙂
Solved! Go to Solution.
I ended up creating an R Visual to get what I wanted. Thanks
I ended up creating an R Visual to get what I wanted. Thanks
I don't know if this is something that can even be achieved but any help would be much appreciated
Hi @ayeshask
I don't know how to calcualte "Time since servicing" and "Failure Duration".
Could you tell me the rule?
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-juanli-msft
Hi!
The Failure Duration is just the Duration of the Failure from the first table (no calculations). The Time Since Servicing is supposed to be the sum of all the Utilisation Durations between a Servicing Event and a Failure Event.
Sorry I know it's extremely confusing!
Check if the issue can be resolved with the suggestion in
https://community.powerbi.com/t5/Desktop/how-to-identify-previous-value/m-p/780841#M376125
Hi @amitchandak ! I have already looked at that, and I have kind of used it to create a measure to show me the last service data next to the failure date:
PrevSrvcDate = VAR __index = MAX ( [EventStart] ) //in visual table context, this is the current index VAR FailureBefore = CALCULATE ( MAX ( 'Table1'[EventStart] ), ALLSELECTED ( 'Table1' ), 'Table1'[EventStart] < __index, 'Table1'[Event] = "Failure" ) VAR SrvcBefore = CALCULATE ( MAX ( 'Table1'[EventStart] ), ALLSELECTED ( 'Table1' ), 'Table1'[EventStart] < __index, 'Table1'[Event] = "Servicing" ) RETURN IF ( FailureBefore <= SrvcBefore, SrvcBefore, "" )
However I still have the issue of not having the Utilisation Time visible next to the Failure:
ID | Event Start | Event Finish | Event | Duration (hours) | Cumulative Utilsation | PrevSrvcDate |
123456 | 13/08/2019 11:00 | 13/08/2019 12:58 | Servicing | 1.98 | ||
123456 | 14/08/2019 18:20 | 15/08/2019 4:30 | Utilisation | 10.16 | 10.16 | 13/08/2019 11:00 |
123456 | 15/08/2019 6:42 | 15/08/2019 19:00 | Utilisation | 12.30 | 22.47 | 13/08/2019 11:00 |
123456 | 16/08/2019 6:22 | 17/08/2019 10:04 | Utilisation | 23.38 | 45.85 | 13/08/2019 11:00 |
123456 | 17/08/2019 10:04 | 17/08/2019 17:25 | Utilisation | 7.36 | 53.21 | 13/08/2019 11:00 |
123456 | 17/08/2019 19:29 | 18/08/2019 5:29 | Failure | 6.89 | 13/08/2019 11:00 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |