% change average for Graph with multiple weeks

Question

Hi everyone,&nbsp;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&nbsp;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])&nbsp;Item NbrItem Desc 1StoreUnit RetailUnit CostWeekPOS SalesPOS QtyLY POS QtyLY POS Sales776765Jerky Stick110920230500101776765Jerky Stick2109202305202303776765Jerky Stick31092023050000776765Jerky Stick41092023050000776765Jerky Stick5109202305202202776765Jerky Stick610920230500303776765Jerky Stick7109202305404202776765Jerky Stick810920230500303776765Jerky Stick910920230560600776765Jerky Stick10109202305202101776765Jerky Stick1110920230500101776765Jerky Stick12109202305101303776765Jerky Stick131092023050000776765Jerky Stick14109202305101202776765Jerky Stick15109202305101101776765Jerky Stick16109202305303202776765Jerky Stick1710920230500202776765Jerky Stick1810920230510100776765Jerky Stick1910920230510100776765Jerky Stick20109202305101303776765Jerky Stick2110920230500303776765Jerky Stick22109202305101101776765Jerky Stick23109202305101202776765Jerky Stick2410920230510100776765Jerky Stick25109202305101202776765Jerky Stick2610920230500101776765Jerky Stick2710920230500303776765Jerky Stick28109202305202303776765Jerky Stick2910920230500101776765Jerky Stick3010920230500303776765Jerky Stick3110920230500303776765Jerky Stick3210920230500101776765Jerky Stick3310920230500303776765Jerky Stick34109202305606303776765Jerky Stick3510920230500303776765Jerky Stick361092023050000776765Jerky Stick3710920230500101776765Jerky Stick3810920230530300776765Jerky Stick3910920230520200776765Jerky Stick40109202305101303776765Jerky Stick411092023050000776765Jerky Stick421092023050000

amitchandak · Answer

kurtrod , You need to have table with Year(FY), Week, and year week and join it back with you table 
&nbsp;
Create a new week rank column
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format 
&nbsp;
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))
&nbsp;
This Week= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) &amp;&amp; 'Date'[Week] = Max('Date'[Week]) ))Last Year Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 &amp;&amp; 'Date'[Week] = Max('Date'[Week])))
&nbsp;
&nbsp;
Power BI — Week on Week and WTD https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123https://www.youtube.com/watch?v=pnAesWxYgJ8

kurtrod · Answer

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.

grantsamborn · Answer

Hi&nbsp;kurtrod&nbsp;Just a couple of questions1 - What type of graph did you want?&nbsp; (I'm just trying to picture your expected end result.)2 - The sample data is for 42 stores but just 1 week.&nbsp; Is there any way you can come up with more sample data that makes sense?&nbsp; (I've tried to come up with random data for a 6 week period but the numbers obviously wouldn't make any sense.)&nbsp;Let me know if you have any questions.&nbsp;

kurtrod · Answer

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=110709759988858120884&rtpof=true&sd=true

Here is what I am trying to produce:

grantsamborn · Answer

Hi&nbsp;kurtrod&nbsp;Can you double-check your dataset?&nbsp; It looks like [LY POS Qty] and [LY POS Sales] are reversed.Grant&nbsp;

Forum Discussion

% change average for Graph with multiple weeks

5 Replies

Related content

Daily Average Over Multiple Years

Average and Moving Average

Average for each small multiple

Weekly Average

Accumulated average

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