Calculate Duration Datetime and Sum

[BlastS]

Greetings, i am trying to get a duration of a single column which contains the datetime and after that i need to sum that duration between the dates i want to get which i gona show in a example.

First Examle of my DateTime With N values: (Duration should be likeB= B - A = 10m ; C = C-B = 10 and .... same to the others probably the first value needs to be  0 since there is nothing before). Sounds easy on C# but i dont know how to do this on powerBI.

Column: DaTeTime

20/05/2012 01:20:00  (A)
20/05/2012 01:30:00  (B)
20/05/2012 01:40:00  (C)

The next step after i get a Duration column would be to sum that duration based of the purple area (by saying this i am saying i want to get all datetime(duration) inside purple area and sum that.

How can i achieve this on powerbI (on first step get duration column and next get duration inside the purple area) is this possible?

[BlastS]

UpdateInfo:

First Step off getting the duration is already done, which i achieve this using this : 

Duration = IF(DATEDIFF(LOOKUPVALUE(X[Time],X[Index],X[Index]-1),X[Time],MINUTE)>720,BLANK(),DATEDIFF(LOOKUPVALUE(X[Time],X[Index],X[Index]-1),X[Time],MINUTE)) 

2Step now is where it becomes hard, since i want to sum the duration from the time inside that purple area i dont have aclue how to make this here. Is like i want to put, for example, SUM(Duration FROM TIME WHERE LINE IS 1) .

The Row(Line) that makes the purple area you can watch on the image have values 0 and 1, meaning that when is 1it makes the purple area when is 0 dont exist area.

[Ashish_Mathur]

Hi,

Try this measure

=SUM([Duration])

Hope this helps.

[BlastS]

Hi, @Ashish_Mathur

That will sum all duration and not only the duration that belong to the purple area on the image, so that will never work for this case.

I need something like this example https://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339..., but i dont have the started repair and end repair date witch i dont know to apply this to my case.

[Ashish_Mathur]

Hi,

In a spare column, share the exact result you are expecting.

[BlastS]

@Ashish_MathurThis is something very siilar that i planned to get ,but i want to get sum the duration of the datetime inside the purple area in my image. like he got between failures.

[Ashish_Mathur]

That is not what i am expecting from you.  I want the exact numeric answer you are expecting.  Once i write my DAX formula, i will then compare my answer with yours.

[BlastS]

@Ashish_Mathur

This is a example of an expected output:

AverageTimeFailure

2Days10Hours20min

 By Sum the duration of each date duration inside purple area it will give that.

Basically the column will be that AverageTimeFailure (Sum(All durations inside purple area and next divide by the number of fails), hope i helped you with this.

[Ashish_Mathur]

Hi,

I still do not understand.  Someone else will help you.  Sorry.