Rolling average of previous number of days totals

L

Legacy 167309

Guest
Hi.

Using PowerPivot I am looking to generate the rolling average of total daily sales value per person for the previous seven days, inclusive of days where there are no sales for that individual. I have considered a measure using a calculate function with DATESINPERIOD but I only get the average of all the values within that date range as opposed to an average of the sum of each day.

avg 7day Sales:=CALCULATE(average('SalesData'[sales value]),DATESINPERIOD(DatesTable[Date],PREVIOUSDAY(DatesTable[Date]),-7,DAY),ALL(DatesTable[Date]))


I know this will be simple but I’ve been thinking about it for too long. Could anyone help or guide me in the relevant direction.

Thanks.

G

Prefered outcome:
24/06/20167500
25/06/20167471
26/06/20167471
27/06/20167471
28/06/20166357
29/06/20165686
30/06/20165686
01/07/20165771

<tbody>
</tbody>


Example data:




WeekDatePersonSales Value
Wk 106/06/2016Person 11700
Wk 106/06/2016Person 11400
Wk 106/06/2016Person 101600
Wk 106/06/2016Person 101300
Wk 106/06/2016Person 21800
Wk 106/06/2016Person 21500
Wk 106/06/2016Person 31500
Wk 106/06/2016Person 31200
Wk 106/06/2016Person 41800
Wk 106/06/2016Person 41500
Wk 106/06/2016Person 5400
Wk 106/06/2016Person 5100
Wk 106/06/2016Person 61700
Wk 106/06/2016Person 61400
Wk 106/06/2016Person 71600
Wk 106/06/2016Person 71300
Wk 106/06/2016Person 81600
Wk 106/06/2016Person 81300
Wk 106/06/2016Person 91800
Wk 107/06/2016Person 11900
Wk 107/06/2016Person 11600
Wk 107/06/2016Person 102000
Wk 107/06/2016Person 101700
Wk 107/06/2016Person 22100
Wk 107/06/2016Person 21800
Wk 107/06/2016Person 32500
Wk 107/06/2016Person 32200
Wk 107/06/2016Person 42200
Wk 107/06/2016Person 52600
Wk 107/06/2016Person 52300
Wk 107/06/2016Person 62100
Wk 107/06/2016Person 61800
Wk 107/06/2016Person 72900
Wk 107/06/2016Person 72600
Wk 107/06/2016Person 82600
Wk 107/06/2016Person 92200
Wk 107/06/2016Person 91900
Wk 109/06/2016Person 15000
Wk 109/06/2016Person 14700
Wk 109/06/2016Person 104700
Wk 109/06/2016Person 104400
Wk 109/06/2016Person 25200
Wk 109/06/2016Person 24900
Wk 109/06/2016Person 34000
Wk 109/06/2016Person 33700
Wk 109/06/2016Person 44700
Wk 109/06/2016Person 44400
Wk 109/06/2016Person 54200
Wk 109/06/2016Person 53900
Wk 109/06/2016Person 64300
Wk 109/06/2016Person 64000
Wk 109/06/2016Person 75900
Wk 109/06/2016Person 75600
Wk 109/06/2016Person 84700
Wk 109/06/2016Person 84400
Wk 109/06/2016Person 94600
Wk 109/06/2016Person 94300
Wk 110/06/2016Person 13300
Wk 110/06/2016Person 13000
Wk 110/06/2016Person 103100
Wk 110/06/2016Person 102800
Wk 110/06/2016Person 32800
Wk 110/06/2016Person 43100
Wk 110/06/2016Person 42800
Wk 110/06/2016Person 53100
Wk 110/06/2016Person 52800
Wk 110/06/2016Person 63100
Wk 110/06/2016Person 62800
Wk 110/06/2016Person 73300
Wk 110/06/2016Person 73000
Wk 110/06/2016Person 83000
Wk 110/06/2016Person 82700
Wk 110/06/2016Person 93200
Wk 213/06/2016Person 11400
Wk 213/06/2016Person 11100
Wk 213/06/2016Person 101400
Wk 213/06/2016Person 101100
Wk 213/06/2016Person 31400
Wk 213/06/2016Person 31100
Wk 213/06/2016Person 41800
Wk 213/06/2016Person 41500
Wk 213/06/2016Person 51800
Wk 213/06/2016Person 51500
Wk 213/06/2016Person 61400
Wk 213/06/2016Person 61100
Wk 213/06/2016Person 71400
Wk 213/06/2016Person 81400
Wk 213/06/2016Person 81100
Wk 213/06/2016Person 91400
Wk 213/06/2016Person 91100
Wk 214/06/2016Person 15600
Wk 214/06/2016Person 15300
Wk 214/06/2016Person 104900
Wk 214/06/2016Person 104600
Wk 214/06/2016Person 25200
Wk 214/06/2016Person 34100
Wk 214/06/2016Person 33800
Wk 214/06/2016Person 44900
Wk 214/06/2016Person 54400
Wk 214/06/2016Person 54100
Wk 214/06/2016Person 64400
Wk 214/06/2016Person 76500
Wk 214/06/2016Person 76200
Wk 214/06/2016Person 84800
Wk 214/06/2016Person 84500
Wk 214/06/2016Person 95200
Wk 214/06/2016Person 94900
Wk 216/06/2016Person 13500
Wk 216/06/2016Person 13200
Wk 216/06/2016Person 102800
Wk 216/06/2016Person 102500
Wk 216/06/2016Person 22800
Wk 216/06/2016Person 22500
Wk 216/06/2016Person 32500
Wk 216/06/2016Person 32200
Wk 216/06/2016Person 43400
Wk 216/06/2016Person 43100
Wk 216/06/2016Person 53200
Wk 216/06/2016Person 62300
Wk 216/06/2016Person 62000
Wk 216/06/2016Person 73100
Wk 216/06/2016Person 72800
Wk 216/06/2016Person 82600
Wk 216/06/2016Person 82300
Wk 216/06/2016Person 93500
Wk 216/06/2016Person 93200
Wk 217/06/2016Person 16000
Wk 217/06/2016Person 15700
Wk 217/06/2016Person 105900
Wk 217/06/2016Person 105600
Wk 217/06/2016Person 23600
Wk 217/06/2016Person 23300
Wk 217/06/2016Person 35100
Wk 217/06/2016Person 56000
Wk 217/06/2016Person 55700
Wk 217/06/2016Person 65800
Wk 217/06/2016Person 65500
Wk 217/06/2016Person 75700
Wk 217/06/2016Person 75400
Wk 217/06/2016Person 85700
Wk 217/06/2016Person 96100
Wk 217/06/2016Person 95800
Wk 320/06/2016Person 12200
Wk 320/06/2016Person 11900
Wk 320/06/2016Person 102000
Wk 320/06/2016Person 101700
Wk 320/06/2016Person 22100
Wk 320/06/2016Person 21800
Wk 320/06/2016Person 31900
Wk 320/06/2016Person 42300
Wk 320/06/2016Person 42000
Wk 320/06/2016Person 52300
Wk 320/06/2016Person 52000
Wk 320/06/2016Person 61900
Wk 320/06/2016Person 61600
Wk 320/06/2016Person 72100
Wk 320/06/2016Person 71800
Wk 320/06/2016Person 81900
Wk 320/06/2016Person 81600
Wk 320/06/2016Person 92100
Wk 320/06/2016Person 91800
Wk 321/06/2016Person 15500
Wk 321/06/2016Person 15200
Wk 321/06/2016Person 104800
Wk 321/06/2016Person 104500
Wk 321/06/2016Person 34100
Wk 321/06/2016Person 33800
Wk 321/06/2016Person 44700
Wk 321/06/2016Person 44400
Wk 321/06/2016Person 54000
Wk 321/06/2016Person 53700
Wk 321/06/2016Person 64400
Wk 321/06/2016Person 64100
Wk 321/06/2016Person 76000
Wk 321/06/2016Person 75700
Wk 321/06/2016Person 84500
Wk 321/06/2016Person 84200
Wk 321/06/2016Person 95000
Wk 321/06/2016Person 94700
Wk 323/06/2016Person 13700
Wk 323/06/2016Person 13400
Wk 323/06/2016Person 102600
Wk 323/06/2016Person 102300
Wk 323/06/2016Person 22600
Wk 323/06/2016Person 22300
Wk 323/06/2016Person 32400
Wk 323/06/2016Person 32100
Wk 323/06/2016Person 43400
Wk 323/06/2016Person 43100
Wk 323/06/2016Person 52900
Wk 323/06/2016Person 52600
Wk 323/06/2016Person 62500
Wk 323/06/2016Person 62200
Wk 323/06/2016Person 73300
Wk 323/06/2016Person 73000
Wk 323/06/2016Person 82600
Wk 323/06/2016Person 82300
Wk 323/06/2016Person 93800
Wk 323/06/2016Person 93500
Wk 324/06/2016Person 13900
Wk 324/06/2016Person 103700
Wk 324/06/2016Person 103400
Wk 324/06/2016Person 23900
Wk 324/06/2016Person 23600
Wk 324/06/2016Person 33400
Wk 324/06/2016Person 33100
Wk 324/06/2016Person 54200
Wk 324/06/2016Person 74100
Wk 324/06/2016Person 73800
Wk 324/06/2016Person 83800
Wk 324/06/2016Person 83500
Wk 324/06/2016Person 94200
Wk 324/06/2016Person 93900
Wk 427/06/2016Person 12500
Wk 427/06/2016Person 12200
Wk 427/06/2016Person 102300
Wk 427/06/2016Person 22800
Wk 427/06/2016Person 22500
Wk 427/06/2016Person 31500
Wk 427/06/2016Person 31200
Wk 427/06/2016Person 42600
Wk 427/06/2016Person 42300
Wk 427/06/2016Person 52000
Wk 427/06/2016Person 51700
Wk 427/06/2016Person 62500
Wk 427/06/2016Person 62200
Wk 427/06/2016Person 71700
Wk 427/06/2016Person 71400
Wk 427/06/2016Person 81700
Wk 427/06/2016Person 81400
Wk 427/06/2016Person 92600
Wk 428/06/2016Person 14800
Wk 428/06/2016Person 14500
Wk 428/06/2016Person 104200
Wk 428/06/2016Person 103900
Wk 428/06/2016Person 33500
Wk 428/06/2016Person 44100
Wk 428/06/2016Person 53800
Wk 428/06/2016Person 53500
Wk 428/06/2016Person 63700
Wk 428/06/2016Person 75000
Wk 428/06/2016Person 94100
Wk 428/06/2016Person 93800
Wk 430/06/2016Person 13700
Wk 430/06/2016Person 13400
Wk 430/06/2016Person 22500
Wk 430/06/2016Person 22200
Wk 430/06/2016Person 43500
Wk 430/06/2016Person 43200
Wk 430/06/2016Person 53200
Wk 430/06/2016Person 62400
Wk 430/06/2016Person 73400
Wk 430/06/2016Person 82400
Wk 430/06/2016Person 82100
Wk 401/07/2016Person 26000
Wk 401/07/2016Person 35500
Wk 401/07/2016Person 35200
Wk 401/07/2016Person 46400
Wk 401/07/2016Person 46100
Wk 401/07/2016Person 55500
Wk 401/07/2016Person 55200
Wk 401/07/2016Person 65900
Wk 401/07/2016Person 65600
Wk 401/07/2016Person 76200
Wk 401/07/2016Person 75900
Wk 401/07/2016Person 83300
Wk 401/07/2016Person 83000
Wk 507/07/2016Person 92900
Wk 507/07/2016Person 92600
Wk 611/07/2016Person 12900
Wk 611/07/2016Person 12600
Wk 611/07/2016Person 101000
Wk 611/07/2016Person 10700
Wk 611/07/2016Person 2900
Wk 611/07/2016Person 2600
Wk 611/07/2016Person 3900
Wk 611/07/2016Person 3600
Wk 611/07/2016Person 42600
Wk 611/07/2016Person 42300
Wk 611/07/2016Person 52400
Wk 611/07/2016Person 52100
Wk 611/07/2016Person 61100
Wk 611/07/2016Person 6800
Wk 611/07/2016Person 7900
Wk 611/07/2016Person 7600
Wk 611/07/2016Person 8800
Wk 611/07/2016Person 92800
Wk 611/07/2016Person 92500
Wk 612/07/2016Person 15400
Wk 612/07/2016Person 15100
Wk 612/07/2016Person 104700
Wk 612/07/2016Person 104400
Wk 612/07/2016Person 25100
Wk 612/07/2016Person 34200
Wk 612/07/2016Person 33900
Wk 612/07/2016Person 45900
Wk 612/07/2016Person 45600
Wk 612/07/2016Person 55200
Wk 612/07/2016Person 54900
Wk 612/07/2016Person 64800
Wk 612/07/2016Person 64500
Wk 612/07/2016Person 75300
Wk 612/07/2016Person 75000
Wk 612/07/2016Person 84700
Wk 612/07/2016Person 84400
Wk 612/07/2016Person 96100
Wk 614/07/2016Person 14000
Wk 614/07/2016Person 13700
Wk 614/07/2016Person 102700
Wk 614/07/2016Person 102400
Wk 614/07/2016Person 22700
Wk 614/07/2016Person 22400
Wk 614/07/2016Person 32500
Wk 614/07/2016Person 32200
Wk 614/07/2016Person 43700
Wk 614/07/2016Person 43400
Wk 614/07/2016Person 53600
Wk 614/07/2016Person 53300
Wk 614/07/2016Person 62600
Wk 614/07/2016Person 62300
Wk 614/07/2016Person 73500
Wk 614/07/2016Person 73200
Wk 614/07/2016Person 82600
Wk 614/07/2016Person 82300
Wk 614/07/2016Person 94000
Wk 614/07/2016Person 93700
Wk 615/07/2016Person 13600
Wk 615/07/2016Person 13300
Wk 615/07/2016Person 103800
Wk 615/07/2016Person 103500
Wk 615/07/2016Person 24000
Wk 615/07/2016Person 23700
Wk 615/07/2016Person 33200
Wk 615/07/2016Person 32900
Wk 615/07/2016Person 44400
Wk 615/07/2016Person 53700
Wk 615/07/2016Person 53400
Wk 615/07/2016Person 63600
Wk 615/07/2016Person 73600
Wk 615/07/2016Person 73300
Wk 615/07/2016Person 83500
Wk 615/07/2016Person 94100
Wk 615/07/2016Person 93800

<tbody>
</tbody>
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
Welcome to the board :)

For future reference, we only need a small representative sample of what you are working with, not a big long list of data :)

having said that, here is how I would handle this. I only show a small portion of your sample, so adjust my ranges as needed...
A​
B​
C​
D​
E​
F​
G​
H​
I​
J​
1​
WeekDatePersonSales ValuePerson 1Person 2Person 3Person 4
2​
Wk 4
7/1/2016​
Person 2
6000​
7/9/2016​
0​
0​
0​
0​
3​
Wk 4
7/1/2016​
Person 3
5500​
7/10/2016​
0​
0​
0​
0​
4​
Wk 4
7/1/2016​
Person 3
5200​
7/11/2016​
5500​
1500​
1500​
4900​
5​
Wk 4
7/1/2016​
Person 4
6400​
7/12/2016​
10500​
5100​
8100​
11500​
6​
Wk 4
7/1/2016​
Person 4
6100​
7/13/2016​
0​
0​
0​
0​
7​
Wk 4
7/1/2016​
Person 5
5500​
7/14/2016​
7700​
5100​
4700​
7100​
8​
Wk 4
7/1/2016​
Person 5
5200​
7/15/2016​
6900​
7700​
6100​
4400​

F8=your start date
G2=SUMIFS($D:$D,$B:$B,$F2,$C:$C,G$1)
copied down and across as needed
 
Upvote 0
Thanks for that reply and the advice.

I see and understand how you've done that. However I was hoping to do it with a measure in PowerPivot, and I am looking to get a rolling average so that as data is collected over time I always get the average of the daily totals for the previous seven days. Or in the future the previous seven days according to a date selected on a slicer.

Thanks.

G
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
help required with complex (well, i think so) statement in power query
Replies
3
Views
154
ajm
A
N
  • Question
Calculating hours of working beetween start and end time but when end tim is on other row then start time and on that row has midnight as end time ?
Replies
0
Views
264
nunizgb
N
J
  • Question
Adding stock market data from yahoo finance to master Excel file
Replies
3
Views
790
kcmuppet
K
J
  • Question
Excel Power Query & Power Pivot: Ageing Trend
Replies
1
Views
163
jajatidev
J
G
  • Question
Keep conditional duplicate line
Replies
1
Views
449
johnny51981
J

Forum statistics

Threads
1,215,987
Messages
6,128,122
Members
449,424
Latest member
zephyrunimpressively

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top