First time poster here - thank you for all the people that take the time to respond!
I am trying to execute (what should be) a very simple average formula but I can't seem to get the right output. I need to have a rolling 4 week average calculating for each week and each store in my table. My table looks like this:
The table name can be 'WeeklySales' in this example. [WeekUID] is a unique week field that I am using starting Jan 2019 and it increases by 1 every week following Jan 2019 until infinity so that I can run rolling calculations without worrying about week numbers resettting in the new year.
Simply put, I would like every week for every store to show a 4 week rolling average. As an example, week 199 would be an average of all sales from week 196 to week 199. Week 174 would be an average of all sales from weeks 171 to 174.
I have tried the following DAX formula but it is not working and I'm sure that there is an obvious reason why:
Sales 4wravg =
var salesavg = average('WeeklySales'[sales])
var weeksstart = max('WeeklySales'[WeekUID])
var weeksend = weeksstart - 3
Calculate(salesavg,weekstart >= weeksend, weeksstart <= weekstart)
The logic is that if I am calucalting for week 199 in the table, this formula would read Calculate(Saleavg where the row's week >= 196 and is <= to week 199)
This doesn't work when I drop it into a table but I would like to be able to see it as:
I'm certain that this has to do with the second filter in the formula as well as the need for "KEEPFILTERS" or "ALLSELECTED" please help 🙂