Convert Excel function to DAX - Power BI (Circular Reference Error)

[csinha]

Hello,
I need help converting this excel sheet to dax in Power BI. 

Here are the formulas used in the sheet ( split into two parts):

and finally, here is the Excel sheet with the aforementioned data

Scenario:
This is a fairly typical in finance - debt/loan amortization table, although it is easy to make in excel, I am not able to replicate it in Power BI using DAX. 
I am enountering circular reference errors when I try to replicate these excel functions in Power BI. I think it is due to the fact there are several recursive forumulas in the excel sheet. I'm not sure how to execute them in Power BI. 

Notes:
1. Column A, B, C are the input columns (marked in yellow). 

2. All the other columns are calculated using formlae (marked in blue).

3. The first three of the four input columns are bank statement entries and the remaining fourth one is the daily interest rate. Based on these four columns, we calculate the other columns marked in blue. 

The setup:
I'll do my best to elaboate on the nature of the relationships of the columns in the excel sheet, although it is way easier to follow the functions in the actual attached excel sheet.
1. Column E is dependent on column G, although it references different rows.
2. Column G is the difference of Column F and G.
3. Column G is inturn dependent on Column E.
4. Column F is depenent on Column J and L.
5. Column J in turn needs values from column G, and column L is dependent on column K, which again is inturn dependent on J and column H.
6. Column H uses values from column J and column L.

Here is a Power BI file with the input columns preloaded into the file.

Please help me with this task. I am new to Power BI and DAX and I deliver upon this urgently.

Regards.

[csinha]

Hey, I believe the data set has self referencing columns rather than recursive functions.
I identified the problem incorrectly.

@v-rzhou-msft @v-sihou-msft I've read other community forum posts where you've addressed a similar issue. Could you take a look at this data set too, you might be able to come up with something.

I open to everything from DAX solutions, to Power Query solutions, or even creative ways to restrcuture the data. 

Please do give it a go, you'd be doing me huge favour!

[v-shex-msft]

Hi @csinha,

For the circular reference issue, you can take a look at the following in to know more about this:

Avoiding circular dependency errors in DAX 

BTW, current power bi reservation calculation can only achieve on the rolling calculations. (apply rolling calculations based on specific index filed and aggregate functions)
AFAIK, power bi data model table does not exist row/column index. If you want to invoke previous calculations, you need to manually/hard code to config correspond filters to get specific row calculations to result and it will increase on each iteration. (it same as Greg_Deckler 's link mentioned)

Regards,

Xiaoxin Sheng

[rrathod]

@amitchandak  Hi Amit,  I have seen a lot of solution provided by you and learned alot from it and we are really thanks for that. Me and my team is stucked at the situation here and it would be really helpfull if you guide us here.

[Greg_Deckler]

@csinha  You cannot do either looping or recursion in DAX. Believe me I have tried. You will need to use Power Query to do the recursion.

Here is a thing on Recursion in DAX - https://www.linkedin.com/pulse/previous-value-recursion-dax-greg-deckler-microsoft-mvp-/

Here is a thing on Recursion in Power Query - https://www.linkedin.com/pulse/fun-graphing-power-bi-part-5-by-5-greg-deckler-microsoft-mvp-/

[csinha]

Hey @Greg_Deckler,
Thanks a lot for replying. I read your post on the Fibonacci Numbers just today, and abolsutely loved it!
Although I do suspect that the issue is recursive functions, I am not sure of it. I think I am way too new to Power BI and DAX to even identify it correctly. 
I'd be extremly grateful if you could you please go through the excel file, you just might find a solution. I'm really desperate here. 

Thanks a ton!

[Greg_Deckler]

@csinha Thanks, I've been fighting recusion in DAX for forever. I'll try to take a look but could take some time to try to unwind it all and see if there are any "creative" solutions!

[csinha]

I realised the first picture of the sheet didnt make it into the first post. here is sheet I need to replicate in Power BI with DAX.