cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Nosheen
Frequent Visitor

Measure Calculation percentage difference of current week sales by avg of its previous sales

Hi Everyone, I have a data in below format

Storeweeksales
X9555
X8647
X7889
X6846
X5558
X4993
Y9736
Y8746
Y7927
Y6601
Y5509
Y4589
Z9964
Z8578
Z7833
Z6992
Z5558
Z4739

and I want to create a summary table (using power BI table visual) as per blow

 current weekPrevious Avg%diff
Store X555786.60-29.4%
Store Y736674.409.1%
Store Z964740.0030.3%

How can I do this more efficiently so that each week I don't have to manually filter anything .e.g., if week 10 sales are added then current week should be moved to week 10 and all previous weeks should be included in Previous week avg.

How to include this automation in measure % diff and other columns where I am currently using filters

Your help will be appreciated.

Thanks.

3 ACCEPTED SOLUTIONS
Greg_Deckler
Super User
Super User

Seems like:

 

Current Week Measure =
  VAR __Store = MAX('Table'[Store])
  VAR __MaxWeek = MAXX(FILTER('Table',[Store] = __Store),[week])
RETURN
  MAXX(FILTER('Table',[Store] = __Store && [week] = __MaxWeek),[sales])

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

Greg_Deckler
Super User
Super User

For previous average:

 

Previous Avg Measure =
  VAR __Store = MAX('Table'[Store])
  VAR __MaxWeek = MAXX(FILTER('Table',[Store] = __Store),[week])
RETURN
  AVERAGEX(FILTER('Table',[Store] = __Store && [week] <> __MaxWeek),[sales])

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

Greg_Deckler
Super User
Super User

For % diff:

%Diff Measure = 
  ([Current Week Measure] - [Previous Avg Measure]) / [Previous Avg Measure]

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

7 REPLIES 7
Greg_Deckler
Super User
Super User

For % diff:

%Diff Measure = 
  ([Current Week Measure] - [Previous Avg Measure]) / [Previous Avg Measure]

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

Greg_Deckler
Super User
Super User

For previous average:

 

Previous Avg Measure =
  VAR __Store = MAX('Table'[Store])
  VAR __MaxWeek = MAXX(FILTER('Table',[Store] = __Store),[week])
RETURN
  AVERAGEX(FILTER('Table',[Store] = __Store && [week] <> __MaxWeek),[sales])

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

Greg_Deckler
Super User
Super User

Seems like:

 

Current Week Measure =
  VAR __Store = MAX('Table'[Store])
  VAR __MaxWeek = MAXX(FILTER('Table',[Store] = __Store),[week])
RETURN
  MAXX(FILTER('Table',[Store] = __Store && [week] = __MaxWeek),[sales])

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

Thanks Greg, worked like a charm 🙂

Awesome! 🙂

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

GrahamKnowles
Resolver I
Resolver I

Assuming you are using just a numbered week rather than a date table, you could add a custom columns to your table to achieve this.

 

So create a custom column as:

Latest Week = MAX(Table[week])

 

then a second column to dictate if a line is in the current week or last week:

IsCurrentWeek = if(Table[week]=Table[Latest Week],"Current",if(Table[week]=(Table[Latest Week]-1),"Last Week","Not Current")
 
You can then make further calculations off these results.
 
Does this help?
 
amitchandak
Super User
Super User

@Nosheen 

Refer this file , How I creates Week , last/prior week using Rank. In case you have only one year data you can use Week

https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.