cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ayeshask Frequent Visitor
Frequent Visitor

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:

IDEvent StartEvent FinishEventDuration (hours)
1234563/08/2019 6:495/08/2019 17:31Servicing47.43
1234563/08/2019 17:593/08/2019 19:38Failure1.64
1234565/08/2019 18:206/08/2019 11:11Utilisation13.65
1234566/08/2019 13:407/08/2019 7:01Utilisation17.35
1234567/08/2019 14:118/08/2019 4:45Utilisation13.25
1234568/08/2019 7:198/08/2019 19:55Utilisation12.16
1234568/08/2019 19:559/08/2019 6:30Servicing10.58
1234569/08/2019 23:0710/08/2019 4:11Servicing5.08
12345610/08/2019 18:1611/08/2019 5:52Utilisation8.94
12345611/08/2019 10:5612/08/2019 5:43Utilisation18.77
12345612/08/2019 11:0612/08/2019 21:14Failure10.14
12345613/08/2019 2:4213/08/2019 5:48Utilisation3.09
12345613/08/2019 11:0013/08/2019 12:58Servicing1.98
12345614/08/2019 18:2015/08/2019 4:30Utilisation10.16
12345615/08/2019 6:4215/08/2019 19:00Utilisation12.30
12345616/08/2019 6:2217/08/2019 10:04Utilisation23.38
12345617/08/2019 10:0417/08/2019 17:25Utilisation7.36
12345617/08/2019 19:2918/08/2019 5:29Failure6.89
12345618/08/2019 22:0819/08/2019 5:05Utilisation5.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.):

IDEvent StartTime since servicingFailure Duration
1234563/08/2019 6:4901.64
1234569/08/2019 23:0727.7110.14
12345613/08/2019 11:0053.216.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):

IDEvent StartEvent FinishEventDuration (hours)Cumulative Duration (Utilsation)
12345613/08/2019 11:0013/08/2019 12:58Servicing1.98 
12345614/08/2019 18:2015/08/2019 4:30Utilisation10.1610.16
12345615/08/2019 6:4215/08/2019 19:00Utilisation12.3022.47
12345616/08/2019 6:2217/08/2019 10:04Utilisation23.3845.85
12345617/08/2019 10:0417/08/2019 17:25Utilisation7.3653.21
12345617/08/2019 19:2918/08/2019 5:29Failure6.89 

 

Any help would be much appreciated!!

Thanks 🙂

1 ACCEPTED SOLUTION

Accepted Solutions
ayeshask Frequent Visitor
Frequent Visitor

Re: Calculating duration between categories in same column

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

View solution in original post

6 REPLIES 6
amitchandak Super Contributor
Super Contributor

Re: Calculating duration between categories in same column

ayeshask Frequent Visitor
Frequent Visitor

Re: Calculating duration between categories in same column

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:

IDEvent StartEvent FinishEventDuration (hours)Cumulative UtilsationPrevSrvcDate
12345613/08/2019 11:0013/08/2019 12:58Servicing1.98  
12345614/08/2019 18:2015/08/2019 4:30Utilisation10.1610.16 13/08/2019 11:00
12345615/08/2019 6:4215/08/2019 19:00Utilisation12.3022.47 13/08/2019 11:00
12345616/08/2019 6:2217/08/2019 10:04Utilisation23.3845.85 13/08/2019 11:00
12345617/08/2019 10:0417/08/2019 17:25Utilisation7.3653.21 13/08/2019 11:00
12345617/08/2019 19:2918/08/2019 5:29Failure6.89  13/08/2019 11:00
Community Support Team
Community Support Team

Re: Calculating duration between categories in same column

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 Frequent Visitor
Frequent Visitor

Re: Calculating duration between categories in same column

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

ayeshask Frequent Visitor
Frequent Visitor

Re: Calculating duration between categories in same column

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

ayeshask Frequent Visitor
Frequent Visitor

Re: Calculating duration between categories in same column

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

View solution in original post

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 6 members 3,357 guests
Please welcome our newest community members: