topic Re: XIRR from a table of cash flows in DAX Commands and Tips

XIRR from a table of cash flows

Richml — Wed, 21 Jun 2023 17:39:47 GMT

I want to add a column that has the IRR for a project.

I have two tables:

1 - the Projects

2 - the Cash Flows

Tables linked 1-* on Project

Result is not filtering by row context - Help please!

Richard

Re: XIRR from a table of cash flows

Martin_D — Wed, 21 Jun 2023 18:35:35 GMT

This could be the easiest fix:

IRR = XIRR(FILTER('Cash Flows', 'Cash Flows'[Project] = 'Projects'[Project]), 'Cash Flows'[Cashflow], 'Cash Flows'[Cashflow date])

Re: XIRR from a table of cash flows

Richml — Wed, 21 Jun 2023 19:03:49 GMT

Thanks Martin_D - this gave an error message "A result for XIRR could not be determined because the Values and Dates arguements are empty"

Re: XIRR from a table of cash flows

Martin_D — Wed, 21 Jun 2023 20:27:30 GMT

Can you provide the file, pls.?

Re: XIRR from a table of cash flows

Richml — Wed, 21 Jun 2023 20:58:37 GMT

Martin_D - Here you go.

https://drive.google.com/file/d/1f0k1nDl1zoehrCEu7HpR0qdx_C-01xfK/view?usp=sharing

Re: XIRR from a table of cash flows

Martin_D — Thu, 22 Jun 2023 00:47:17 GMT

The error was caused by project 3 for which there is no data in your Cash Flows table. This formula checks for this condition and works with your data:

IRR = IF ( 'Projects'[Project] IN VALUES ( 'Cash Flows'[Project] ), XIRR(FILTER('Cash Flows', 'Cash Flows'[Project] = 'Projects'[Project]), 'Cash Flows'[Cashflow], 'Cash Flows'[Cashflow date]) )

Re: XIRR from a table of cash flows

Richml — Thu, 22 Jun 2023 04:55:51 GMT

Thanks Martin_D !