Forum Discussion
Calculating duration between categories in same column
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 :)
6 Replies
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
Hi Anonymous
I don't know how to calcualte "Time since servicing" and "Failure Duration".
Could you tell me the rule?
Best Regards
MaggieCommunity 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!
