Circular reference problem

[didijaba]

Hello, pls help. I have two column, A and B in Power Pivot data model. I will try to make it simple so I'll use Excel cell reference.
Formula in A column of data model is for A1: 2+B1, A2: 2+B2, ....
Formula for of data model B1=0, for B2=sum(A$1:A1), for B3=(A$1:A2), .... Problem is I set DAX formula in A and B, but I get circular reference error. is there any way around it. I used COUNTROW to determine first row where B1=0, and FILTER, SUMX for summing values. How to get around this problem??
Thanks,

 

[gsistek]

The best way to fix the problem is to understand what it is you are trying to accomplish. From what I can tell, column 'A' will contain powers of 2 (2,4,8,16,...) and column 'B' is column 'A' - 2. How were you going to use this in PowerPivot? What is the ultimate goal?

 

[didijaba]

Sorry, but no. I want to see interest from iregular payments. This is file https://www.dropbox.com/s/w4udylo3ydskrck/DAX_LOAN.rar?dl=0

 

[didijaba]

This can be done all in one calculated column, but I do not know how to sum calculated values from earlier rows in that calculated column?

 

[didijaba]

I was answered at Understanding Circular Dependencies in Tabular and PowerPivot – SQLBI). And to explain it a litlle more see https://cwebbbi.wordpress.com/2011/05/01/recursive-calculation-problems/

 

[scottsen]

So, you are good? I wasn't super sure if you were just trying to create a "running total" by your ID column? Which is easier than you are making it...

 

[didijaba]

Thanks, I wanted to see if recursive calculation can be done using DAX. If you have idea how to do this, I'm all ears .

 

[scottsen]

Nope, no way to do it. It makes forward projection style calcs incredibly difficult. FY16 is [whatever was in FY15] + [new interest] + [new business]... no good way to do this. The calc can be done, but it has to be thought of as "in any given year what are ALL the things that feed into this year..." which is un-fun.