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.
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!
User | Count |
---|---|
90 | |
73 | |
68 | |
63 | |
55 |
User | Count |
---|---|
98 | |
89 | |
73 | |
63 | |
61 |