cancel
Showing results for 
Search instead for 
Did you mean: 

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
Regular Visitor

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

Moderator

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 
Regular Visitor

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

 

 

Moderator

Hi @harikishant

 

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

 

Best Regards,
Qiuyun Yu

Moderator

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 

Regular Visitor

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

 

 

Moderator

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