Circular Reference problem

[ARob198]

Hello there,  I am actually reposting as I marked the responses as a solution, but they aren't actually working for me. I am still stuck in a circular reference that isn't solved by the solutions that were posted.  Can anyone help?

Here is the post:

I am trying to create a Cap Table in Power BI.

I have three people who all have interests in the fund and are able to make additions and subtractions on the first day of the month (buy more shares or sell more shares in the company).  In excel, the table is pretty straight foward and looks like this:

Blue Fund
	1/31/2020	1/31/2020	2/1/2020	2/1/2020	2/1/2020	2/1/2020	2/29/2020	2/29/2020	3/1/2020	3/1/2020	3/1/2020	3/1/2020	3/31/2020	3/31/2020
Customer	Value	% Ownership	Additions	Subtractions	Value	% Ownership	Value	% Ownership	Additions	Subtractions	Value	% Ownership	Value	% Ownership
T	$          8.43	33%	$        0.50		$        8.93	35%	$          9.29	35%	$        0.50		$        9.79	37%	$          9.84	37%
U	$          8.43	33%		$         (0.25)	$        8.18	32%	$          8.51	32%		$         (0.30)	$        8.21	31%	$          8.25	31%
V	$          8.43	33%			$        8.43	33%	$          8.77	33%			$        8.77	33%	$          8.82	33%
Totals	$       25.30	100%	$       0.50	$        (0.25)	$    25.55	100%	$       26.57	100%	$       0.50	$        (0.30)	$    26.77	100%	$       26.91	100%

The only numbers that are "hardcoded" and uploaded are the values on Jan 31,2020 and the additions or subtractions at the start of every month (IN BLUE).  The additions/subtractions will be added when the dashboard is refreshed.  However, I don't know how to start the calculations.  I set this up in two different tables as the calculations at the beginning of the month and the end of the month are different.  This way all of the beginning of the month calculations can be applied to an entire table and end of month calculations that are different can also be applied to the entire table.  However, I am trouble getting the calculations started.   My tables in the dashboard look like this (but I am open to suggestions!)  I am not concerned with creating this visual, I am worried aobut getting the calculations correct.  The month end calculations depend on the start of month calculations, and the start of month calculations depend on the end of month calculations from the prior month.  The company value is pulled from elsewhere in the model.  Again, the inputs are in blue below, I would like the rest of the table to populate as new data is added/refreshed.

The formulas are as follows:  The values on month end (ie 1/31/2020) = the % ownerhsip from the start of the month * company value for that date

The values on the begining of the month (ie 2/1/2020) = value from 1/31/2020 + additions for 2/1/2020 + subtractions for 2/1/2020

Then there is a new ownership % that is calculated which is the value for 2/1/2020 divided by the company value for 1/31/2020

Month End Table
NAME DETAIL	DATE	VALUE	% OWNERSHIP	Company	Company Value
T	1/31/2020	8.43	33%	Blue	25.3
U	1/31/2020	8.43	33%	Blue	25.3
V	1/31/2020	8.43	33%	Blue	25.3
T	2/29/2020
U	2/29/2020
V	2/29/2020
T	3/31/2020
U	3/31/2020
V	3/31/2020
Beginning of the Month Table
NAME DETAIL	DATE	Additions	Subtractions		% OWNERSHIP	Company	Company Value
T	2/1/2020	0.5	0			Blue	25.3
U	2/1/2020	0	-0.25			Blue	25.3
V	2/1/2020	0				Blue	25.3
T	3/1/2020	0.5	0			Blue	26.57
U	3/1/2020	0	-0.3			Blue	26.57
V	3/1/2020	0				Blue	26.57
T	4/1/2020					Blue	26.91
U	4/1/2020					Blue	26.91
V	4/1/2020					Blue	26.91

Does anyone have any ideas in helping to recreate this?  Perhaps I am thinking about designing this wrong?  Any input would be greatly appreciated.

Thank you so much.

[v-lionel-msft]

Hi @ARob198 ,

Here's my data model(No table relationship).

1. Create 3 calculated columns in 'Month End Table'.

Beginning Date = 
DATE(
    YEAR('Month End Table'[DATE]) , MONTH('Month End Table'[DATE]) + 1, 1
)

_Additions = 
LOOKUPVALUE(
    'Beginning of the Month Table'[Additions],
    'Beginning of the Month Table'[NAME DETAIL], 'Month End Table'[NAME DETAIL],
    'Beginning of the Month Table'[DATE], 'Month End Table'[Beginning Date]
)

_Subtrations = 
LOOKUPVALUE(
    'Beginning of the Month Table'[Subtractions],
    'Beginning of the Month Table'[NAME DETAIL], 'Month End Table'[NAME DETAIL],
    'Beginning of the Month Table'[DATE], 'Month End Table'[Beginning Date]
)

2. Create measures.

Values on the begining of the month = 
VAR x = 
SELECTEDVALUE('Month End Table'[VALUE]) + SELECTEDVALUE('Month End Table'[_Additions]) + SELECTEDVALUE('Month End Table'[_Subtrations])
RETURN
x

% OWNERSHIP = 
DIVIDE(
    [Values on the begining of the month],
    SELECTEDVALUE('Month End Table'[Company Value])
)

Values on month end = 
[% OWNERSHIP] * SELECTEDVALUE('Month End Table'[Company Value])

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ARob198]

@v-lionel-msft 

I have spent the last few days trying to make this work....How do a calculate the 'Month End Table' [VALUE] if it is not a static input?  I think you are taking it as a static input.  It is always pulled from the beginning of the month.  I guess where I am really stuck is that all the steps work until the end and then I get the circular reference again.  Any ideas that you have would be really appreciated.  This is really frustrating.

[v-lionel-msft]

Hi @ARob198 ,

I don't know where the problem is, maybe you can look at my .pbix file and point out the difference between our models.

.pbix file 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ARob198]

@v-lionel-msft 

Thank you very much.  I think I finally got the circular reference (maybe?) but- I am stuck on how to get ME Prior Val.  Or maybe this error is keeping me from getting the circular dependency error. I have tried dateadd, previousmonth, etc.  I am not sure if the dates are not working properly becuase the tables aren't connected (in order to allow for the circular dependency.)  If you look at the row for 3/1/17, the ME Prior should be 0.  For 4/1/17, the ME Prior Val should be 200,006.78.

[ARob198]

Hi Lionel,

I think this gets me part of the way there, maybe?  But it doesn't really solve my problem fully.  Unfortunately, I am still stuck in circular references.  I am getting an error that circular dependency was detected.  I know this can be done- I am just getting sucked into a black hole here!

Thank you for your help!

Values on the begining of the month = 
VAR x = 
SELECTEDVALUE('Month End Table'[VALUE]) + SELECTEDVALUE('Month End Table'[_Additions]) + SELECTEDVALUE('Month End Table'[_Subtrations])
RETURN
x

  'Month End Table'[VALUE] is a value that starts with the input numbers for 12/31/2016 but that is all that will be filled in for that.  The important numbers will be the BM Value and the ME Value.  The ME Value is calculated based ownership % from the start of the month * the fund value at month end.  

BM Value = ME Value (for previous day) + ADDS + SUBS

Ex: Feb 1 value = (ME Value for Jan 31) + ADDS (on Feb 1) + SUBS (on Feb 1)