Rolling Average of Last 8 Weeks

[Jaf017]

I've tried numerous different ways of calculating a rolling 8 week average, but just can't seem to get it to work.

Through my errors I've experienced a result of the average being the same value of the week, blank, or all the same value for every location.

Below is a sample of my data, date key, and my datamodel. Also one of the DAX formulas I've used.

Data ModelData SampleDate Key

8 Week Avg Cases =

CALCULATE (

AVERAGEX ( 'Summary', 'Summary'[_Cases] ),

DATESINPERIOD (

'Date Key'[WE],

LASTDATE ( 'Date Key'[WE] ),

-56,

DAY

)

)

Any help on this would be greatly appreciated.

[amitchandak · Accepted solution]

@Jaf017 , Refer my blog for that

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

Rolling 8 week Sales = CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Week Rank]>=min('Date'[Week Rank])-8 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
or
Next 8 Week Sales =
Var _min = maxx(allselected('Date','Date'[Week Rank])
Var _max = maxx(allselected('Date','Date'[Week Rank]) -8
CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Week Rank]>=_min && 'Date'[Week Rank]<=_max))