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

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-ticketThank you.

Aggregate XIRR Calculation Help | Microsoft Fabric Community

14 Replies

Anonymous

Not applicable

1 year ago

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
    )

Anonymous

Not applicable

1 year ago

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

Anonymous

Not applicable

1 year ago

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

v-ssriganesh
Community Support
1 year ago
Hi Anonymous

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.
Anonymous
Not applicable
1 year ago
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
Community Support
1 year ago
Hi Anonymous

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.
- Anonymous
  Not applicable
  1 year ago
  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=117035587901407620801&rtpof=true&sd=true
  - v-ssriganesh
    Community Support
    1 year ago
    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.
v-ssriganesh
Community Support
1 year ago
Hi Anonymous

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
Community Support
1 year ago
Hi Anonymous ,
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
Community Support
1 year ago
Hi Anonymous
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.
- Anonymous
  Not applicable
  1 year ago
  Your solution was not helpful
  - v-ssriganesh
    Community Support
    1 year ago
    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.

Forum Discussion

Aggregate XIRR Calculation Help

14 Replies

Related content

Calculate XIRR

XIRR calculation using DAX

XIRR Calculation Error

xIRR calculation discrepancy

Rolling XIRR Calculation