Calculate difference between consecutive rows filtered by SELECT DISTINCT
I need to calculate the difference between values in consecutive rows and display the value in the 2nd of each pair of rows, apparently without the help of an index. Specifically, I created a calculated column, Workdays Since Filed, and now want to subtract the Row 1 Workdays value from the Row 2 value and display the result in Row 2 as a calculated measure. Values in red show how it should work: PERMIT_ID ACTION ACTION DATE FILED DATE WORKDAYS SINCE FILED (calculated) DAYS BETWEEN (calc) (Just to show math, not a real column) FFXREC215FN Submission Deficiencies Issued 11/29/2021 11/12/2021 11 FFXREC215FN New Document Received 12/6/2021 11/12/2021 16 5 16 - 11 FFXREC215FN New Document Received 12/7/2021 11/12/2021 17 1 17 - 16 FFXREC215FN Waiting for Information 12/10/2021 11/12/2021 20 3 20 - 17 Getting the "Days Between" calculation is complicated by the filters on this table that made using an index difficult. The main filter is PERMIT_ID and that could be indexed using dynamic filtering tricks I saw on other posts in this forum, but the filter that really hoses things up is a SELECT DISTINCT that removes duplicate rows from display when multiple new documents are received in the same day. So the user sees this: Behind the scenes, the table actually looks like this: PERMIT_ID ACTION ACTION DATE FILED DATE WORKDAYS SINCE FILED DAYS BETWEEN FFXREC215FN Submission Deficiencies Issued 11/29/2021 11/12/2021 11 FFXREC215FN New Document Received 12/6/2021 11/12/2021 16 5 FFXREC215FN New Document Received 12/6/2021 11/12/2021 16 0 FFXREC215FN New Document Received 12/6/2021 11/12/2021 16 0 FFXREC215FN New Document Received 12/7/2021 11/12/2021 17 1 FFXREC215FN New Document Received 12/7/2021 11/12/2021 17 0 FFXREC215FN New Document Received 12/7/2021 11/12/2021 17 0 FFXREC215FN Waiting for Information 12/10/2021 11/12/2021 20 3 I worked through solutions on about 5 similar posts but didn't find one that fit this issue specifically. Any ideas? Thanks, Allison
