Calculating difference between rows

[FlowViz]

Hi there, I have the following data and want to calculate the difference in days between the two highlighted dates:

Note: this may happen multiple times within the dataset, as each 'revision' is for a different [IssueID]

Any thoughts?

[v-jingzhang · Accepted solution]

Hi @FlowViz

Can you explain why you highlight these two rows? Is it because they have "Flagged" value in Field column?

If my assumption is correct, you can add a new column with below code

Date Difference =
IF (
    'Table'[Field] = "Flagged",
    VAR vCurDate = 'Table'[ChangedDate]
    VAR vNextDate =
        CALCULATE (
            MIN ( 'Table'[ChangedDate] ),
            ALLEXCEPT ( 'Table', 'Table'[IssueID] ),
            'Table'[ChangedDate] > vCurDate,
            'Table'[Field] = "Flagged"
        )
    RETURN
        DATEDIFF ( vCurDate, vNextDate, DAY ),
    BLANK ()
)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.