Starting a calculation/ Circular Reference

[ARob198]

Hello there,  I am trying to create a Cap Table in Power BI. Perhaps I am making this too complicated so I am hoping someone may be able to help.

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.

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.

[sanimesa · Accepted solution]

@ARob198  You can actually keep a single table of running contributions (positive and negative) and achieve this by using cumulative values from the beginning of time. You have to initialize the table only once and then every month you just keep adding the contributions.

Then you can use measures such as below examples. You will need to modify the measures to account for the company name.

See the picture as it appears at my end.

CUMULATIVE AMOUNT = 
CALCULATE (
    SUM ( Shares[AMOUNT] ),
    FILTER (
        ALL ( Shares[DATE] ),
        Shares[DATE] <= MAX ( Shares[DATE] )
    )
)

COMPANY VALUE = 
CALCULATE (
    SUM ( Shares[AMOUNT] ),
    FILTER (
        ALL ( Shares ),
        Shares[DATE] <= MAX ( Shares[DATE] )
    )
)

% OWNERSHIP = DIVIDE([CUMULATIVE AMOUNT], [COMPANY VALUE])

[v-gizhi-msft · Accepted solution]

Hi,

Hope this can help:

See my attached pbix file.

Best Regards,

Giotto

[ARob198]

v-gizhi-msft

Hello,  I have been trying to get this to work for my data.  I feel like I am getting really close.  My table is almost there, except that I need the ME Value prior to pick up the value from the previous month end.  For example, the ME Value M from 3/31/17 needs to show up in the ME Prior Val column for 4/1/2017.  I think once I get this, the other #s will update automatically.  Do you have any suggestions?

When I try to use the formula that you used, I am getting an error.  I am also a bit confused when I should be using measures for the calculations vs calculated columns.

Thank you so much

[ARob198]

When I try this, I am getting a syntax error in which DAX can not reference my tables and values.  For example, it doesn't seem to like EARLIER('FundCustInp'[NAME DETAIL] or 'FundCustInp'[DATE] even though my table is named FundCustInp and the column is named NAME DETAIL or DATE.

New Value = var a = CALCULATE(MAX('FundCustInp'[VALUE], FILTER(ALLSELECTED('FundCustInp'), 'FundCustInp'[NAME DETAIL] = EARLIER('FundCustInp'[NAME DETAIL])&& 'FundCustInp'[DATE] = EARLIER('FundCustInp'[DATE]-1)) return IF(DAY('FundCustInp'[DATE])=1&& 'FundCustInp'[DATE]<>MAX('FundCustInp'[DATE]), a+'FundCustInp'[ADDITIONS]+'FundCustInp'[SUBTRACTIONS], 'FundCustInp'[VALUE])

The syntax for 'return' is incorrect. (DAX(var a = CALCULATE(MAX('FundCustInp'[VALUE], FILTER(ALLSELECTED('FundCustInp'), 'FundCustInp'[NAME DETAIL] = EARLIER('FundCustInp'[NAME DETAIL])&& 'FundCustInp'[DATE] = EARLIER('FundCustInp'[DATE]-1)) return IF(DAY('FundCustInp'[DATE])=1&& 'FundCustInp'[DATE]<>MAX('FundCustInp'[DATE]), a+'FundCustInp'[ADDITIONS]+'FundCustInp'[SUBTRACTIONS], 'FundCustInp'[VALUE])))

Thank you so much for your help

[ARob198]

Hi Giotto,

I really appreciate your response.  However, I guess I just don't understand how to start this still.  I have an excel file that is uploaded that has the additions/subtractions.  Those will be updated every month when the data is refreshed.  How do I build the table?  Did you create it in Query Editor and enter the data?  I guess I am missing the step to start this?

Thank you

[sanimesa · Accepted solution]

@ARob198  You can actually keep a single table of running contributions (positive and negative) and achieve this by using cumulative values from the beginning of time. You have to initialize the table only once and then every month you just keep adding the contributions.

Then you can use measures such as below examples. You will need to modify the measures to account for the company name.

See the picture as it appears at my end.

CUMULATIVE AMOUNT = 
CALCULATE (
    SUM ( Shares[AMOUNT] ),
    FILTER (
        ALL ( Shares[DATE] ),
        Shares[DATE] <= MAX ( Shares[DATE] )
    )
)

COMPANY VALUE = 
CALCULATE (
    SUM ( Shares[AMOUNT] ),
    FILTER (
        ALL ( Shares ),
        Shares[DATE] <= MAX ( Shares[DATE] )
    )
)

% OWNERSHIP = DIVIDE([CUMULATIVE AMOUNT], [COMPANY VALUE])