Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

XIRR DAX - Bug

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" 

 

valueas_at
-$11,632,091.30/06/2013
$11,632,091.30/06/2013
-$578,491,058.31/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.8630/06/2014
$3,754,510.30/06/2014
-$3,754,510.30/06/2014
-$3,874,717.2730/06/2014
$3,874,717.2730/06/2014
-$66,879.7330/06/2014
$66,879.7330/06/2014

 

Can I get a resolution to this issue ASAP??

 

Thanks,

Hari T

Status: New
Comments
harikishant
Helper II

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
Community Support

Hi @harikishant,

 

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

 

Measure1 = XIRR('Table1','Table1'[value],'Table1'[as_at])
 
Reference: 
 
Best Regards,
Qiuyun Yu 
harikishant
Helper II

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
Community Support

Hi @harikishant

 

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
Community Support

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.

 

q1.PNGq2.PNG

 

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 

harikishant
Helper II

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
Community Support

Hi @harikishant ,

 

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
Not applicable

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
Not applicable

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
Helper V

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.