Cap Table Circular Reference

[ARob198]

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

@v-lionel-msft

[amitchandak]

@ARob198 , refer if this can help

https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/

[HotChilli]

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?

[ARob198]

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 HotChilli

[HotChilli]

I'm slightly confused.  Maybe there is a misunderstanding about terminology.

There's a circular reference error (that's the title of the post).  There are no other tables.  The circular reference must be coming from the DAX formula for a calculated column (probably based on another calculated column).  So i'd like to see the DAX formula for (any) calculated columns.  Let me know if I've got that wrong.

Power Query is an excellent tool and works beautifully for this sort of thing.

The difference between calculated columns and measures->

https://radacad.com/measure-vs-calculated-column-the-mysterious-question-not 

[ARob198]

Sure- how do I share a file with you?

Thank you

[HotChilli]

You can upload to a 3rd party sharing site and post the link (or send it the link via messages - on this site-  to me if you don't want it public)

[ARob198]

Here is my link to the file with the circular reference.  

link: 

The beginning of the month values are based on the values from the previous month end.  So values on 2/1/2017 will be pulled from the values on 1/31/2017 then any new additions or subtractions are added and the % ownership is calculated.  The month end values are calculated using the % ownership from the start of the month multiplied by the month end value for the fund.  Please let me know if you have any other questions.

Thank you for your help.

[ARob198]

@ v-lionel-msft

@v-gizhi-msft

I have posted the PBIX file.  Is it possible for you to take a look?  I believe there is a way to do this, I am just unsure of how to format the examples that I have found to fit my data.  Here is one example: https://www.sqlbi.com/articles/computing-the-future-value-of-an-investment-based-on-compound-growth-...

Would adding a period identifier help?  For example, January could be period 1, Feb period 2, etc?

Thank you

Thank you so much!

[HotChilli]

I see these are not columns, these are measures.

The circular reference is caused by measure 

ME Value

depending on

BM Value 2

which depends on

ME Value

(the chain is a little more complicated than that but that's what it boils down to)

---

it's quite difficult to unpick what is trying to be achieved with these measures. I couldn't get it.

It might be better to simplify the dataset to 1 or 2 customers with 1 or 2 dates and explain what the

desired outcome is by manually calculating the figures and showing how they are arrived at.

[ARob198]

Sure- that is what I was trying to explain in the first post in this thread.  I have attached the excel file behind the picture in the first post.  Here is the link:  link 

It is already a very simplified version of the excel model that I need to use.  I know that this is possible in Power BI as I have found examples of people creating sort of similar models like the link in the posting above.  I am just very new at DAX/Power BI so I am not quite sure how to create them with my data.  Any thoughts or suggestions that anyone has would be very helpful.

Thank you so much.