Help fixing circular dependency

[ConnorH]

I am trying to find the difference between each row given that project IDs are the same and the rank is in order.

[Rank] = Rankx(filter('Final Cost Log', 'Final Cost Log'[ProjectID] = EARLIER('Final Cost Log'[ProjectID])), 'Final Cost Log'[Index])

[Difference] = 
var preValue = CALCULATE(VALUES('Final Cost Log'[Final Cost]), FILTER('Final Cost Log','Final Cost Log'[ProjectID] = EARLIER('Final Cost Log'[ProjectID]) && 'Final Cost Log'[Rank] = (EARLIER('Final Cost Log'[Rank]))-1))
Var difference = 'Final Cost Log'[Final Cost] - preValue
return difference

When trying pressing enter for the [Difference] column I recieve this error "A circular dependency was detected: Final Cost Log[Difference]"

ProjectID	Final Cost	Modified	Index	Rank	Difference
15008	0	10/2/21	22	1	0
15008	1500	10/8/21	120	2	1500
15008	1400	10/25/21	462	3	(100)
15008	7500	11/3/21	1053	4	6100
15008	7500	11/4/21	1134	5	0
15008	0	11/6/21	1372	6	(7500)

[VahidDM · Accepted solution]

Hi @ConnorH 

Try this code to add a new column:

Difference = 
Var _Previous = CALCULATE(sum([Final Cost]),filter(all('Final Cost Log'),[Rank]=EARLIER('Final Cost Log'[Rank])-1))
return
[Final Cost]-_Previous

Update your RANK column code with this:

Rank = Rankx(filter('Final Cost Log', 'Final Cost Log'[ProjectID] = EARLIER('Final Cost Log'[ProjectID])), 'Final Cost Log'[Index],,ASC)

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[ConnorH]

Thank you so much!