subtract row values (where values are measure) *circular dependency issue*

[elf_ranger91]

Hi Guys,

I have a table with 3 columns as shown below.  The column "sales gap (usd) column)" is a measure that has a relationship (that is to say, has a connection) with "remaining months to recognize" but the measure is not directly dependent on it.

Now I want to find the difference between the row values in the "sale gap (usd)" column. I add a new column and write this expression: 

Diff. in Sales Gap =

             'New Deals Goal'[Sales Gap (USD) column] -

                     IF('New Deals Goal'[Index]=0; 'New Deals Goal'[Sales Gap (USD) column];

                                                     LOOKUPVALUE('New Deals Goal'[Sales Gap (USD) column];'New Deals Goal'[Index];'New                                                                                  Deals Goal'[Index]-1))

As seen below:

 I have a circular dependency issue. I have read about it and I understand the problem, but how do I solve this?

Thank you. 

[Baskar · Accepted solution]

Hi,

Try this one .

Column =
var A= CALCULATE( MAX('New Deals Goal'[Index]))-1
return
If('New Deals Goal'[Index]=0,
'New Deals Goal'[Sales Gap (USD) column],
'New Deals Goal'[Sales Gap (USD) column] - CALCULATE(SUM('New Deals Goal'[Sales Gap (USD) column]),FILTER('New Deals Goal','New Deals Goal'[Index]=A))
)