XIRR at input date that includes a terminal value rolled forward to that same day

kdrizzy92 ,

Take a look at the attached. I don't think it's 100% complete but it's a good start.  Here's a few things I did:

1. Created a Dimension table for Company Name and Investment Name. Want to use those columns as filters, never want to filter using a fact table since that would be a performance hit
2. Created a separate NAV table. Probably not 100% necessary, but I find it easier to have the ENAVs and the cashflows separted

So here's how the data model looks:

Then back in the report view, with Dates on rows from your Date Table, and Company Name and InvestmentName from our new Dimension tables, can use this IRR function:

IRR v2 = 

//Create a table that is just the ENAV value for the current date in the current filter context
VAR __EnavTable=
	CALCULATETABLE(
		NavTable,
			FILTER(
				ALL( 'Date'),
				MAX('Date'[Date]) = 'Date'[Date]
			)
		)

//Create a table that has all the cashflows (not the terminal value) from start of time till the date in the current filter context
//Need to use Select Columns for the Union Function. Union Function Requires the same amount of columns
VAR __CFTable=
SELECTCOLUMNS(
    	CALCULATETABLE(
		IRR,
			FILTER(
				ALL( 'Date'),
				MAX( 'Date'[Date]) >= 'Date'[Date]
            )
    ),
    "CompanyName", IRR[CompanyName], "InvestmentName", IRR[InvestmentName], "Date", IRR[Date],"CashFlow", IRR[Cashflow])

//Need to make one table out of the above tables since want all the cashflows preceeding the current date in the current filter context
//and only want the ENAV value for the date from the context, not all the preeceding ones
Var __Union =
    UNION(  __CFTable,__EnavTable)

//Feed the columns from our union table to the XIRR function
var __XIRR=
      IFERROR(
            XIRR( __Union,[Cashflow],[Date]),BLANK()
        
        
    )

RETURN
__XIRR

There's notes in the code itself, but basically it says look at the date I'm currently in (along with the Company, Investment, or whatever filters are applied). Then in the ENVA table, give me a table where it is only the ENAV for all those above filters. Then the second table applies the same filters, but to the IRR table and gives a table where it is all the cashflows up to, and including, the date in the current filter context.

So with those two tables, we want to use merge them into one so we use UNION. and with that merged table we can use as the columns for the IRR function. 

I dont know enough about your data, or if the data was random, but I did a quick check in excel and looked good, but in no way did I do a deep check.  There are a lot of little things that I have found to account for when doing IRRs and such. (such as what to do when the initial cash flow and the date you want the IRR is < 1 year....) but that's putting the cart before the horse. 

So take a look at the attached and let me know what you think

https://1drv.ms/u/s!Amqd8ArUSwDS3VZLLHbm6kUvXIMD?e=0Bvy6L