% change average for Graph with multiple weeks

[kurtrod]

Hi everyone,

I am trying to get an average of YoY at weekly level to plot in a graph. When I plot it in the graph, it doesn't turn out correctly due to all of the zeroes where I had no sales last year and the actual zeroes from the data. I have attached an excerpt of the data set I am using. The current calculation I am using is 

Sales % Diff = DIVIDE('FYTD and LY Current Item Data'[POS Sales]-'FYTD and LY Current Item Data'[LY POS Sales],'FYTD and LY Current Item Data'[LY POS Sales])

Item Nbr	Item Desc 1	Store	Unit Retail	Unit Cost	Week	POS Sales	POS Qty	LY POS Qty	LY POS Sales
776765	Jerky Stick	1	10	9	202305	0	0	10	1
776765	Jerky Stick	2	10	9	202305	20	2	30	3
776765	Jerky Stick	3	10	9	202305	0	0	0	0
776765	Jerky Stick	4	10	9	202305	0	0	0	0
776765	Jerky Stick	5	10	9	202305	20	2	20	2
776765	Jerky Stick	6	10	9	202305	0	0	30	3
776765	Jerky Stick	7	10	9	202305	40	4	20	2
776765	Jerky Stick	8	10	9	202305	0	0	30	3
776765	Jerky Stick	9	10	9	202305	60	6	0	0
776765	Jerky Stick	10	10	9	202305	20	2	10	1
776765	Jerky Stick	11	10	9	202305	0	0	10	1
776765	Jerky Stick	12	10	9	202305	10	1	30	3
776765	Jerky Stick	13	10	9	202305	0	0	0	0
776765	Jerky Stick	14	10	9	202305	10	1	20	2
776765	Jerky Stick	15	10	9	202305	10	1	10	1
776765	Jerky Stick	16	10	9	202305	30	3	20	2
776765	Jerky Stick	17	10	9	202305	0	0	20	2
776765	Jerky Stick	18	10	9	202305	10	1	0	0
776765	Jerky Stick	19	10	9	202305	10	1	0	0
776765	Jerky Stick	20	10	9	202305	10	1	30	3
776765	Jerky Stick	21	10	9	202305	0	0	30	3
776765	Jerky Stick	22	10	9	202305	10	1	10	1
776765	Jerky Stick	23	10	9	202305	10	1	20	2
776765	Jerky Stick	24	10	9	202305	10	1	0	0
776765	Jerky Stick	25	10	9	202305	10	1	20	2
776765	Jerky Stick	26	10	9	202305	0	0	10	1
776765	Jerky Stick	27	10	9	202305	0	0	30	3
776765	Jerky Stick	28	10	9	202305	20	2	30	3
776765	Jerky Stick	29	10	9	202305	0	0	10	1
776765	Jerky Stick	30	10	9	202305	0	0	30	3
776765	Jerky Stick	31	10	9	202305	0	0	30	3
776765	Jerky Stick	32	10	9	202305	0	0	10	1
776765	Jerky Stick	33	10	9	202305	0	0	30	3
776765	Jerky Stick	34	10	9	202305	60	6	30	3
776765	Jerky Stick	35	10	9	202305	0	0	30	3
776765	Jerky Stick	36	10	9	202305	0	0	0	0
776765	Jerky Stick	37	10	9	202305	0	0	10	1
776765	Jerky Stick	38	10	9	202305	30	3	0	0
776765	Jerky Stick	39	10	9	202305	20	2	0	0
776765	Jerky Stick	40	10	9	202305	10	1	30	3
776765	Jerky Stick	41	10	9	202305	0	0	0	0
776765	Jerky Stick	42	10	9	202305	0	0	0	0

[grantsamborn]

Hi @kurtrod 

Just a couple of questions

1 - What type of graph did you want?  (I'm just trying to picture your expected end result.)

2 - The sample data is for 42 stores but just 1 week.  Is there any way you can come up with more sample data that makes sense?  (I've tried to come up with random data for a 6 week period but the numbers obviously wouldn't make any sense.)

Let me know if you have any questions.

[kurtrod]

Here is a link to a full data set for this one item. I have several items in my actual dataset:

https://docs.google.com/spreadsheets/d/1khw4L_d2Lmaj5J88THBaPgrlUTjFTyxO/edit?usp=sharing&ouid=11070... 

Here is what I am trying to produce:

[grantsamborn]

Hi @kurtrod 

Can you double-check your dataset?  It looks like [LY POS Qty] and [LY POS Sales] are reversed.

Grant

[amitchandak]

@kurtrod , You need to have table with Year(FY), Week, and year week and join it back with you table

Create a new week rank column

Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format

then have measure

This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

This Week= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Week] = Max('Date'[Week]) ))
Last Year Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Week] = Max('Date'[Week])))

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

[kurtrod]

Does it matter that I don't have last year in a separate row or showing at all? I have the one year week and it has this week column and last year same week column.