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

    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

  • ARob198's avatar
    ARob198
    Helper IV
    6 years ago

    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'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