Aggregate XIRR Calculation Help

[ekthunder]

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 interesting way that finance has set to calculate Cashflow it takes Contributions + Distributions of the past and adds the current Contributions + Distributions + NAV (Please let me know if this is common/normal)

I have different accounts that go through these and can have an XIRR calculated to them individually. All fine and dandy and can correlate to what can happen in Excel/Googlesheets.

I am having rather a difficult time when it comes to Aggregating the accounts together to generate an IRR for the selected (slicer) or all accounts.

I have tried making a separate table calulation and making sure that the DAX Measure only sees the date column and cashflow but no dice on the XIRR function. I have an iferror catch to make it -100%.

Attached below is the mockup and you can see that I was able to create the by account XIRR but having issues with the aggregated account level XIRR.

**UPDATE: Trying to load .pbix for an example but can't seem to load...

[v-ssriganesh]

Hi @ekthunder 
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[ekthunder]

Your solution was not helpful

[v-ssriganesh · Accepted solution]

Hi @ekthunder,

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.

[v-ssriganesh]

Hi @ekthunder,

Could you please confirm whether the issue has been resolved after raising your support case? If a solution has been found, we would greatly appreciate it if you could share your insights with the community. This would be valuable for other members who may encounter similar issues.

As of now, we are closing this case. If you have any further questions, feel free to reach out to the community by creating a new post, and we’ll be happy to assist you.

Thank you for continuing to use the Microsoft Fabric Community Forum.

[v-ssriganesh]

Hi @ekthunder ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-ssriganesh]

Hi @ekthunder 

May I ask if you have resolved this issue? If so, please mark the helpful reply and Accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-ssriganesh]

Hi @ekthunder 

Thank you for sharing the required details. I have reviewed the PBIX file and verified that the XIRR calculations are working as expected. Could you please specify your expectations for the output? This will help us provide the most accurate solution at our earliest convenience.

Thanks for your understanding and have a great day a head.

[ekthunder]

Linked below is an excel with the expected outcome if the following accounts were selected for the Aggregate XIRR:
A458

B672

E090

The XIRR calculation in PowerBI is only giving me -100% while I am getting the expected results in excel.

https://docs.google.com/spreadsheets/d/1FFzpIpBCHGVTJZrq8gd5MkTn2jAHbwHm/edit?usp=drive_link&ouid=11...

[v-ssriganesh]

Hello,
After conducting several workarounds to resolve the issue, we discovered that the data in the calcashflow column is in an incorrect format, specifically using parentheses “( )”. This format causes the values within the parentheses to be interpreted as negative, leading to invalid IRR results. Consequently, we suspect this may be causing the XIRR function to return an error.

Therefore, we recommend modifying the data accordingly and checking if the outcomes reflect as expected.

We believe this will address your concerns. Please implement the changes and feel free to raise any further issues if needed.

Thank you and have a great day ahead.

[ekthunder]

Linked here for those that would like to get the .pbix file

https://drive.google.com/file/d/178yM-6-Joic1kyUtaQTafgnMQPFgz15m/view?usp=drive_link 

[v-ssriganesh]

Hi @ekthunder 

Thank you for posting your query in the Microsoft Fabric community. I apologize for any inconvenience you may have experienced.

Could you please assist me with a few details? If possible, could you share your .pbix file with me? Also, I would appreciate it if you could describe the expected behavior you're looking for.

If you encounter any issues while updating the file, please ensure that its size does not exceed 47 MB.

[ekthunder]

some of the data:

Account	Quarter	Contributions	Distributions	NAV	CalcCashFlow
L499	3/31/2008	($22,519.98)	$0.00	$22,221.38	($298.60)
R642	3/31/2008	($29,706.05)	$0.00	$25,683.88	($4,022.17)
L499	6/30/2008	$0.00	$0.00	$21,995.07	($524.91)
R642	6/30/2008	($44,559.08)	$0.00	$66,491.17	($7,773.96)
R642	9/30/2008	($23,764.84)	$0.00	$86,454.72	($11,575.25)
L499	9/30/2008	$0.00	$0.00	$21,794.86	($725.12)
L499	12/31/2008	$0.00	$0.00	$21,574.97	($945.01)
R642	12/31/2008	($17,823.63)	$0.00	$100,554.72	($15,298.88)
R642	3/31/2009	$0.00	$0.00	$96,823.52	($19,030.08)
L499	3/31/2009	$0.00	$0.00	$21,350.04	($1,169.94)
L499	6/30/2009	$0.00	$0.00	$21,229.55	($1,290.43)
R642	6/30/2009	($35,647.26)	$0.00	$130,293.38	($21,207.48)
L499	9/30/2009	$0.00	$0.00	$22,390.06	($129.92)
R642	9/30/2009	($14,853.03)	$0.00	$160,045.04	($6,308.85)
R642	12/31/2009	$0.00	$16,113.79	$148,205.98	($2,034.12)
L499	12/31/2009	$0.00	$1,221.09	$21,515.93	$217.04
L499	3/31/2010	$0.00	$0.00	$21,257.33	($41.56)
R642	3/31/2010	($14,853.03)	$0.00	$159,376.48	($5,716.65)
R642	6/30/2010	($47,529.68)	$0.00	$223,748.00	$11,125.19
L499	6/30/2010	$0.00	$0.00	$22,511.87	$1,212.98
L499	9/30/2010	$0.00	$0.00	$23,306.25	$2,007.36
R642	9/30/2010	($47,529.68)	$0.00	$280,029.12	$19,876.63
R642	12/31/2010	($56,441.50)	$0.00	$386,609.10	$70,015.11
L499	12/31/2010	($11,259.99)	$0.00	$38,427.33	$5,868.45
L499	3/31/2011	$0.00	$0.00	$42,325.19	$9,766.31
R642	3/31/2011	($32,676.66)	$0.00	$470,928.52	$121,657.87
R642	6/30/2011	($35,647.26)	$55,011.09	$504,969.28	$175,062.46
L499	6/30/2011	$0.00	$4,168.66	$42,045.22	$13,655.00
L499	9/30/2011	$0.00	$0.00	$55,645.29	$27,255.07
R642	9/30/2011	($41,588.47)	$0.00	$724,754.66	$353,259.37
L499	12/31/2011	$0.00	$0.00	$59,749.07	$31,358.85
R642	12/31/2011	($11,882.42)	$0.00	$791,288.03	$407,910.32
R642	3/31/2012	($17,823.63)	$27,176.57	$807,164.83	$433,140.06
L499	3/31/2012	($11,259.99)	$2,039.70	$70,856.85	$33,246.34
R642	6/30/2012	$0.00	$0.00	$915,154.75	$541,129.98
L499	6/30/2012	$0.00	$0.00	$79,497.16	$41,886.65
R642	9/30/2012	$0.00	$0.00	$910,111.77	$536,087.00
L499	9/30/2012	$0.00	$0.00	$79,116.38	$41,505.87
R642	12/31/2012	$0.00	$0.00	$926,402.76	$552,377.99
L499	12/31/2012	$0.00	$0.00	$80,358.01	$42,747.50
R642	3/31/2013	$0.00	$0.00	$887,686.48	$513,661.71
L499	3/31/2013	$0.00	$0.00	$77,445.26	$39,834.75
R642	6/30/2013	$0.00	$0.00	$873,095.93	$499,071.16
L499	6/30/2013	$0.00	$0.00	$76,342.20	$38,731.69
L499	9/30/2013	$0.00	$0.00	$82,726.91	$45,116.40
R642	9/30/2013	$0.00	$0.00	$958,105.48	$584,080.71
R642	12/31/2013	$0.00	$0.00	$1,071,860.33	$697,835.56
L499	12/31/2013	$0.00	$0.00	$91,275.30	$53,664.79
R642	3/31/2014	$0.00	$0.00	$1,179,955.19	$805,930.42
L499	3/31/2014	$0.00	$0.00	$99,422.03	$61,811.52
R642	6/30/2014	($23,764.84)	$149,899.83	$1,193,637.86	$945,748.08
L499	6/30/2014	$0.00	$11,293.79	$99,124.47	$72,807.75
R642	9/30/2014	$0.00	$81,532.72	$1,150,649.64	$984,292.58
L499	9/30/2014	$0.00	$6,119.28	$95,890.58	$75,693.14
L499	12/31/2014	$0.00	$0.00	$116,496.00	$96,298.56
R642	12/31/2014	($23,764.84)	$0.00	$1,435,952.11	$1,245,830.21
R642	3/31/2015	($29,706.05)	$0.00	$1,589,320.53	$1,369,492.58
L499	3/31/2015	$0.00	$0.00	$126,302.90	$106,105.46
R642	6/30/2015	$0.00	$98,513.15	$1,437,175.68	$1,315,860.88
L499	6/30/2015	$0.00	$7,602.52	$113,278.19	$100,683.27
R642	9/30/2015	$0.00	$0.00	$1,469,542.08	$1,348,227.28
L499	9/30/2015	$0.00	$0.00	$115,833.01	$103,238.09
R642	12/31/2015	$0.00	$62,552.40	$1,372,521.38	$1,313,758.98
L499	12/31/2015	$0.00	$5,131.97	$107,701.45	$100,238.50
R642	3/31/2016	$0.00	$31,888.86	$1,244,872.74	$1,217,999.20
L499	3/31/2016	$0.00	$2,756.22	$97,790.74	$93,084.01
R642	6/30/2016	($20,794.24)	$48,549.55	$1,186,304.59	$1,187,186.36
L499	6/30/2016	$0.00	$4,000.63	$91,460.82	$90,754.72
R642	9/30/2016	$0.00	$42,584.18	$1,144,498.97	$1,187,964.92
L499	9/30/2016	$0.00	$3,195.99	$88,307.12	$90,797.01
R642	12/31/2016	$0.00	$0.00	$1,090,021.07	$1,133,487.02
L499	12/31/2016	$0.00	$0.00	$84,188.37	$86,678.26
R642	3/31/2017	$0.00	$0.00	$1,084,690.57	$1,128,156.52
L499	3/31/2017	$0.00	$0.00	$83,768.10	$86,257.99
R642	6/30/2017	$0.00	$0.00	$1,030,374.71	$1,073,840.66
L499	6/30/2017	$0.00	$0.00	$79,637.32	$82,127.21
R642	9/30/2017	$0.00	$0.00	$1,096,887.66	$1,140,353.61
L499	9/30/2017	$0.00	$0.00	$84,616.93	$87,106.82
R642	12/31/2017	$0.00	$131,316.49	$926,695.57	$1,101,478.01
L499	12/31/2017	$0.00	$9,914.61	$71,777.65	$84,182.15
R642	3/31/2018	$0.00	$0.00	$1,042,305.91	$1,217,088.35
L499	3/31/2018	$0.00	$0.00	$80,500.05	$92,904.55
R642	6/30/2018	$0.00	$121,712.84	$1,013,178.09	$1,309,673.37
L499	6/30/2018	$0.00	$9,173.28	$78,298.04	$99,875.82
R642	9/30/2018	($23,764.84)	$72,814.43	$1,101,479.53	$1,447,024.40
L499	9/30/2018	$0.00	$5,488.72	$83,083.36	$110,149.86
R642	12/31/2018	$0.00	$45,424.82	$967,761.51	$1,358,731.20
L499	12/31/2018	$0.00	$3,442.30	$72,948.24	$103,457.04
R642	3/31/2019	$0.00	$46,399.65	$1,458,057.13	$1,895,426.47
L499	3/31/2019	$0.00	$3,516.15	$109,710.47	$143,735.42
R642	6/30/2019	$0.00	$634,542.96	$1,990,883.55	$3,062,795.85
L499	6/30/2019	$0.00	$47,712.49	$149,614.52	$231,351.96
R642	9/30/2019	$0.00	$249,799.05	$1,215,120.86	$2,536,832.21
L499	9/30/2019	$0.00	$18,817.69	$91,304.71	$191,859.84
R642	12/31/2019	$0.00	$261,361.78	$960,813.98	$2,543,887.11
L499	12/31/2019	$0.00	$19,629.47	$72,216.51	$192,401.11
R642	3/31/2020	$0.00	$265,118.92	$773,575.06	$2,621,767.11
L499	3/31/2020	$0.00	$19,897.96	$58,140.25	$198,222.81
R642	6/30/2020	$0.00	$165,834.89	$669,695.49	$2,683,722.43
L499	6/30/2020	$0.00	$12,446.41	$50,332.21	$202,861.18
R642	9/30/2020	$0.00	$311,959.94	$370,752.66	$2,696,739.54
L499	9/30/2020	$0.00	$23,413.50	$27,883.53	$203,826.00
R642	12/31/2020	$0.00	$130,831.50	$319,626.52	$2,776,444.90
L499	12/31/2020	$0.00	$9,820.42	$24,048.47	$209,811.36
R642	3/31/2021	$0.00	$0.00	$332,148.61	$2,788,966.99
L499	3/31/2021	$0.00	$0.00	$24,986.06	$210,748.95
R642	6/30/2021	$0.00	$0.00	$375,866.92	$2,832,685.30
L499	6/30/2021	$0.00	$0.00	$28,258.54	$214,021.43
R642	9/30/2021	$0.00	$0.00	$391,852.33	$2,848,670.71
L499	9/30/2021	$0.00	$0.00	$29,457.54	$215,220.43
R642	12/31/2021	$0.00	$0.00	$435,020.65	$2,891,839.03
L499	12/31/2021	$0.00	$0.00	$32,719.85	$218,482.74
R642	3/31/2022	$0.00	$0.00	$411,992.63	$2,868,811.01
L499	3/31/2022	$0.00	$0.00	$30,965.29	$216,728.18
R642	6/30/2022	$0.00	$0.00	$372,299.57	$2,829,117.95
L499	6/30/2022	$0.00	$0.00	$27,966.49	$213,729.38
R642	9/30/2022	$0.00	$0.00	$350,389.19	$2,807,207.57
L499	9/30/2022	$0.00	$0.00	$26,297.18	$212,060.07
R642	12/31/2022	$0.00	$0.00	$296,306.47	$2,753,124.85
L499	12/31/2022	$0.00	$0.00	$22,187.10	$207,949.99
R642	3/31/2023	$0.00	$83,784.94	$216,742.50	$2,757,345.82
L499	3/31/2023	$0.00	$6,235.30	$16,259.78	$208,257.97
L499	6/30/2023	$0.00	$0.00	$16,333.17	$208,331.36
R642	6/30/2023	$0.00	$0.00	$217,854.36	$2,758,457.68
R642	9/30/2023	$0.00	$0.00	$220,036.84	$2,760,640.16
L499	9/30/2023	$0.00	$0.00	$16,488.36	$208,486.55
R642	12/31/2023	$0.00	$20,321.99	$198,530.85	$2,759,456.16
L499	12/31/2023	$0.00	$1,537.41	$14,850.59	$208,386.19
R642	3/31/2024	$0.00	$28,042.24	$170,108.53	$2,759,076.08
L499	3/31/2024	$0.00	$2,123.78	$12,688.99	$208,348.37
R642	6/30/2024	$0.00	$0.00	$163,129.65	$2,752,097.20
L499	6/30/2024	$0.00	$0.00	$12,151.26	$207,810.64
R642	9/30/2024	$0.00	$0.00	$159,679.80	$2,748,647.35
L499	9/30/2024	$0.00	$0.00	$11,880.15	$207,539.53

[ekthunder]

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
    )

[ekthunder]

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