Calculation IF a value appears in earlier rows

Question

I have a set of data including RMAs with various dates. I would like to create a measure which calculates if a particular Serial Number has been returned multiple times, and IF it does, to calculate the date difference between the most recent Actual Ship Date and the current Date Submitted.

Link to sample .pbix file

For example:

Serial num	Date submitted	Date actual ship	IF multiple, Days between
x23432	1/3/2021	1/25/2021	NA
x85423	3/4/2021	4/2/2021	NA
x23432	6/4/2021	6/17/2021	141(6/4/2021-1/25/2021)
x96423	5/3/2021	5/26/2021	NA
x85423	7/2/2021	7/19/2021	92 (7/2/2021-4/2/2021)
x23432	9/6/2021	10/2/2021	82 ( 9/6/2021-6/17/2021)

lbendlin · Answer

Thank you for providing the sample data
&nbsp;
&nbsp;
If multiple, Days between = 
var ds=SELECTEDVALUE(RMA[Date Submitted])
var pasd = CALCULATE(max(RMA[ACTUAL_SHIP_DATE]),ALLEXCEPT(RMA,RMA[Serial Num]),RMA[ACTUAL_SHIP_DATE]&lt;ds)
return DATEDIFF(pasd,ds,DAY)
&nbsp;
&nbsp;

&nbsp;

ashish_mathur · Answer

Hi,
This should typically be a calculated column formula.&nbsp; You may download my PBI file from here.
Hope this helps.

&nbsp;

swisdom · Answer

Thank you for your response!So I can see that this is working on your pbix file, but for some reason when I apply it to my own file, it does something weird and gives values for entries that shouldn't have them (see below)The ones highlighted in green are&nbsp;correct, but the rest I believe should be blank?&nbsp;Any idea what is going wonky?

swisdom · Answer

Hello thank you for your response!So your method looks great, however when I try to apply it to my actual dataset (not the test dataset) I get this error: "Circular dependency was detected"(I split up the code a bit just for myself so I could read it easier)&nbsp;Any ideas why I would get that error?