Forum Discussion
Quick Measure: Incorrect YOY calculation - handling negatives
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.
Super UserWhy 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.
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