Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
ARob198
Helper IV
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/20201/31/20202/1/20202/1/20202/1/20202/1/20202/29/20202/29/20203/1/20203/1/20203/1/20203/1/20203/31/20203/31/2020
CustomerValue% OwnershipAdditionsSubtractionsValue% OwnershipValue% OwnershipAdditionsSubtractionsValue% OwnershipValue% Ownership
T $          8.4333% $        0.50  $        8.9335% $          9.2935% $        0.50  $        9.7937% $          9.8437%
U $          8.4333%  $         (0.25) $        8.1832% $          8.5132%  $         (0.30) $        8.2131% $          8.2531%
V $          8.4333%   $        8.4333% $          8.7733%   $        8.7733% $          8.8233%
Totals $       25.30100% $       0.50 $        (0.25) $    25.55100% $       26.57100% $       0.50 $        (0.30) $    26.77100% $       26.91100%

 

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 DETAILDATEVALUE % OWNERSHIP  Company Company Value  
T1/31/20208.4333%Blue25.3  
U1/31/20208.4333%Blue25.3  
V1/31/20208.4333%Blue25.3  
T2/29/2020      
U2/29/2020      
V2/29/2020      
T3/31/2020      
U3/31/2020      
V3/31/2020      
        
Beginning of the Month Table      
NAME DETAILDATEAdditionsSubtractions  % OWNERSHIP  Company Company Value
T2/1/20200.50  Blue25.3
U2/1/20200-0.25  Blue25.3
V2/1/20200   Blue25.3
T3/1/20200.50  Blue26.57
U3/1/20200-0.3  Blue26.57
V3/1/20200   Blue26.57
T4/1/2020    Blue26.91
U4/1/2020    Blue26.91
V4/1/2020    Blue26.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
sanimesa
Post Prodigy
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])

 

 

PBI Help shares percentage cumul.png

View solution in original post

v-gizhi-msft
Community Support
Community Support

Hi,

 

Hope this can help:

8.PNG

See my attached pbix file.

 

Best Regards,

Giotto

View solution in original post

5 REPLIES 5
v-gizhi-msft
Community Support
Community Support

Hi,

 

Hope this can help:

8.PNG

See my attached pbix file.

 

Best Regards,

Giotto

 

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 muchCapture.PNGERROR.PNG

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

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

 

 

PBI Help shares percentage cumul.png

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.