Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Quick Measure: Incorrect YOY calculation - handling negatives

Hello,

 

YOY quick measure in Power BI results incorrect calculation when previous year data is negative or vice versa. Following quick measure formula results a negative 217% YOY where previous year value is minus 463 and current year value is plus 541. 

 
YTD_Total Sales Gross YoY% =
VAR __PREV_YEAR =
    CALCULATE(
        SUM('Append1'[YTD_Total Sales Gross]),
        DATEADD('Date_Key'[Date], -1, YEAR)
    )
RETURN
    DIVIDE(SUM('Append1'[YTD_Total Sales Gross]) - __PREV_YEAR, __PREV_YEAR)
 
Is there a way to edit the quick measure to add ABS? I'd prefer not create seperate measures for each metric we are tracking and calculate the YOY for each. It will be a very tedious workaround.
 
Thanks,
Petek
DAX Calculation
Measures
Query String
  • stevedep's avatar
    stevedep
    6 years ago

    Hi,

    Your formula would be:

    YTD_Total Sales Gross YoY% =
VAR __PREV_YEAR =
    CALCULATE(
        SUM('Append1'[YTD_Total Sales Gross]),
        DATEADD('Date_Key'[Date], -1, YEAR)
    )
RETURN
    DIVIDE(SUM('Append1'[YTD_Total Sales Gross]) - __PREV_YEAR, ABS(__PREV_YEAR)

     

    Notice the ABS wrapped around the Denominator in the Divide statement. 

     

    Please mark as solution if this helps you. Kubos are much appreciated.

     

    Kind regards, Steve. 

4 Replies

  • stevedep's avatar
    stevedep
    Memorable Member
    6 years ago

    Hi,

    Your formula would be:

    YTD_Total Sales Gross YoY% =
VAR __PREV_YEAR =
    CALCULATE(
        SUM('Append1'[YTD_Total Sales Gross]),
        DATEADD('Date_Key'[Date], -1, YEAR)
    )
RETURN
    DIVIDE(SUM('Append1'[YTD_Total Sales Gross]) - __PREV_YEAR, ABS(__PREV_YEAR)

     

    Notice the ABS wrapped around the Denominator in the Divide statement. 

     

    Please mark as solution if this helps you. Kubos are much appreciated.

     

    Kind regards, Steve. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you Steve - you are indeed "Resolver" 🙂

       

      Cheers,

      Petek

  • lbendlin's avatar
    lbendlin
    Super User
    6 years ago

    Why would you want to do an ABS conversion?  What is your definition of YoY performance when the previous year was -20 and this year is 30 ? IMHO you should BLANK() out results where the prior year was negative.  Unless both are negative, for example prior year -20 and this year -10 ?  Nah, still doesn't make sense.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you for your response. I hear you, but in our world none of the YOY or growth numbers alone make sense unless we present them with context and a narrative. In this scenario, pre-built quick measure is giving a negative growth although it is positive.  

       
       
       
         w/ABSw/out ABS
      CurrentPreviousIncrease (Decrease)% Growth (YOY)% Growth (YOY)
           
      1055100%100%
      510-5-50%-50%
      10-515300%-300%
      -105-15-300%-300%
      -5-10550%-50%
      -10-5-5-100%100%

       

      In the spirit of providing solutions, how do you suggest the quick measure to be edited so it returns BLANK(). Your response might help others whose scenario fits the definition.

       

      Thank you,

      Petek