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.
Good day all,
I see this topic has been discussed in lenth. I just can not put my finger on it.
I have a large dataset (extract posted below) with different investments, each investment with its own irregular cashflows, date and calculated closing balance at month end.
I need to calculate XIRR from inception to specified date (Slicer linked to Calander table). I dont know I think it is more the addcolumn that I am notunderstanding. The selected date and negative closing balance (as if funds are withdrawn on said date) needs to form part of my XIRR arrays.
Investments are labeled closed (Investment table) when funds are withdrawn and I can calculate a straightforward return as the withdrawal completes the cashflow.
Any help in the right direction will be appreciated.
Date | Investment | Value | Closing Balance |
25/09/2020 | investment-2002 | 54,000.00 | 12032.71 |
30/09/2020 | investment-2002 | - 42,000.00 | 12032.71 |
29/01/2021 | investment-2002 | - 2,000.00 | 191.67 |
29/01/2021 | investment-2002 | - 10,000.00 | 191.67 |
15/03/2022 | investment-2002 | 57,841.35 | 96.01 |
23/03/2022 | investment-2002 | - 58,000.00 | 96.01 |
23/03/2022 | investment-2001 | 58,000.00 | 672.75 |
24/03/2022 | investment-2001 | - 27,769.50 | 672.75 |
24/03/2022 | investment-2001 | - 14,978.00 | 672.75 |
24/03/2022 | investment-2001 | - 14,605.74 | 672.75 |
01/04/2022 | investment-2001 | - 3.60 | 926.16 |
05/04/2022 | investment-2001 | - 0.93 | 926.16 |
25/04/2022 | investment-2001 | 190.00 | 926.16 |
25/04/2022 | investment-2001 | 66.02 | 926.16 |
04/05/2022 | investment-2001 | - 13.61 | 911.89 |
06/05/2022 | investment-2001 | - 3.50 | 911.89 |
06/05/2022 | investment-2001 | - 3.50 | 911.89 |
09/05/2022 | investment-2001 | 3.50 | 911.89 |
02/06/2022 | investment-2001 | - 13.15 | 898.11 |
02/06/2022 | investment-2001 | - 3.37 | 898.11 |
04/07/2022 | investment-2001 | - 3.30 | 124.15 |
04/07/2022 | investment-2001 | - 12.87 | 124.15 |
25/07/2022 | investment-2001 | 223.69 | 124.15 |
25/07/2022 | investment-2001 | 63.81 | 124.15 |
28/07/2022 | investment-2001 | - 2,673.50 | 124.15 |
28/07/2022 | investment-2001 | 1,790.86 | 124.15 |
28/07/2022 | investment-2001 | - 165.65 | 124.15 |
01/08/2022 | investment-2001 | - 3.14 | 780.88 |
01/08/2022 | investment-2001 | - 12.24 | 780.88 |
08/08/2022 | investment-2001 | 535.62 | 780.88 |
10/08/2022 | investment-2001 | 133.91 | 780.88 |
01/09/2022 | investment-2001 | - 3.27 | -80.62 |
01/09/2022 | investment-2001 | - 198.94 | -80.62 |
01/09/2022 | investment-2001 | - 304.90 | -80.62 |
01/09/2022 | investment-2001 | - 264.70 | -80.62 |
02/09/2022 | investment-2001 | - 12.68 | -80.62 |
22/09/2022 | investment-2001 | - 42.92 | -80.62 |
22/09/2022 | investment-2001 | 6.37 | -80.62 |
22/09/2022 | investment-2001 | - 40.84 | -80.62 |
05/10/2022 | investment-2001 | - 3.21 | 1233.5 |
05/10/2022 | investment-2001 | - 12.31 | 1233.5 |
11/10/2022 | investment-2002 | 6,000.00 | 6116.48 |
24/10/2022 | investment-2001 | 148.02 | 1233.5 |
24/10/2022 | investment-2001 | 818.50 | 1233.5 |
24/10/2022 | investment-2001 | 362.14 | 1233.5 |
01/11/2022 | investment-2001 | - 12.29 | 15.48 |
01/11/2022 | investment-2001 | - 3.19 | 15.48 |
11/11/2022 | investment-2002 | - 6,000.00 | 85.65 |
23/11/2022 | investment-2001 | - 2,163.80 | 15.48 |
23/11/2022 | investment-2001 | 289.84 | 15.48 |
23/11/2022 | investment-2001 | 666.60 | 15.48 |
05/12/2022 | investment-2001 | - 3.36 | 568.16 |
05/12/2022 | investment-2001 | - 13.10 | 568.16 |
03/01/2023 | investment-2001 | - 13.16 | 568.16 |
05/01/2023 | investment-2001 | - 3.43 | 568.16 |
23/01/2023 | investment-2001 | 121.02 | 568.16 |
23/01/2023 | investment-2001 | 463.92 | 568.16 |
02/02/2023 | investment-2001 | - 3.54 | 40.57 |
02/02/2023 | investment-2001 | - 13.61 | 40.57 |
03/02/2023 | investment-2001 | 4,370.45 | 40.57 |
03/02/2023 | investment-2001 | - 4,507.00 | 40.57 |
13/02/2023 | investment-2001 | - 264.25 | 40.57 |
13/02/2023 | investment-2001 | - 106.28 | 40.57 |
Hi @Laurie check official Microsoft link for your XIRR function requirement. On the link there is short video dealing with alternative results (if you need it sometimes). I hope this help
https://learn.microsoft.com/en-us/dax/xirr-function-dax?WT.mc_id=DP-MVP-4025372
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
99 | |
86 | |
71 | |
67 |
User | Count |
---|---|
116 | |
109 | |
94 | |
79 | |
72 |