The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
It seems like a PowerBI bug to me.
I'm trying to calculate XIRR using DAX with a certain dataset which is provided below. It works like a charm in excel but returns an error "XIRR function couldn't find a solution"
Can I get a resolution to this issue ASAP??
I've provided with just one dataset example. I have plenty of data sets handy where the XIRR doesn't work.
I'm looking for a workaround solution
You can create a measure in Power BI desktop use XIRR() function like below:
Thanks for your response Qiuyun Yu.
I'm pretty aware about the XIRR function in PBI and have created a dashboard out of XIRR.
My Question was on the output of the XIRR function with the dataset that I've provided. Have you tried to create a measure on this dataset? Have you got it working? Have you tried to calculate in excel with the same dataset that I've provided?
I already sent a email to consult this issue internally, will update here once I get any information.
Best Regards,Qiuyun Yu
Hi @harikishant ,
You table has positive and negative values on the same day, it’s invalid input there is no way to search for a solution when the values fluctuate violently on the same day. The input to XIRR should be one value per date.
You need to use Group By in Query Editor to group by [as_at] column and filtering the leading row when using the XIRR function. e.g. Column = XIRR(FILTER('Work', [as_at] > DATE(2013, 6, 30)), [Total], [as_at]), XIRR will find a solution.
Remember the assumption of XIRR is that there are a few large investments upfront (big negative numbers on the first few dates) and then recoup your investment over a long period of time with mostly positive cash flows. The best chance for XIRR to find a solution is to make sure input data follows the pattern. The other input patterns don’t make financial sense anyway.
Best Regards,Qiuyun Yu
Thanks Yu for your response.
I've already figured out the problem. And yes you are partially correct with your approach to calculate
1. PBI doesn't work if the net value is zero on the first date.
2. PBI still works with multiple values per date.
I still don't understand why this same dataset works in excel and not in Power BI. One would expect the exact same functionality when replicating excel to Power BI.
I feel this is definetely a bug in PBI which is not handled.
XIRR doesn't guarantee to find a solution. This is true for both Excel and Power BI. That being said, Power BI uses a different algorithm than Excel, so given a particular sequence of data points, one of the products may find a solution but the other doesn't. This is by design, not a product defect.
I have similar issue where Excel formula works but Power BI XIRR doesn't work.Does Power BI designed to work like IRR where in it expects 1ve then positive values
Is there a solution for this issue coming up in future releases?
Power BI XIRR formula doesn't work when negative values exist for last period date.It works fine using Excel.
I also need a fix for this. We get different values from XIRR in PBI based on the 'guess' value we enter. The results swing wildly based on very minor variations in guess. i.e. If we pass in a guess of .1 it returns a correct value of 11.45%. If we pass in a guess of .08 it returns an incorrect value of 498%. Would really help to get this fixed. It is unusable and unreliable in the current state - especialy when Excel and other calculators work.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.