XIRR Calculation with Filters
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
Measure XIRR in DAX
Hello all, I have a problem with the Measure XIRR. The calculation is correct if I only access values, date and category in a table. If I then slice the table over another table with a 1:n connection the following error occurs: MdxScript(Model) (106, 34) Calculation error in measure 'Med DAX'[XIRRF]: The XIRR function couldn't find a solution. Apparently it is not possible for me to slice the table with another table. Only if I activate a value in the slicer for the table of the IRR, a result is displayed again. This is also possible for a multiple selection. Does anyone else know this error and can suggest a solution? Or give further support? Many thanks and best regards Carsten
XIRR
Transaction Listing file contains Closing Balance and Monthly Balance for each calendar month. With Standard XIRR DAX I could calculate monthly XIRR but Cumulative is not correct as Opening and Closing balance of interim period should not be considered while calculating XIRR. For example, Opening Balance of 1st Jan (First Day of the year) and CLosing Balance of 31st March (If i am calculating for 31st March) should be considered and Opening Balance of 1st Feb and 1st March and Closing Balance of 1st Feb and 1st March should be ignored. So for cumulative i want to consider 1) Opening Balance of 1st Day of the Month, 2) Closing Balance of the Month for which Cumulative XIRR is calculated and 3) All transactions between and including this period but excluding Transaction Type Opening Balance and Closing Balance. Tranaction Type Transaction Date XIRR Amount Purchase 01-Jan-20 -3151290 Sell 02-Jan-20 1575995 Purchase 03-Jan-20 -3467640 Sell 04-Jan-20 1891704 Purchase 05-Jan-20 -3783948 Sell 06-Jan-20 2207590 Purchase 07-Jan-20 -4100382 Sell 08-Jan-20 2523632 Purchase 09-Jan-20 -4416944 Sell 10-Jan-20 2839725 Purchase 11-Jan-20 -4733550 Sell 12-Jan-20 3156140 Purchase 13-Jan-20 -5050384 Sell 14-Jan-20 3472557 Purchase 15-Jan-20 -5367359 Sell 16-Jan-20 3789264 Purchase 17-Jan-20 -5684670 Sell 18-Jan-20 4106180 Purchase 19-Jan-20 -6002195 Sell 20-Jan-20 4423328 Purchase 21-Jan-20 -6319960 Sell 22-Jan-20 4740630 Purchase 23-Jan-20 -6637806 Sell 24-Jan-20 5058096 Purchase 25-Jan-20 -6955872 Sell 26-Jan-20 5375757 Purchase 27-Jan-20 -7274095 Sell 28-Jan-20 5693580 Purchase 29-Jan-20 -7592520 Sell 30-Jan-20 6011676 Closing Balance 31-Jan-20 31644600 Purchase 31-Jan-20 -7911150 Opening Balance 01-Feb-20 -31644600 Sell 01-Feb-20 6329820 Purchase 02-Feb-20 -8229936 Sell 03-Feb-20 6648201 Purchase 04-Feb-20 -8548902 Sell 05-Feb-20 6966762 Purchase 06-Feb-20 -8868104 Sell 07-Feb-20 7285549 Purchase 08-Feb-20 -9187461 Sell 09-Feb-20 7604496 Purchase 10-Feb-20 -9507060 Sell 11-Feb-20 7924050 Purchase 12-Feb-20 -9827434 Sell 13-Feb-20 8243638 Purchase 14-Feb-20 -10147424 Sell 15-Feb-20 8563104 Purchase 16-Feb-20 -10467534 Sell 17-Feb-20 8882832 Purchase 18-Feb-20 -10787962 Sell 19-Feb-20 9202773 Purchase 20-Feb-20 -11108160 Sell 21-Feb-20 9522660 Purchase 22-Feb-20 -11428812 Sell 23-Feb-20 9843058 Purchase 24-Feb-20 -11749794 Sell 25-Feb-20 10163328 Purchase 26-Feb-20 -12070586 Sell 27-Feb-20 10483044 Purchase 28-Feb-20 -12390768 Closing Balance 29-Feb-20 47663850 Sell 29-Feb-20 10803806 Opening Balance 01-Mar-20 -47663850 Purchase 01-Mar-20 -12712240 Sell 02-Mar-20 11124820 Purchase 03-Mar-20 -13034269 Sell 04-Mar-20 11448396 Purchase 05-Mar-20 -13358898 Sell 06-Mar-20 11770477 Purchase 07-Mar-20 -13681396 Sell 08-Mar-20 12092474 Purchase 09-Mar-20 -14004364 Sell 10-Mar-20 12414948 Purchase 11-Mar-20 -14327010 Sell 12-Mar-20 12735520 Purchase 13-Mar-20 -14646630 Sell 14-Mar-20 13056532 Purchase 15-Mar-20 -14969453 Sell 16-Mar-20 13378428 Purchase 17-Mar-20 -15291120 Sell 18-Mar-20 13697478 Purchase 19-Mar-20 -15596945 Sell 20-Mar-20 14006828 Purchase 21-Mar-20 -15919650 Sell 22-Mar-20 14330250 Purchase 23-Mar-20 -16229475 Sell 24-Mar-20 14630576 Purchase 25-Mar-20 -16542344 Sell 26-Mar-20 14959254 Purchase 27-Mar-20 -16917070 Sell 28-Mar-20 15323376 Purchase 29-Mar-20 -17241390 Sell 30-Mar-20 15651972 Closing Balance 31-Mar-20 89476520 Purchase 31-Mar-20 -17575745