XIRR DAX - Bug

[Anonymous]

Hi Team,

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" 

value	as_at
-$11,632,091.	30/06/2013
$11,632,091.	30/06/2013
-$578,491,058.3	1/10/2013
$4,572,622.	31/12/2013
-$4,572,622.	31/12/2013
-$3,860,257.	31/12/2013
$3,860,257.	31/12/2013
$621,697,497.86	30/06/2014
$3,754,510.	30/06/2014
-$3,754,510.	30/06/2014
-$3,874,717.27	30/06/2014
$3,874,717.27	30/06/2014
-$66,879.73	30/06/2014
$66,879.73	30/06/2014

Can I get a resolution to this issue ASAP??

Thanks,

Hari T

[Anonymous]

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

[v-qiuyu-msft]

Hi @Anonymous,

You can create a measure in Power BI desktop use XIRR() function like below: 

Measure1 = XIRR('Table1','Table1'[value],'Table1'[as_at])

Reference: 

https://docs.microsoft.com/en-us/dax/xirr-function-dax

Best Regards,
Qiuyun Yu 

[Anonymous]

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?

-Hari T

[v-qiuyu-msft]

Hi @Anonymous, 

I already sent a email to consult this issue internally, will update here once I get any information. 

Best Regards,
Qiuyun Yu

[v-qiuyu-msft]

Hi @Anonymous ,

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 

[Anonymous]

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.

Cheers,

Hari T

[v-qiuyu-msft]

Hi @Anonymous ,

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. 

Best Regards,
Qiuyun Yu 

[Anonymous]

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

[Anonymous]

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.

[dancarr22]

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.