Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
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!!
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
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 |