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 ye...
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. 

stevedep's avatar
stevedep
Icon for Memorable Member rankMemorable 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