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,

 

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's avatar
dnewton
Icon for Helper II rankHelper II
8 years ago

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.