Trailing 12 Week Average

[Anonymous]

Hi everyone, 

I have just started using Power BI and DAX however I have been struggling with a trailing average measure. My data is sorted like the example data below.

The problem I am having is that all functions seem to aim at dates instead of week numbers.

What formula could create a trailing average for 12 weeks excluding the current week? 

Currently I am using something like this 

Measure = 
  VAR __CurrentWeek = WEEKNUM(TODAY())
  VAR __StartWeek = __CurrentWeek - 13
RETURN
  AVERAGEX(FILTER('Table', [Week No] >= __StartWeek && [Week No] < __CurrentWeek),[Salesvalue])

However, this takes an average of the individual vales for sales value. I need the averages for the sum of the entire week. 

Hope someone can help me.

Kind regards, 

Majesteit 

Week No	Salesvalue	Parameter 1	Parameter 2
1	345	Promo	Store
1	34	Regular	Online
2	21	Promo	Store
2	1245	Regular	Online
3	5636	Promo	Store
3	37	Regular	Online
4	35	Promo	Store

[amitchandak · Accepted solution]

@Anonymous , I would Advice to use week Rank, inplace of week num,

Try a meausre like

Avg Last 13 weeks = CALCULATE(Averagex(Values('Date'[Week Rank]), [Sales Value]) , FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-13 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

Where Week Rank is column

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