Forum Discussion
Aggregate XIRR Calculation Help
Hi community, I have been busting my head on this and nothing has been working as expected. TLDR: Trying to get an aggregated level XIRR across multiple accounts. Longer story: I have an...
Hi Anonymous,
If these solutions do not resolve the issue, please consider raising a Microsoft support ticket. You can create a Microsoft support ticket using the link below:
https://learn.microsoft.com/en-us/power-bi/support/create-support-ticket
Thank you.
Per Account Level:
Account Level XIRR =
VAR CurrentAct = 'Sheet1'[Account]
VAR CurrentQuarter = 'Sheet1'[Quarter]
VAR PriorQuarterFlows =
SELECTCOLUMNS(
FILTER(
'Sheet1',
'Sheet1'[Quarter] < CurrentQuarter &&
'Sheet1'[Quarter] >= 'Sheet1'[Earliest Contrib Date] &&
'Sheet1'[Account] = CurrentAct
),
"CashFlow", 'Sheet1'[contrib+distrib],
"FlowDate", 'Sheet1'[Quarter]
)
VAR CurrentQuarterFlow =
SELECTCOLUMNS(
FILTER(
'Sheet1',
'Sheet1'[Quarter] = CurrentQuarter &&
'Sheet1'[Account] = CurrentAct
),
"CashFlow", 'Sheet1'[contrib+distrib+nav],
"FlowDate", 'Sheet1'[Quarter]
)
VAR CombinedFlows =
UNION(PriorQuarterFlows, CurrentQuarterFlow)
RETURN
IFERROR(
XIRR(CombinedFlows, [CashFlow], [FlowDate]),
-1
)Attempt for Aggregate XIRR:
CalcCashFlow = VAR CurrentAct = 'Sheet1'[Account] VAR CurrentQuarter = 'Sheet1'[Quarter] VAR PriorQuarterFlows = SELECTCOLUMNS( FILTER( 'Sheet1', 'Sheet1'[Quarter] < CurrentQuarter && 'Sheet1'[Quarter] >= 'Sheet1'[Earliest Contrib Date] && 'Sheet1'[Account] = CurrentAct ), "CashFlow", 'Sheet1'[contrib+distrib], "FlowDate", 'Sheet1'[Quarter] ) VAR CurrentQuarterFlow = SELECTCOLUMNS( FILTER( 'Sheet1', 'Sheet1'[Quarter] = CurrentQuarter && 'Sheet1'[Account] = CurrentAct ), "CashFlow", 'Sheet1'[contrib+distrib+nav], "FlowDate", 'Sheet1'[Quarter] ) VAR CombinedFlows = UNION(PriorQuarterFlows, CurrentQuarterFlow) RETURN SUMX(CombinedFlows,[CashFlow])IRR Check = VAR mainflow = SELECTCOLUMNS( 'Sheet1', "QTR", 'Sheet1'[Quarter], "CalcCashflow", 'Sheet1'[CalcCashFlow] ) VAR maingroup = GROUPBY( mainflow, [QTR], "Cashflow", SUMX(CURRENTGROUP(), [CalcCashflow]) ) VAR mainrow = SELECTCOLUMNS( maingroup, "period",[QTR], "cf",[Cashflow] ) VAR mainfilter = FILTER(mainrow,[cf] <> 0) VAR maincalc = IFERROR( XIRR(mainfilter, [cf], [period]), -1 ) RETURN maincalc