Rolling Max Price Calculations

[bcharlto]

I'm trying to apply the rolling sum calculations (http://www.powerpivotpro.com/2013/07/moving-averages-sums-etc/) to price, but having difficulty. I'm trying to build a "RegularPrice" field that is based on the max average price for a product sold at store level over the course of 12 weeks (84 days). I have tried the printscreen attached, but you can see, as average price goes down, so does the regular price... Can anyone help me master this one?

[bcharlto · Accepted solution]

Maybe this was just a glitch as I'm working with over 2 million rows of data... It looks to be working now. Cheers

[bcharlto]

The secondary point here is that I want this to be a table I can reference to bring the "RegularPrice" field into my main query. This could be easier with a seperate SQL query, so let me know if you have a better SQL formula that I should use to calculate RegularPrice as defined above... Kind regards,