Comparing Average Values Indexed

[Anonymous]

I'm trying to compare 13 week averages and get the % change. 

I have a table with all my values by date. I then have a table with date and 13 week period.

I am able to relate date with date and get averages by 13 week period but I cannot figure out how to compare to the previous period. I created an index column in my attempt.

The logic I'm thinking is MEASURE = ((AVERAGE(VALUE), INDEX) - (AVERAGE(VALUE), INDEX - 1 )) / (AVERAGE(VALUE), INDEX - 1)

I'm not sure if I'm on the right track or how to accomplish this. Thanks in advance!

(Acomplishing this with a filter value works but is undesirable)

[Anonymous · Accepted solution]

@Anonymous 

I believe you are having the right logic, I create the demo of Running Average, if you want average to a specific index, just change the filter to  <=13, and <13. 

Measure= 
var currentaverage  = CALCULATE(AVERAGE([Value]),FILTER(ALL('Table'),MAXX(FILTER('Table',EARLIER('Table'[Index])<=[Index]),'Table'[Value])))
var previousaverage = CALCULATE(AVERAGE([Value]),FILTER(ALL('Table'),MAXX(FILTER('Table',EARLIER('Table'[Index])<[Index]),'Table'[Value])))
Return (currentaverage - previousaverage) / previousaverage

Best,
Paul

[Anonymous]

I modified it slightly to :

% Change =

var currentaverage  = CALCULATE(AVERAGE([Value]),FILTER(ALL('13W Averages'),MAXX(FILTER('13W Averages',EARLIER('13W Averages'[13W Index])=[13W Index]),'13W Averages'[Value])))

var previousaverage = CALCULATE(AVERAGE([Value]),FILTER(ALL('13W Averages'),MAXX(FILTER('13W Averages',EARLIER('13W Averages'[13W Index])=[13W Index]-1),'13W Averages'[Value])))

Return (currentaverage - previousaverage) / previousaverage

I think our table might be set up in reverse but you got me 99.9% of the way there! Thank you very much. This was plaguing me for days!