Receiving Error when CAGR and XIRR measures are added to a table

[imsai2105]

Hello Everyone,

I have transaction data and need assistance incorporating CAGR and XIRR values into the same table.

While XIRR is already implemented using an inbuilt function, I am using the following CAGR formula:

CAGR =
( (Current Value - Invested Amount) ^ (365/ (Current Date-Invested date) in days ) ) -1

When I display CAGR and XIRR values in a card, they work well for individual accounts, and when all items are selected, I get an average. However, when I place them in a table, I encounter the attached error. Could you please provide assistance with this issue?

[Anonymous]

Hi @imsai2105 ,

If I understand correctly, the issue is that you received error when measures were added to a table. Please try the following methods and check if they can solve your problem:

1.Ensure that the data types of the columns are correct and that they match the expected data types for the formula.

2.Check the DAX formula for CAGR. You can try to use the following formula.

CAGR =
( (Current Value/Invested Amount)(1/(Current Date-Invested date) in years ) ) -1

3.Check if there are any filters applied that might be affecting the calculation within the table.

Looking forward to your reply.

Best Regards,

Wisdom Wu

[imsai2105]

Hello @Anonymous,

Thank you for your response. I did review the data types, they are fine. Even when I put in a filter it doesnt load the data. The expectation is to have complete picture of a fund based on timelines and by accounts.
I'm attaching sample data for referrence. Please let me know if you come up with any other possible solutions.

[Daniel29195]

im not sure but try adding ( int  ) , the int will convert the current  - invested date into integer which is the nb of days between the 2 dates .

CAGR =
( (Current Value - Invested Amount) ^ (365/  int((Current Date-Invested date)) in days ) ) -1

[imsai2105]

Hello @Daniel29195,

Thank you for your response. I did convert the dates in to integers as of date difference in days, the above formula is for reference. Please let me know if you have any solutions. Here is the data for reference

[Daniel29195]

@imsai2105 

can you please share the full dax code that you wrote  : 

CAGR =
( (Current Value - Invested Amount) ^ (365/ (Current Date-Invested date) in days ) ) -1

it seems like you have created some variables in your measure  , 

also if possible can you share the sturcture of the visual you are implementing  . just to imitate your work from my side so that i can create the problem you are having . 

[imsai2105]

Calculated column to identify the number of days
Days_Difference = DATEDIFF([DateOfInvestment], Today(), DAY)

Measure to get startvalue:
StartValue = SUM(InvestmentAmount)

Compound annual growth rate daily (365 Days):

CAGR =
VAR Value_Variance = CALCULATE(MAX(Current Valuation]) / [StartValue])
VAR Date_Diff = DIVIDE(365, MAX(Days_Difference))
RETURN
(Value_Variance ^ Date_Diff) -1