How to deal with blank values and error messages in DAX formulas

[MSC]

Hi everyone!

I`m using the two formulas below to calculate the compound annual growth rate (CAGR) of some KPIs. The first formula calculates the CAGR for a company selected. The second formula calculates the CAGR for the company`s industry.

Sometimes the CAGR cannot be calculate for mathematical reasons (e.g. when the denominator is zero), which is completely fine. In these cases, I get this "See details" error message. Also, when there are no values available for a selected year in my range, I get an "(Blank)".

Now, I`m wondering how I can adjust the two formulas to show an "n/a" in these two cases.

Thank you very much for any assistance!

KPI 1

CAGR_EBITDA Margin =

VAR StartYear =

CALCULATE (

    MIN ( 'Date_Table_Updated'[Year] ),

    ALLSELECTED ('Date_Table_Updated'[Year] )

)

VAR EndYear =

CALCULATE (

    MAX ( 'Date_Table_Updated'[Year] ),

    ALLSELECTED ('Date_Table_Updated'[Year] )

)

VAR StartValue =

CALCULATE (

    [EBITDA Margin],

    REMOVEFILTERS ('Date_Table_Updated' ),

    'Date_Table_Updated'[Year] = StartYear

)

VAR EndValue =

CALCULATE (

    [EBITDA Margin],

    REMOVEFILTERS ('Date_Table_Updated' ),

    'Date_Table_Updated'[Year] = EndYear

)

VAR NumYears = EndYear - StartYear

VAR Result =

IF (

    NumYears > 0 && NOT ISBLANK ( StartValue ) && NOT ISBLANK ( EndValue ),

    DIVIDE ( EndValue, StartValue ) ^ (1 / NumYears) - 1

)

RETURN Result

KPI 2

CAGR_EBITDA Margin Industry =

VAR SelIndustry = SELECTEDVALUE( Data_Company[Industry] )

VAR StartYear =

CALCULATE (

    MIN ( 'Date_Table_Updated'[Year] ),

    ALLSELECTED ('Date_Table_Updated'[Year] )

)

VAR EndYear =

CALCULATE (

    MAX ( 'Date_Table_Updated'[Year] ),

    ALLSELECTED ('Date_Table_Updated'[Year] )

)

VAR StartValue =

CALCULATE (

    [EBITDA Margin],

    REMOVEFILTERS ('Date_Table_Updated' ),

    'Date_Table_Updated'[Year] = StartYear,

    REMOVEFILTERS( Data_Company ),

    Data_Company[Industry] = SelIndustry

)

VAR EndValue =

CALCULATE (

    [EBITDA Margin],

    REMOVEFILTERS ('Date_Table_Updated' ),

    'Date_Table_Updated'[Year] = EndYear,

    REMOVEFILTERS( Data_Company ),

    Data_Company[Industry] = SelIndustry

)

VAR NumYears = EndYear - StartYear

VAR Result =

IF (

    NumYears > 0 && NOT ISBLANK ( StartValue ) && NOT ISBLANK ( EndValue ),

    DIVIDE ( EndValue, StartValue ) ^ (1 / NumYears) - 1

)

RETURN Result

[Anonymous]

Hi, @MSC 

Can you provide some of the sample data and what you expect the output to be? Please remove any sensitive data in advance. How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards,

Community Support Team _Charlotte

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

[MSC]

There is something I don`t understand with the error message outlined above. I get the error message when I calculate the CAGR in one formula (i.e. with VAR CAGR_3, see below). However, when I "split" the calculation in separate variables with exactly the same mathematical operations, it works (i.e. with VAR CAGR_3_manually, see below).

Why is that and how can I solve this? Is it caused by my nested IF statement specifying the conditions under which the different CAGR calculations should be applied?

Here is an example of an updated DAX formula (in bold), pay only attention to the calculation of CAGR_3.

CAGR_RoCE =

VAR StartYear =

CALCULATE (

    MIN ( 'Date_Table_Updated'[Year] ),

    ALLSELECTED ('Date_Table_Updated'[Year] )

)

VAR EndYear =

CALCULATE (

    MAX ( 'Date_Table_Updated'[Year] ),

    ALLSELECTED ('Date_Table_Updated'[Year] )

)

VAR StartValue =

CALCULATE (

    [Return on Capital Employed (RoCE)],

    REMOVEFILTERS ('Date_Table_Updated' ),

    'Date_Table_Updated'[Year] = StartYear

)

VAR EndValue =

CALCULATE (

    [Return on Capital Employed (RoCE)],

    REMOVEFILTERS ('Date_Table_Updated' ),

    'Date_Table_Updated'[Year] = EndYear

)

VAR StartValue_ABS = ABS(StartValue)

VAR EndValue_ABS = ABS(EndValue)

VAR NumYears = EndYear - StartYear

VAR CAGR_1 = DIVIDE ( EndValue, StartValue ) ^ (1 / NumYears) - 1

VAR CAGR_2 = (-1) * (DIVIDE ( EndValue, StartValue ) ^ (1 / NumYears) - 1)

VAR CAGR_3 = ((DIVIDE ( (EndValue + 2 * StartValue_ABS), StartValue_ABS)) ^ (1 / NumYears)) - 1

VAR CAGR_4 = (-1) * (DIVIDE ( ABS(EndValue) + 2 * StartValue, StartValue ) ^ (1 / NumYears) - 1)

VAR Result =

IF (

    StartValue > 0 && NOT ISBLANK ( StartValue ) && EndValue > 0 && NOT ISBLANK ( EndValue ),

    CAGR_1,

    IF (

        StartValue < 0 && NOT ISBLANK ( StartValue ) && EndValue < 0 && NOT ISBLANK ( EndValue ),

    CAGR_2,

    IF (

        StartValue < 0 && NOT ISBLANK ( StartValue ) && EndValue > 0 && NOT ISBLANK ( EndValue ),

    CAGR_3,

    IF (

        StartValue > 0 && NOT ISBLANK ( StartValue ) && EndValue < 0 && NOT ISBLANK ( EndValue ),

    CAGR_4,

    "n/a"

))))

VAR Numerator_CAGR_3 = EndValue + 2 * StartValue_ABS

VAR Denominator_CAGR_3 = StartValue_ABS

VAR Fraction_CAGR_3 = DIVIDE(Numerator_CAGR_3, Denominator_CAGR_3)

VAR Power_NumYears = 1 / NumYears

VAR final_Step_CAGR_3 = Fraction_CAGR_3 ^ (1 / NumYears)

VAR CAGR_3_manually = final_Step_CAGR_3 - 1

RETURN CAGR_3_manually

[AllisonKennedy]

@MSC Sometimes returning "N/A" within your formula causes issues, as it will create a measure that returns multiple data types, and can make it challenging if you want to use this measure in a graph. If you only want it in a table or card visual, then all good. 

You've already used the 'DIVIDE' function, this has an optional third argument you could add 'N/A' to or I sometimes use 0, -999 or something similar (depending on the requirements and uses) when working with numerics. 

You also already have an IF statement, so again just use the optional 3rd argument to add the 'N/A' result.

Here's an example:

VAR Result =

IF (

    NumYears > 0 && NOT ISBLANK ( StartValue ) && NOT ISBLANK ( EndValue ),

    DIVIDE ( EndValue, StartValue, "N/A") ^ (1 / NumYears) - 1,

    "N/A"

)

[MSC]

@AllisonKennedy  Thanks a lot for your comment! I`ve tried to add a 3rd argument which worked out in some cases. However, currently I get the error message "Error fetching data for this visual" - "MdxScript(Model) (1215, 14) Calculation error in measure ABC: An argument of function "POWER" has the wrong data type or the result is too large or too small.".
How can I fix this in my DAX formula?