cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

Starting a calculation/ Circular Reference

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.

2 ACCEPTED SOLUTIONS
Post Prodigy

@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])``````

Community Support

Hi,

Hope this can help:

See my attached pbix file.

Best Regards,

Giotto

5 REPLIES 5
Community Support

Hi,

Hope this can help:

See my attached pbix file.

Best Regards,

Giotto

Helper IV

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

Helper IV

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
Helper IV

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

Post Prodigy

@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])``````