Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
User | Count |
---|---|
85 | |
75 | |
73 | |
70 | |
57 |
User | Count |
---|---|
98 | |
97 | |
92 | |
78 | |
70 |