Year-on-year variance not calculating correctly

richardmayo · ‎12-11-2019

Hi Community,

I'm new to Power BI but learning quickly. I am having trouble getting the YOY% showing correctly.

This is the "quick measure" im using

Sales ex GST YoY% = 
VAR __PREV_YEAR =
	CALCULATE(
		SUM('rms repairs'[Sales ex GST]),
		DATEADD('Date (Export)'[Date], -1, YEAR)
	)
RETURN
	DIVIDE(SUM('rms repairs'[Sales ex GST]) - __PREV_YEAR, __PREV_YEAR)

But you can see the % is not working correctly.... for instance, November shows YOY our Sales value is down, but the % is recording +6.94%....

Im using a date table as recommended which all other values are correct..... just this YOY variance I cant get right

parry2k · ‎12-11-2019

@richardmayo what measure you are using in Column Values section of this combo chart?

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

richardmayo · ‎12-11-2019

Hi @parry2k

Im using just the standard Sales ex GST. Ive attached a image so you can see

parry2k · ‎12-11-2019

@richardmayo this is your date table 'Date (Export)'[Date]? Is year filter you are using from this table? Did you market this table as date table?

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

richardmayo · ‎12-11-2019

@parry2k yep 'Date (Export)'[Date] is in the date table. Yep year filter is also in date table and yes date table is set as "date table"

I believe I have just fixed it.... I removed "Calendar Year Number" from the series and then changed to "Calendar Year Number" to just filter on 2019. The data is now right, but it only shows this years values in the column with the correct YOY %. Not sure why it isnt allowing me to show both years actuals with the YOY %.....