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.