Calculating duration between categories in same column

[ayeshask]

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 🙂

[ayeshask · Accepted solution]

I ended up creating an R Visual to get what I wanted. Thanks

[ayeshask]

I don't know if this is something that can even be achieved but any help would be much appreciated

[v-juanli-msft]

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.

[ayeshask]

@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!

[amitchandak]

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

[ayeshask]

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