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

 

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

    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's avatar
      Anonymous
      Not applicable
      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

       

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

        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?