cancel
Showing results for
Did you mean: Frequent Visitor

## Calculate elapsed time between two records based on values in different colums (in DAX)

Hi,

I have a table with records containing status changes of tickets. What I want to know is how long it took for a ticket to change from a "To Be Approved" status to another status. (which means that a ticket has been reviewed) In the example below the ticket IDs are in the 3rd column. So for example ticket A01 is submitted for review on Jan 1st at 12:00PM. The ticket is reviewed on January 4th at 3PM. As tickets sometimes get reviewed randomnly it can be that a ticket that comes in later will be reviewed earlier than another one. Like below, ticket B01. Important to note: tickets might also be submitted for review several times. (see below ticket A01) So I want to know how long each indiviual review took. How can I add a column with the elapsed time for the records where the "Old status" value is "To Be Approved"?

1 ACCEPTED SOLUTION  Community Support

Hi @ROYMMM ,

You could create a rank column at first, then use FIRSTNONBLANK() function to calculate the time.

``````rank =
RANKX (
FILTER ( 'Table', 'Table'[TID] = EARLIER ( 'Table'[TID] ) ),
'Table'[ID],
,
ASC,
DENSE
)``````
``````Time =
VAR a =
CALCULATE (
FIRSTNONBLANK ( 'Table'[Date], 1 ),
FILTER (
'Table',
'Table'[rank]
= EARLIER ( 'Table'[rank] ) - 1
&& 'Table'[TID] = EARLIER ( 'Table'[TID] )
)
)
RETURN
IF ( 'Table'[Old] = "To Be approved", DATEDIFF ( a, 'Table'[Date], DAY ) )``````

Here is my test file for your reference.

If this post helps, then please consider Accept it as the solution to help the other members find it.  Community Support

Hi @ROYMMM ,

You could create a rank column at first, then use FIRSTNONBLANK() function to calculate the time.

``````rank =
RANKX (
FILTER ( 'Table', 'Table'[TID] = EARLIER ( 'Table'[TID] ) ),
'Table'[ID],
,
ASC,
DENSE
)``````
``````Time =
VAR a =
CALCULATE (
FIRSTNONBLANK ( 'Table'[Date], 1 ),
FILTER (
'Table',
'Table'[rank]
= EARLIER ( 'Table'[rank] ) - 1
&& 'Table'[TID] = EARLIER ( 'Table'[TID] )
)
)
RETURN
IF ( 'Table'[Old] = "To Be approved", DATEDIFF ( a, 'Table'[Date], DAY ) )``````

Here is my test file for your reference.

If this post helps, then please consider Accept it as the solution to help the other members find it. Announcements #### Welcome to the User Group Public Preview  