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

[ROYMMM]

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"?

Thanks for your help.

[v-eachen-msft · Accepted solution]

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.