Generating IRR for a dynamic selection of Slicers

Apr 13, 2013 at 7:17 AM

I am working on a project where I have to generate IRR for a dynamic grouping of projects. Each project has a number of cashflows. Then, each project is assigned a number of attributes like sector, country, etc. What I want to do is to select a country or sector using slicers and the IRR for that selection is calculated and displayed in the Pivot Table. I may want to select more than 1 country or sector also and the IRR has to be calculated for all the qualifying cashflows.

I am thinking of doing this now using this extension to write a MDX query that will invoke the XIRR function in Excel, but the inputs to the XIRR function will be dynamically generated using MDX based on Slicer selections. My input data including project cashflows and project attributes will be stored in PowerPivot. I have both Excel 2010 and Excel 2013 and I can work on both of them. Since Excel 2013 gives the facility to see the MDX members in the field list, I will try this approach on Excel 2013.

Is this approach feasible? Is there anything that I need to be aware of?

One other complication I have is that I want to see the detailed cashflows for each of the projects also along with the IRR. Detailed table listing is only possible in Power View as I need to list the date and amount for each of the cashflows. But for my requirements, I can hack an approach to make PowerPivot to generate a Pivot to display the cashflow data I want (using DAX functions to return only one value for both date and amount fields). Would this approach of using an MDX query to call Excel function work in PowerView also? I guess it should work as they are all derivatives of SSAS.

I am right now in the feasibility stage and any inputs will be appreciated.
Apr 13, 2013 at 12:22 PM
Sounds like you have a good gameplan.

Make sure you read the Excel 2013 supported features at the bottom of:

You can't add MDX calculations on top of PowerPivot models in Excel 2013.

You might also look at Vidas' approach to XIRR here:
Apr 13, 2013 at 4:03 PM
Thanks a lot for the immediate response.

Thanks for alerting me to the issue of Excel 2013 not supporting MDX calculations. Before your post, I tried to use the MDX Calculated members in Excel 2013 and figured out that it is not possible.

I will take a look at Vidas' approach as that seems to be interesting as well. I will read his solution to understand what aspects of his approach will be applicable to my situation.

Is it possible to add MDX calculated members in Powerview? From what I know so far, it does not seem possible.
Apr 14, 2013 at 6:01 PM
Thank you very much for pointing me to Vidas' Portfolio Slicer. The approach he has taken to XIRR is what I was thinking, but did not know whether it is possible to build such a thing using DAX. Now, I have an example to decode and learn from. Excellent and thank you again for the help.