Failing to create a list of IRR values over a date range

[landryn]

Hi

I am building a report utilising the XIRR function.

I have not had any issues calculating values for specific dates. When I create a visual with a single date, it works fine.

However, I struggle to produce a tablethat includes multiple dates dates together with the corresponding IRR value.

It will simply produce this error message:

My IRR formula is this: 

IRR = XIRR(VALUES('IRR Table'), [IRR Cashflow],'IRR Table'[As At Date])

I have another formula where I have tried to produce a "Rolling Total":

IRR Total =

CALCULATE(

    'IRR Table'[IRR],

    FILTER(

        ALLSELECTED('IRR Table'[As at Date],'IRR Table'[Portfolio Name]),

        ISONORAFTER('IRR Table'[As at Date], MAX('IRR Table'[As at Date]), DESC)

    )

)

This doesn't work either.

I would like to produce this table of values so I can create a Line Chart with the IRR values.

Hope you can help out

thanks

Christian

[landryn]

Hi @Chris_White 

Thanks for this. I tried to add 0 for the Alternate Result, and you were right. This now populates my table with values.

If I look at the IRR formula that I created it will produce a Zero value for all dates (as expected).

For the 'Running Total' formula, it is now accruately displaying the correct values for the corresponding dates.

However, it will also display a zero value for ALL dates corresponding to my date list.

So a quick fix for this was to filter the visual on the IRR Cashflow column to be "not blank"

Thanks

Well done.

Christian

[Chris_White · Accepted solution]

To better identify where the issue lies, it would be a good idea to use the 'alternate result' functionality in the XIRR function, like so:

IRR = XIRR('IRR Table','IRR Table'[IRR Cashflow], 'IRR Table'[As At Date],,0)

Rather than just giving you an error, the visual will now instead display a zero for the dates it can't calculate. 
Your rolling total is the kind of thing you need to use.
I suspect it can't calculate for some dates as XIRR demands both positive and negative values in the table of data it's working with, but the start/end point of your line chart will presumably only contain one data point, which can't both be positive and negative, hence error.