topic Re: An argument of function has wrong data type or the result is too large or too small in DAX Commands and Tips

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

Anonymous — Fri, 05 Jul 2024 15:03:11 GMT

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

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

lbendlin — Sat, 06 Jul 2024 17:36:01 GMT

Quote:

An error is returned if:
- nper ≤ 0.

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

Anonymous — Mon, 08 Jul 2024 08:43:35 GMT

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

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

lbendlin — Mon, 08 Jul 2024 13:26:34 GMT

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.

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

Anonymous — Wed, 31 Jul 2024 07:27:18 GMT

Hi Anonymous

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.