First of Month/End of the Month- Cap Table

[ARob198]

Hello,  I would really appreciate some help as I am a little stuck on a problem, I am creating a cap table in Power BI.  I don't really see any postings about these in power BI.  Perhaps someone has a link to some?

At the end of the month, I need to look back to changes that happened at the beginning of the month.  Should I have two seperate tabs in power bi for my calculations?  I am not sure how to set this up.  Should I create two seperate tabs one for the start of the month calculations and one for the end of month calculations?  In addition, I am not sure how to start the tables.

Thank you fo much for your help!  I have created an example below.

	Value	Additions	Subtractions	% Value	Fund Value
Jan 1 2020	$    10.00	$      1.00		10.00%	100
Jan 31 2020	$    10.20			10.00%	102
Feb 1 2020	$      9.70		$         (0.50)	9.51%	102
Feb 29 2020	$      9.80			9.51%	103
Mar 1 2020	$    10.55	$      0.75		10.24%	103
Mar 31 2020	$    10.14			10.24%	99

For all beginning of the month calculations, the value is equal to the previous month end's value plus any addition, plus any subtractions.  The % value is then the Value/Fund Value.  So $10 is the Dec 31 value of $9 plus the $1 addition.  % Value of 10% is equal $10/$100.

For all end of month calculations, the value is equal to the % value at the beginning of the month * the fund value. Jan 31 value of $10.20 is taking the fund value of $102 * 10% from Jan 1.

[ARob198]

My initial thought was to set up two different tables, one that holds the values for the first day of each month and one that holds the values for the last day of the month.  This way I can create measures that calculate for all of the dates in the tables. See below.

Table 1	Value	Additions	Subtractions	% Value	Fund Value			Table 2	Value	% Value	Fund Value
Jan 1 2020	$    10.00	$      1.00		10.00%	$  100.00			Dec 31 2019	$      9.00	10.00%	$  100.00
Feb 1 2020	$      9.70		$         (0.50)	9.51%	$  102.00			Jan 31 2020	$    10.20	10.00%	$  102.00
Mar 1 2020	$    10.55	$      0.75		10.24%	$  103.00			Feb 29 2020	$      9.80	9.51%	$  103.00
								Mar 31 2020	$    10.14	10.24%	$    99.00

However, I am not sure how to start the calculations if that makes sense.  I am coming from Excel where I would just have hard coded values that can be the basis to start the calculations.  Where do hard coded numbers live in power bi?  I would really appreciate any feedback or ideas that you might have.  Thank you!!

[v-yiruan-msft]

Hi @ARob198 ,

Whether the value of the field "Additions","Subtractions" and "Fund Value" the are fixed values? Could you please provide the formulas applied in your excel sheet? Thank you.

Best Regards

Rena

[ARob198]

Thank you for your reply and thank you very much for your help in solving this. Any thoughts or ideas are very welcome.  If anyone had links to any cap tables in Power BI that would also be helpful.

The additions and subtractions are inputs that will be input into excel and uploaded into power BI each period.  I had a typo and changed to the example to clarify.  The additions and subtractions are inputs but the tables need to build off each other.  Hopefully this will help to clarify what I am trying to do.  I have highlighted the input values in Blue.  The table formulas are below.  Where should the value for Dec 31 2019 of $9 live to start the table?  How can I create these measures that relate to each other?  Please let me know if this makes sense.

Start of Month								End of Month
Table 1	Value	Additions	Subtractions	% Value	Fund Value			Table 2	Value	% Value	Fund Value
Jan 1 2020	$            10.09	$             1.00		9.99%	$                   101.00			Dec 31 2019	$              9.00	9.00%	100.00
Feb 1 2020	$              9.64		$          (0.50)	9.50%	$                   101.50			Jan 31 2020	$            10.19	9.99%	102.00
Mar 1 2020	$            10.60	$             0.75		10.22%	$                   103.75			Feb 29 2020	$               9.78	9.50%	103.00
								Mar 31 2020	$            10.12	10.22%	99.00
	(End of Previous Month % Value * Start of Month Fund Value) + Additions + Subtractions			Value / Fund Value	End of Previous Month Fund Value + Additions + Subtractions				First value is hard coded, subsequent values are the Start of Month % Value * End of Month Fund Value	Pulled from % Value at Start of Month