% comp works for 1 week, but breaks with 4 week view?

[JoeNewton]

Hi, 

I have successfully added a % comp to my one week view that looks like this for headers:

Retailer | 2022 sales | 2021 sales | % comp |

But in my 4 week tab, which has the following relative dates, it doesn't work:

Date added to filter, relative date, is in the last 4 weeks. 

The problem is that the amounts are correct, but it should be a negative number but it is positive. I have no idea why or how to fix. For example, it says 6% when it should be -6%. 

Hopefully this all makes sense, thanks!

[amitchandak]

@JoeNewton ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Usually formula is divide( [current] -[prior],[prior])

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

[JoeNewton · Accepted solution]

OK so I found the issue. It was calculating correctly, but power bi was not showing the negative. I have no idea why not, but when I hovered my mouse over it I was able to see that it was in fact negative. I changed the format to red to show this. 

[JoeNewton]

So when I use your solution the % values get very wrong for some reason.

YOY Growth % = DIVIDE('Top Customers Data'[POS $]-'Top Customers Data'[POS $ LY], 'Top Customers Data'[POS $ LY])

This gets:

Retailer: X

POS $: 608k

POS $ LY: 644k

YOY Growth %: 598%

Before i had:

POS $ LY % difference from POS $ =

VAR __BASELINE_VALUE = SUM('Top Customers Data'[POS $])

VAR __VALUE_TO_COMPARE = SUM('Top Customers Data'[POS $ LY])

RETURN

    DIVIDE(__VALUE_TO_COMPARE - __BASELINE_VALUE, __BASELINE_VALUE)

Which got the correct % but negative

@amitchandak Added an @ to you in case you lost the thread 🙂