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

[Daxmax]

Hello Community,

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 (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.

 

[Daxmax · Accepted solution]

Hi community, i was able to figure out the issue. I had to remove filter context using ALL(), and then use XIRR. 

My measure is 

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

[amitchandak]

@Daxmax , are u using xirr of power bi ?

https://docs.microsoft.com/en-us/dax/xirr-function-dax

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Daxmax]

@amitchandak , yes i'm using XIRR of PowerBI