Difference (time in minutes etc...) between two rows

[dnewton]

Hi, I'm looking for some advice on how to calculate the time between two rows in my database. A screenshot of the database is attached so the data can be seen for reference.

The database is monitoring system uptime and each time the "status" of the system changes a new row is added to the database with either a "FAILING" or "SUCCESS" current status and the previous status in a seporate column, along with the "state change time".

What I'd like to do is perform a calculation on the rows in order to produce a "duration".

So for example using the first two rows of data (1 and 2), the duration of downtime would be 10 minutes (FAILING at 25/06/2018 10:28:53 (row 2) to SUCESSFUL at 25/06/2018 10:38:53 (row 1)).

Is this possible to do? I've tried a lot of DATEDIFF calculations but none seem to work for me.

[dnewton]

@AnonymousI think I've worked out the problem...

I have multiple checks in my table identified by the check ID column (first column in the screenshot of the dataset in my first post... but the DAX formula is (I think) just looking at the time between failures regartdless of the check name. Do you think it's possible to add in the check ID to the formula - so it calculates the time between failure and sucess where the check ID in the failure is the same check ID in the sucess ?

[Anonymous]

Hey @dnewton,

I meant to get back to you sooner but got busy. Try adding a variable to grab the current Check ID like:

VAR CurrentID = FIRSTNONBLANK('EventMgmt'[Check ID],1)

Then you can add this to the filter conditionwhen checking for the IndexOfPreviousSuccess where the ALL function is specified. Because of this change, I also added code to the code to calculate the IndexOfFollowingFailure variable. I haven't tested it but give it a try!

Downtime (Mins) = 
VAR CurrentIndex = FIRSTNONBLANK('EventMgmt'[Index],1)
VAR CurrentStatus = FIRSTNONBLANK('EventMgmt'[Current State],1)
VAR CurrentID = FIRSTNONBLANK('EventMgmt'[Check ID],1)
VAR IndexOfPreviousSuccess = 
CALCULATE(
 MAX('EventMgmt'[Index]),
 FILTER(
    ALL('EventMgmt'),
    'EventMgmt'[Index] < CurrentIndex &&
    'EventMgmt'[Current State] = "SUCCESS" &&
    'EventMgmt'[Check ID] = CurrentID
 )
)
VAR IndexOfFollowingFailure =
CALCULATE(
 MIN('EventMgmt'[Index]),
 FILTER(
    ALL('EventMgmt'),
    'EventMgmt'[Index] < CurrentIndex &&
    'EventMgmt'[Index] > IndexOfPreviousSuccess &&
    'EventMgmt'[Current State] = "FAILURE" &&
    'EventMgmt'[Check ID] = CurrentID
 )
)
RETURN
IF(
 OR(CurrentIndex = 0, CurrentStatus = "FAILING"),
 0,
 DATEDIFF(
    CALCULATE(
       FIRSTNONBLANK('EventMgmt'[State Change Time],1),
       FILTER(
          ALL('EventMgmt'),
          'EventMgmt'[Index] = IndexOfFollowingFailure
       )
    ),
 FIRSTNONBLANK('EventMgmt'[State Change Time],1),
 MINUTE
 )
)

Hope this helps,

Parker

[dnewton]

Hey @Anonymous

Thanks for this, I really appreciate all your help so far. I've used the new code (worked straight away), this feels like exactly what I need but I still seem to have odd times being calculated. Here's another screenshot showing the different check ID's and the sometimes totallly random time between failures which is literally only a few minutes but comes up as 3,750

This was taken using the new code. I'll continue to tweak and play around and will update the thread if I get anywhere. If you have anymore suggestions it'd be massively appreciated.

[Anonymous]

Hey @dnewton

Give this a try. Start by creating an index column on your table in the Query Editor. You will end up with something like this.

Now you can use your indexes in a measure calculation like this:

TimeDiff = 
VAR CurrentIndex = FIRSTNONBLANK('Time'[Index],1)
RETURN
IF(
    CurrentIndex = 0,
    0,
    DATEDIFF(
        CALCULATE(
            FIRSTNONBLANK('Time'[FullTime],1),
            FILTER(
                ALL('Time'),
                'Time'[Index] = CurrentIndex - 1
            )
        ),
        FIRSTNONBLANK('Time'[FullTime],1),
        MINUTE
    )
)

You end up with the desired result.

In case you have multiple failures and need to get the first failure to the next successful, this can be done as well with a little more code.

Hope this helps,

Parker

[dnewton]

Thank you Parker this is excellent and seems to be working well.

Can you elaborate on the additional code required to get multiple failures as I can see this happening?

[Anonymous · Accepted solution]

If you're trying to calculate the time difference from the first failure to the next success it is:

Downtime = 
VAR CurrentIndex = FIRSTNONBLANK('Time'[Index],1)
VAR CurrentStatus = FIRSTNONBLANK('Time'[Status],1)
VAR IndexOfPreviousSuccess = 
CALCULATE(
 MAX('Time'[Index]),
 FILTER(
    ALL('Time'),
    AND(
       'Time'[Index] < CurrentIndex,
       'Time'[Status] = "Successful"
    )
 )
)
VAR IndexOfFollowingFailure = 
 IF(
    ISBLANK(IndexOfPreviousSuccess),
    0,
    IndexOfPreviousSuccess + 1
 ) 
RETURN
IF(
 OR(CurrentIndex = 0, CurrentStatus = "Failing"),
 0,
 DATEDIFF(
    CALCULATE(
       FIRSTNONBLANK('Time'[FullTime],1),
       FILTER(
          ALL('Time'),
          'Time'[Index] = IndexOfFollowingFailure
       )
    ),
 FIRSTNONBLANK('Time'[FullTime],1),
 MINUTE
 )
)

 You end up with output like:

If you also want to show the downtime at each FAILING step, just take out the CurrentStatus = "FAILING" part of the OR statement.

Hope this helps,

Parker

[dnewton]

Hi Parker,

I wonder if you have any advice for the below - I seem to be running into an issue where some of the calcuations are incorrect (e.g. the correct time between FAILING and SUCESS in the first two rows is 35 minutes not 25). I'm not sure if this is something I'm doing wrong perhaps? I have copied the code below using the table names

You can see another example of this below:

Downtime (Mins) = 
VAR CurrentIndex = FIRSTNONBLANK('EventMgmt'[Index],1)
VAR CurrentStatus = FIRSTNONBLANK('EventMgmt'[Current State],1)
VAR IndexOfPreviousSuccess = 
CALCULATE(
 MAX('EventMgmt'[Index]),
 FILTER(
    ALL('EventMgmt'),
    AND(
       'EventMgmt'[Index] < CurrentIndex,
       'EventMgmt'[Current State] = "SUCCESS"
    )
 )
)
VAR IndexOfFollowingFailure = 
 IF(
    ISBLANK(IndexOfPreviousSuccess),
    0,
    IndexOfPreviousSuccess + 1
 ) 
RETURN
IF(
 OR(CurrentIndex = 0, CurrentStatus = "FAILING"),
 0,
 DATEDIFF(
    CALCULATE(
       FIRSTNONBLANK('EventMgmt'[State Change Time],1),
       FILTER(
          ALL('EventMgmt'),
          'EventMgmt'[Index] = IndexOfFollowingFailure
       )
    ),
 FIRSTNONBLANK('EventMgmt'[State Change Time],1),
 MINUTE
 )
)

Does this look correct?