Forum Discussion

dnewton's avatar
dnewton
Icon for Helper II rankHelper II
8 years ago
Solved

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

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 monit...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

     

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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