Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
kurtrod
Frequent Visitor

% change average for Graph with multiple weeks

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 NbrItem Desc 1StoreUnit RetailUnit CostWeekPOS SalesPOS QtyLY POS QtyLY POS Sales
776765Jerky Stick110920230500101
776765Jerky Stick2109202305202303
776765Jerky Stick31092023050000
776765Jerky Stick41092023050000
776765Jerky Stick5109202305202202
776765Jerky Stick610920230500303
776765Jerky Stick7109202305404202
776765Jerky Stick810920230500303
776765Jerky Stick910920230560600
776765Jerky Stick10109202305202101
776765Jerky Stick1110920230500101
776765Jerky Stick12109202305101303
776765Jerky Stick131092023050000
776765Jerky Stick14109202305101202
776765Jerky Stick15109202305101101
776765Jerky Stick16109202305303202
776765Jerky Stick1710920230500202
776765Jerky Stick1810920230510100
776765Jerky Stick1910920230510100
776765Jerky Stick20109202305101303
776765Jerky Stick2110920230500303
776765Jerky Stick22109202305101101
776765Jerky Stick23109202305101202
776765Jerky Stick2410920230510100
776765Jerky Stick25109202305101202
776765Jerky Stick2610920230500101
776765Jerky Stick2710920230500303
776765Jerky Stick28109202305202303
776765Jerky Stick2910920230500101
776765Jerky Stick3010920230500303
776765Jerky Stick3110920230500303
776765Jerky Stick3210920230500101
776765Jerky Stick3310920230500303
776765Jerky Stick34109202305606303
776765Jerky Stick3510920230500303
776765Jerky Stick361092023050000
776765Jerky Stick3710920230500101
776765Jerky Stick3810920230530300
776765Jerky Stick3910920230520200
776765Jerky Stick40109202305101303
776765Jerky Stick411092023050000
776765Jerky Stick421092023050000
5 REPLIES 5
grantsamborn
Solution Sage
Solution Sage

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.

 

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:

 

kurtrod_0-1678195522632.png

 

Hi @kurtrod 

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

Grant

 

amitchandak
Super User
Super User

@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

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.