Calculate IRR from inception until slicer selection, while other metrics only for that slicer selection

Question

My report needs to display IRR from inception until the year and quarter selected in the slicer, while the same report has other metrics that display only for the year and quarter selected. I'm using XIRR formula.

The report has a slicer for Year, and another for Quarter.

If I make a selection in the slicer for year = 2020, and quarter = Q3, the other metrics display the correct values for Q3 of 2020 (since the data in the respective tables have columns for Year and Quarter), but the IRR metric will incorrectly show me the value for Q3 of 2020. I need that card to show me the IRR up to Q3 of 2020.

(For context, an IRR table usually consists of date, and the cash flow. When the asset is purchased, that date is the inception date and it has a negative cash flow associated with that date. During the life of the investment, there are cash distributions i.e. positive cash flow, and the last date consists the selling price of that asset. Hence showing the IRR for Q3 of 2020 only will result in an error, because the XIRR formula needs to see both positive and negative cash flows)

I'm using the same calendar lookup table for IRR and the other metrics in my data model.

Joao Leal Joao Leal · Accepted Answer · 2020-11-09T13:59:14

You need to clear the filter on quarter and replace it with <= selected quarter.

measure=
   var quarter = SELECTEDVALUE(table[quarter])
   return CALCULATE(XIRR(.....), ALL(table[quarter]), table[quarter] <= quarter))

Calculate IRR from inception until slicer selection, while other metrics only for that slicer selection

1 Answers