Powerpivot Rolling Average Calculated Measure

I am very new to powerpivot and DAX functions and I need to calculate a rolling average without using any of the date/time DAX functions as I do not have any dates, I only have a value representing a # of days. All I can find is rolling average instructions using the specific date functions and can't figure out how to modify for my needs.

I have a Column that has values 0,1,2,3,...... representing a number of days. I have a column with corresponding production values for each of those days. I need to know the 7 day average for each #of days. Obviously the first 7 days won't be a 7 day average - because there won't be seven days worth of production data. So up to the first seven days I just want the 1 day average for day 1, 2 day average for day 2, 3 day average for day 3, etc.

Any suggestions would be extremely helpful.

Thank You.

I am not sure I understand the question. I tried to atach my file - but it is too big. So I attached a sample file. The Table I have included here is a pivot table from powerpivot and each of the columns have been created using a calculated measure.

What I would like is another calculated measure that takes the No Zeros Average Oil Rate and gives me the seven day average in column H.

These are the results I would like.

So Day 0 will just be 98
Day 1 - (222+98)/2
Day 2 - (279+22+98)/3
Day 3 - (245+279+222+98)/4
Day 4 - (246+246+279+222+98)/5
Day 5 - (263+246+245+279+222+98)/6
Day 6 - (294+263+246+245+279+222+98)/7
Day 7 - (312+297+263+246+245+279+222)/7
Day 8 - (294+312+297+263+246+245+279)/7
Day 9 - (246+294+312+297+263+246+245)/7
etc......


Does that makes sense?

PowerPivotHelp.xlsx

Okay- The powerpivot table nozeros apparently isn't linked to my source data table - which I didn't realize until just now when I updated the source data and powerpivot table didn't update. But - I scaled down the sourcedata to only include 2013 dates and that seemed to make it small enough to attach.

So the No Zeros table is just filtered to only include 2013 so it matches the source data even though they aren't actually linked. I will fix that later - I am not sure if there is a way to link it after the fact- but that is beside the point.

Measure 1 is where I was attempting to create the 7 day average.

Thanks bunches if you can figure this out I know it is kind of a mess.

Formula looks great - one question.

Do I make the Sum of No Zeros Average Oil Rate another calculated measure? Or can I do that right in this formula? My No Zeros Average Oil Rate is the result of another calculated measure so I am not sure where to do this sum?

if(countrows(values(Table1[Days])) = 1,
CALCULATE(
AVERAGEX(VALUES(Table1[Days]), Table1[Sum of No Zeros Average Oil Rate])
,Table1[Days] <= VALUES(Table1[Days]) && Table1[Days] > VALUES(Table1[Days])-7
)
, blank())

It doesn't seem to allow that.

This is my formula and it returns an error. I tried without the calculate function as well - which also returned an error. I am assuming because the sum function wants a column and not a calculated measure but I am not sure. Any ideas?


Sum of No Zeros Average Oil Rate:=CALCULATE(SUM('No Zeros'[No Zeros Average Oil Rate]))