Calculate difference between consecutive rows filtered by SELECT DISTINCT

[Anonymous]

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

[amitchandak · Accepted solution]

@Anonymous , Try a new column like

New column =
var _max = maxx(filter(Table, [PERMIT_ID] =earlier([PERMIT_ID]) && [ACTION DATE] <earlier([ACTION DATE])) , [ACTION DATE])
return
[WORKDAYS SINCE FILED] - maxx(filter(Table, [PERMIT_ID] =earlier([PERMIT_ID]) && [ACTION DATE] = _max) , [WORKDAYS SINCE FILED])

[Anonymous]

How do you get around the "EARLIER refers to an earlier row context which doesn't exist" problem? It's very stubborn.