Forum Discussion

ARob198's avatar
ARob198
Helper IV
6 years ago
Solved

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...
Need Help
Tutorial Requests
  • sanimesa's avatar
    sanimesa
    6 years ago

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

     

     

ARob198's avatar
ARob198
Helper IV
6 years ago

 

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