topic Quick Measure: Incorrect YOY calculation - handling negatives in DAX Commands and Tips

Quick Measure: Incorrect YOY calculation - handling negatives

Anonymous — Thu, 04 Jun 2020 22:03:23 GMT

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

Re: Quick Measure: Incorrect YOY calculation - handling negatives

lbendlin — Fri, 05 Jun 2020 01:05:03 GMT

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.

Re: Quick Measure: Incorrect YOY calculation - handling negatives

stevedep — Fri, 05 Jun 2020 06:01:57 GMT

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.

Re: Quick Measure: Incorrect YOY calculation - handling negatives

Anonymous — Mon, 08 Jun 2020 00:10:57 GMT

Thank you Steve - you are indeed "Resolver" 🙂

Cheers,

Petek

Re: Quick Measure: Incorrect YOY calculation - handling negatives

Anonymous — Mon, 08 Jun 2020 02:05:22 GMT

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