An argument of function has wrong data type or the result is too large or too small

[Good_Stuff]

I am working on a CAGR Calculation in Power BI, the Syntax is 

CAGR = RRI([Num of CAGR Year],[FirstYearShipValue],[LastYearShipValue])

The num of CAGR Year, FirstYearValue and LastYearValue DAX are as below: 

The issue is the calculation could only be visulized individually on a card, once I brought any dimentional attributes to a table with the CAGR calculation, I immediately got en error. 

Could please anyone advice what could be the cause?  How could I fix it? 

Thank you!

FirstYearShipValue =

VAR FirstYear = FIRSTNONBLANK ('Calendar (Reporting Date & Backlog GID)'[FY],CALCULATE ([Ship Value]))

VAR FilterYear = FILTER('Calendar (Reporting Date & Backlog GID)', 'Calendar (Reporting Date & Backlog GID)'[FY] =  FirstYear)

RETURN

SUMX(FilterYear, [Ship Value])

LastYearShipValue =

VAR LastYear = LASTNONBLANK('Calendar (Reporting Date & Backlog GID)'[FY],CALCULATE ([Ship Value]))

VAR FilterYear = FILTER('Calendar (Reporting Date & Backlog GID)', 'Calendar (Reporting Date & Backlog GID)'[FY] =  LastYear)

RETURN

SUMX(FilterYear, [Ship Value])

Num of CAGR Year =

VAR FirstYear = FIRSTNONBLANK (

     'Calendar (Reporting Date & Backlog GID)'[FY],

    CALCULATE ([Ship Value]))

VAR LastYear = LASTNONBLANK (

    'Calendar (Reporting Date & Backlog GID)'[FY],

    CALCULATE ([Ship Value]))

RETURN LastYear - FirstYear

[Anonymous]

Hi @Good_Stuff 

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Good_Stuff]

Thank you for your reply. By using the blow year slicer and a dimentional attribute, I can see I don't have number of years <= 0. But once I bring the CAGR to the table chart, the visual would break and the same error comes up....

[lbendlin]

Are you familiar with DAXDebugOutput?  It provides you an option to use EVALUATEANDLOG to troubleshoot your issue.

Or you can provide sample data here that illustrates the issue.

[lbendlin]

Quote:

• An error is returned if:

  • nper ≤ 0.