Forum Discussion
Cap Table Circular Reference
Hello All,
I need some help with a circular reference problem that I have been working on for a few weeks. I believe that this can be done, it is just that I am still in the process of learning power bi and I am really stuck. I have attached an example of my problem.
At the start of the month, values can be added or subtracted. There is a beginning month fund value which is equal to the previous month end value plus any additions and subtractions.
The end of the month fund value is an external input. However the customer's month end value is calculated using the % ownership of that customer from the previous month end.
The only values which are inputs are the following columns: Adds, Subs, and Month End Fund Value. Everything else is calculated which is how it becomes a circular reference problem.
Any suggestions or ideas would be really appreciated. Thank you so much.
| NOTES | ADDS and SUBS will be uploaded inputs | Ownership % on ME does not change from BM | From previous month end + adds + subs | For the purpose of this calculation, this is hard coded | ||||||||||
| Customer | Date | Adds | Subs | BM Cust Value | ME Cust Value | % Ownership | Beginning Month Fund Value | Month End Fund Value | CHECK | |||||
| C1 | 1/1/2017 | $ 50.00 | $ 50.00 | 47.6% | $ 105.00 | 100.0% | ||||||||
| C2 | 1/1/2017 | $ 25.00 | $ 25.00 | 23.8% | $ 105.00 | |||||||||
| C3 | 1/1/2017 | $ 30.00 | $ 30.00 | 28.6% | $ 105.00 | |||||||||
| C1 | 1/31/2017 | $ 50.48 | $ 106.00 | |||||||||||
| C2 | 1/31/2017 | $ 25.24 | $ 106.00 | |||||||||||
| C3 | 1/31/2017 | $ 30.29 | $ 106.00 | |||||||||||
| C1 | 2/1/2017 | $ 5.00 | $ 55.48 | 45.8% | $ 121.00 | 100.0% | ||||||||
| C2 | 2/1/2017 | $ 5.00 | $ 30.24 | 25.0% | $ 121.00 | |||||||||
| C3 | 2/1/2017 | $ 5.00 | $ 35.29 | 29.2% | $ 121.00 | |||||||||
| C1 | 2/28/2017 | $ 58.25 | $ 127.05 | |||||||||||
| C2 | 2/28/2017 | $ 31.75 | $ 127.05 | |||||||||||
| C3 | 2/28/2017 | $ 37.05 | $ 127.05 | |||||||||||
| C1 | 3/1/2017 | $ (7.00) | $ 51.25 | 45.7% | $ 112.05 | 100.0% | ||||||||
| C2 | 3/1/2017 | $ (5.00) | $ 26.75 | 23.9% | $ 112.05 | |||||||||
| C3 | 3/1/2017 | $ (3.00) | $ 34.05 | 30.4% | $ 112.05 | |||||||||
| C1 | 3/31/2017 | $ 59.64 | $ 130.40 | |||||||||||
| C2 | 3/31/2017 | $ 31.13 | $ 130.40 | |||||||||||
| C3 | 3/31/2017 | $ 39.63 | $ 130.40 | |||||||||||
| C1 | 4/1/2017 | $ 59.64 | 45.7% | $ 130.40 | 100.0% | |||||||||
| C2 | 4/1/2017 | $ 31.13 | 23.9% | $ 130.40 | ||||||||||
| C3 | 4/1/2017 | $ 39.63 | 30.4% | $ 130.40 | ||||||||||
| C1 | 4/30/2017 | $ 64.07 | $ 140.07 | |||||||||||
| C2 | 4/30/2017 | $ 33.44 | $ 140.07 | |||||||||||
| C3 | 4/30/2017 | $ 42.57 | $ 140.07 | |||||||||||
| C1 | 5/1/2017 | $ 5.00 | $ 69.07 | 44.5% | $ 155.07 | 100.0% | ||||||||
| C2 | 5/1/2017 | $ 5.00 | $ 38.44 | 24.8% | $ 155.07 | |||||||||
| C3 | 5/1/2017 | $ 5.00 | $ 47.57 | 30.7% | $ 155.07 | |||||||||
| C1 | 5/31/2017 | $ 65.51 | $ 147.08 | |||||||||||
| C2 | 5/31/2017 | $ 36.46 | $ 147.08 | |||||||||||
| C3 | 5/31/2017 | $ 45.11 | $ 147.08 | |||||||||||
| C1 | 6/1/2017 | $ 65.51 | 44.5% | $ 147.08 | 100.0% | |||||||||
| C2 | 6/1/2017 | $ 36.46 | 24.8% | $ 147.08 | ||||||||||
| C3 | 6/1/2017 | $ 45.11 | 30.7% | $ 147.08 | ||||||||||
| C1 | 6/30/2017 | $ 68.78 | $ 154.43 | |||||||||||
| C2 | 6/30/2017 | $ 38.28 | $ 154.43 | |||||||||||
| C3 | 6/30/2017 | $ 47.37 | $ 154.43 |
10 Replies
ARob198 , refer if this can help
https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/
I think the DAX formulas will have to be provided and relationships ( if there are more than one table) so we can see what's going on.
Also, have you tried calculating the columns in Power Query?
There is not another table, nor are there any DAX formulas. I am happy to share this excel file if you tell me how to do that on the forum. I am trying to recreate this table and calculations in DAX. What is the difference between doing the calulcations as measures in the desktop and doing them in Power Query Editor? I was under the impression that calculations should not be done in Power Query Editor if at all possible.
Thank you for your help
