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

[Anonymous]

Hi Everyone, I have a data in below format

Store	week	sales
X	9	555
X	8	647
X	7	889
X	6	846
X	5	558
X	4	993
Y	9	736
Y	8	746
Y	7	927
Y	6	601
Y	5	509
Y	4	589
Z	9	964
Z	8	578
Z	7	833
Z	6	992
Z	5	558
Z	4	739

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

	current week	Previous Avg	%diff
Store X	555	786.60	-29.4%
Store Y	736	674.40	9.1%
Store Z	964	740.00	30.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.

[Greg_Deckler · Accepted solution]

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])

[Greg_Deckler · Accepted solution]

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])

[Greg_Deckler · Accepted solution]

For % diff:

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

[Greg_Deckler · Accepted solution]

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])

[Greg_Deckler · Accepted solution]

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])

[Anonymous]

Thanks Greg, worked like a charm 🙂

[Greg_Deckler]

Awesome! 🙂

[GrahamKnowles]

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]

@Anonymous 

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