Forum Discussion
Helper IIDifference (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 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.
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
7 Replies
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
dnewtonThank 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?
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
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 ?
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.