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.
I am trying to calculate an XIRR function in Power BI but I am having difficulties. Specifically, we are looking to create a dynamic IRR function in Power BI that would allow us to calculate the IRR for each investment based on a certain period end – for example, would like to calculate a since inception to-date XIRR for “investment A” but also have the ability to back into prior period XIRRs as well. As you can see, we have multiple underlying investments, each which has their own specific IRR calculation and that is where we are getting tripped up. Basically, the end goal is to be able to choose a date from a drop down, and have a table showing each investment and their respective IRR based on the chosen date. One thing to note is that the data includes a “Commitment” Type, which needs to be excluded from the calculation as well. Any tips or help is greatly appreciated.
Here is some Sample Data. Let me know if there is anything else I can provide to help the process.
Security | Type | Value | Trade Date |
Investment A | Commitment | 100 | 1/1/2022 |
Investment A | Contribution | -50 | 8/31/2022 |
Investment A | Valuation | 50 | 9/30/2022 |
Investment A | Income | 5 | 11/30/2022 |
Investment A | Distribution | 25 | 12/15/2022 |
Investment A | Valuation | 23 | 12/31/2022 |
Investment A | Contribution | -50 | 2/1/2023 |
Investment A | Distribution | 5 | 3/15/2023 |
Investment A | Valuation | 70 | 3/31/2023 |
Investment A | Distribution | 15 | 4/15/2023 |
Investment B | Commitment | 500 | 1/1/2022 |
Investment B | Contribution | -200 | 8/15/2022 |
Investment B | Contribution | -300 | 1/15/2023 |
Investment B | Distribution | 100 | 9/15/2022 |
Investment B | Income | 50 | 10/31/2022 |
Investment B | Valuation | 103 | 9/30/2022 |
Investment B | Valuation | 55 | 12/31/2022 |
Investment B | Valuation | 275 | 3/31/2023 |
Investment B | Distribution | 100 | 2/15/2023 |
Investment C | Commitment | 50 | 1/1/2022 |
Investment C | Valuation | 15.5 | 12/31/2022 |
Investment C | Valuation | 16 | 3/31/2023 |
Investment C | Valuation | 10 | 6/30/2023 |
Investment C | Contribution | -30 | 10/15/2022 |
Investment C | Contribution | -20 | 4/15/2023 |
Investment C | Distribution | 15 | 11/15/2022 |
Investment C | Distribution | 25 | 5/15/2023 |
Another thing to add: I only want to include one Valuation, specifically the one from the period that I have selected. All other "Valuations" should be excluded from the calculation.
User | Count |
---|---|
48 | |
41 | |
19 | |
14 | |
13 |
User | Count |
---|---|
102 | |
55 | |
28 | |
19 | |
14 |