# Powerpivot Rolling Average Calculated Measure

1. ## 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.  Register To Reply

2. ## Re: Powerpivot Rolling Average Calculated Measure

do you want the average of the total daily production for each of the last seven days, or the average production amount in your source table for the last 7 days-i.e. the average of the individual table rows, or the average of the daily totals?

a sample workbook with an excel table representative of the real source table data (censored as need be) would be helpful  Register To Reply

3. ## Re: Powerpivot Rolling Average Calculated Measure

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  Register To Reply

4. ## Re: Powerpivot Rolling Average Calculated Measure

ok so you want the average of the totals. can you strip the file down to a smaller sample of the data and post that so we can see the real data structure and the current measures?

in case you can't provide a file, the formula should be something like this

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

replacing Table1 with your actual table name  Register To Reply

5. ## Re: Powerpivot Rolling Average Calculated Measure

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.  Register To Reply

6. ## Re: Powerpivot Rolling Average Calculated Measure

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())  Register To Reply

7. ## Re: Powerpivot Rolling Average Calculated Measure

I'd keep it as a separate measure  Register To Reply

8. ## Re: Powerpivot Rolling Average Calculated Measure

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]))  Register To Reply

9. ## Re: Powerpivot Rolling Average Calculated Measure

for your measure the formula oughta be

=if(countrows(values('No Zeros'[Days])) = 1,
CALCULATE(
AVERAGEX(VALUES('No Zeros'[Days]), 'No Zeros'[No Zeros Average Oil Rate])
,'No Zeros'[Days] <= VALUES('No Zeros'[Days]) && 'No Zeros'[Days] > VALUES('No Zeros'[Days])-7
)
, blank())  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 