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.
I have the below measure:
XIRR = CALCULATE(XIRR('TABLEA','TABLEA'[ColumnA], 'TABLEA'[Date].[Date]), 'TABLEA'[Filter])
This flags the error:
MdxScript(Model) (3, 97) Calculation error in measure 'TABLEA'[XIRR]: Cannot convert value 'ABC' of type Text to type True/False.
How can I use the XIRR function on dynamic values as the below seems to work. Should I be using a GROUP BY or SUMMARIZE?
XIRR = CALCULATE(XIRR('TABLEA','TABLEA'[ColumnA], 'TABLEA'[Date].[Date]), 'TABLEA'[Filter] = "ABC")
Thanks.
Using 'TABLEA'[Filter] as a filter will not work, as it will try and evaluate this, but it is meaningless. It is asking what you want to filter by, and it is given a column name with no context. I'm unsure what you're trying to achieve. If you are filtering, you should be able to directly use TABLEA[Filter] as a filter in your report, and omit it from the measure?
Please elaborate if I have misunderstood.
Did I answer your question? Mark my post as a solution! Proud to be a Super User!
Connect with me!
Stay up to date on
Read my blogs on
Hi,
Thanks for your reply.
If I don't use the filter within the measure I get the same XIRR result value for all rows, even although 'TABLEA'[ColumnA]' is different for each row, its as if the XIRR function is summing the ColumnA value for all rows and then performing the function logic. This is why I need the function to run on a row by row basis or group by distinct ColumnA values before calculating the function.
Thanks.
Hi @Anonymous,
Do you mind share your sample table for further analysis? So that we can check the specific reason why the error returned? Thanks for understanding.
Best Regards,
Angelia
Hi,
Below is my "Data" table of data...
Investment Cashflow Date
ABC | -57.28775201 | 30/06/2017 |
ABC | -1.017111279 | 31/12/2017 |
ABC | -1.011349925 | 30/09/2017 |
ABC | -0.942991769 | 31/03/2018 |
ABC | 0.43541108 | 30/06/2018 |
ABC | 0.445494726 | 30/09/2018 |
ABCD | 0.456578729 | 31/12/2018 |
ABCD | 0.497459863 | 30/06/2019 |
ABCD | 0.507532876 | 30/09/2019 |
ABCD | 0.507597629 | 31/12/2021 |
ABCD | 0.512661415 | 31/12/2019 |
ABCD | 0.513519403 | 30/09/2021 |
ABCD | 0.520365843 | 30/06/2021 |
ABCDE | 0.521142079 | 31/03/2020 |
ABCDE | 0.567614866 | 31/03/2021 |
ABCDE | 0.56995523 | 31/12/2020 |
ABCDE | 0.577998285 | 30/09/2020 |
ABCDE | 0.587201729 | 30/06/2020 |
ABCDE | 8.175973867 | 31/03/2022 |
ABCDE | 9.301638451 | 31/03/2019 |
ABCDE | 69.61181073 | 30/06/2022 |
I then created a new "XIRR Col" from the fields list in Power BI:
XIRR Col = XIRR('Data','Data'[Cashflow], 'Data'[Date].[Date])
I seem to get the same value for each of my rows in the XIRR Col???
Thanks.
Hi @Anonymous,
Have you resolved your issue? If you have, welcome to share your solution or mark the right reply as answer. More people will benefit from here.
Best Regards,
Angelia
Hi @Anonymous,
You want to calculate the XIRR value for "ABC", "ABCD", "ABCDE" seperately, right? But XIRR expects at least one positive cash flow and one negative cash flow; otherwise, XIRR returns the #NUM! error value. For all Cashflow rows for "ABCD" and "ABCDE", there is no negative value, it returns error.
Thanks,
Angelia
Instead of creating a new column you can directly do this calculation in a new measure instead.
Then you can use that measure in for example a table visual with your investments as rows in that table?
XIRR Measure = XIRR('Data','Data'[Cashflow], 'Data'[Date].[Date])
Br,
Magnus
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 |
---|---|
96 | |
95 | |
83 | |
70 | |
66 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |