Same measure shows different results in different visuals.

[jwin2424]

Hello community! This is driving me crazy. Here is my measure:

Opp YoY = 
VAR _PriorYR = 
CALCULATE(
    SUM('Opportunity Product'[extendedamount]
    ),
    Opportunity[createdon].[Year] = MAX (Opportunity[createdon].[Year]) -1
)
VAR _CurrentYR = sum('Opportunity Product'[extendedamount])
RETURN
DIVIDE(_CurrentYR-_PriorYR,_PriorYR)

When I put this measure into a table, it yields the correct rate of change. When I put this SAME measure into a bar chart for the line, the results ADD 1. It should say 17%, but it says 117%. If I add -1 to the formula, then the table also minuses 1, and I don't want that. I want BOTH to say 17%. What am I missing?

EDIT: I figured I should also note that if I remove a 2021 from the bar chart, then the % is correct. It is only when I add another year to the bar chart that it becomes 117% instead of 17%.

[jwin2424 · Accepted solution]

I found a solution. I used ChatGPT (OpenAI) and asked it the question, and it actually solved it. The error was that I was using the sum of the value and not the sum of the max value. Here is the correct code:

Opp YoY $ Measure = 
VAR _PriorYR = 
CALCULATE(
    SUM('Opportunity Product'[extendedamount]
    ),
    Opportunity[createdon].[Year] = MAX (Opportunity[createdon].[Year]) -1
)
VAR _CurrentYR = 
CALCULATE(
    SUM('Opportunity Product'[extendedamount]
    ),
    Opportunity[createdon].[Year] = MAX (Opportunity[createdon].[Year])
)

RETURN
DIVIDE(_CurrentYR-_PriorYR,_PriorYR)

[FreemanZ]

hi @jwin2424 

it seems you have month on the x axis, but it cant differentiate it is month of 2021 or 2022, so it sums.

To make YoY comparison, try to use DAX code to make the time/year shift, like DATEADD function.

check this post:

https://community.powerbi.com/t5/Desktop/DATEADD-function/td-p/2113441

[jwin2424]

Hi @FreemanZ ,

* Data updated, so percentages dont match the first post.

I took the measure and moved it from X to Y on the table, and the % is still correct for each month except for the grand total. The total is essentially taking 2022 and 2021 sum and dividing it by 2021. So you are correct that putting it on the X axis is causing this. The bar chart is giving me the Grand Total of the two years and not the total of the month. 

I can't use DATEADD because I am on a fiscal calendar, and the YoY by month won't be accurate. I have already tried this, and the numbers aren't correct. 

Any other suggestions?