Forum Discussion

jmorela1's avatar
jmorela1
Icon for Helper I rankHelper I
7 months ago
Solved

YOY change incorrect when prior year value is negative

I am having an issue that YOY percent change is not correct with the prior year value is negative. If I wrap it in ABS, then the calculation is incorrect when the prior year is positive.

 

YoY Net Growth Change = iferror((([TotalNetGrowthCurrentYear]-[TotalNetGrowthPreviousYear])/[TotalNetGrowthPreviousYear]),0)
 
TotalNetGrowthPreviousYear =
CALCULATE(
SUM('Monthly Growth Tracking'[Net Monthly Change]),
'Monthly Growth Tracking'[Year]= SELECTEDVALUE('Monthly Growth Tracking'[Year]) - 1
)

 

TotalNetGrowthCurrentYear =
CALCULATE(
SUM('Monthly Growth Tracking'[Net Monthly Change]),
'Monthly Growth Tracking'[Year] = SELECTEDVALUE('Monthly Growth Tracking'[Year])
)

 

Example:

  • Demert's avatar
    Demert
    7 months ago

    Hi jmorela1 what is the expected output ? Because the calculation is correctly doing what is expected.
    (13,145- (-7,455)) = 20,600 / -7,455 = -2.76. 

     

    It's not possible to show how much % your growth increased since it went from negative to positive.

6 Replies

  • Demert's avatar
    Demert
    Icon for Resolver III rankResolver III
    7 months ago

    Hi jmorela1 what is the expected output ? Because the calculation is correctly doing what is expected.
    (13,145- (-7,455)) = 20,600 / -7,455 = -2.76. 

     

    It's not possible to show how much % your growth increased since it went from negative to positive.

    • jmorela1's avatar
      jmorela1
      Icon for Helper I rankHelper I
      7 months ago

      I would expect the value to be +276% change YOY (instead of the -276% currently calculated)

      • Demert's avatar
        Demert
        Icon for Resolver III rankResolver III
        7 months ago

        Depending on what the scenario is for other examples you have many options.

         

        Perhaps a simple solution would be *-1 for this specific case.

         

        But I assume it's more complex than that. Since it would make the example in email 2 also positive which isn't the expected output. My suggestion would be to create a switch statement in your YoY for each possible output (prev year negative  and curr year positive, both years negative, ...) since all them have a different expected output and thus a different calculation

  • Kedar_Pande's avatar
    Kedar_Pande
    Icon for Super User rankSuper User
    7 months ago

     Standard DIVIDE handles negative denominators correctly:

      
    YoY Net Growth Change = 
    DIVIDE(
    [TotalNetGrowthCurrentYear] - [TotalNetGrowthPreviousYear],
    [TotalNetGrowthPreviousYear],
    0
    )

     

    Your iferror() wrapper + manual subtraction breaks it. DIVIDE gives proper % change regardless of sign. Ditch the iferror.

     

    If this answer helped, please click ? or Accept as Solution.
    -Kedar
    LinkedIn: https://www.linkedin.com/in/kedar-pande

     

     

    jmorela1

    • jmorela1's avatar
      jmorela1
      Icon for Helper I rankHelper I
      7 months ago

      This still results in an incorrectly negative percentage when the previous year is negative. This value should be +276%

       

  • FBergamaschi's avatar
    FBergamaschi
    Icon for Super User rankSuper User
    7 months ago

    Hi jmorela1 

    I confirm what was said by the others, the % change when there is a change in sign is complex. I suggest to create yourself a simple excel file and reproduce what you want to obtain when the sign changes. Once you are satisfied, we can help you implement that in DAX.

     

    If this helped, please consider giving kudos and mark as a solution

    @me in replies or I'll lose your thread

    Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

    Consider voting this Power BI idea

    Francesco Bergamaschi

    MBA, M.Eng, M.Econ, Professor of BI