Quick Measure: Incorrect YOY calculation - handling negatives

[Anonymous]

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

[stevedep · Accepted solution]

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]

Thank you Steve - you are indeed "Resolver" 🙂

Cheers,

Petek

[lbendlin]

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]

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/ABS	w/out ABS
Current	Previous	Increase (Decrease)	% Growth (YOY)	% Growth (YOY)
10	5	5	100%	100%
5	10	-5	-50%	-50%
10	-5	15	300%	-300%
-10	5	-15	-300%	-300%
-5	-10	5	50%	-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