How do I create a 12 week moving average in Power BI that accounts for stores open less than 12 weeks?

Question

I work for a retailer and made a rolling 12 week sales average the brute force way. More specifically, I made 12 measures to calculate the average for my 12 weeks individually. Then a 13th measure to average my 12 weeks. That said, if a store has been open less than 12 weeks the average is lower than it should be.

How do you think I should work around this? My formulas are below.

Weekly Average

RollingAvg2 = calculate(sum('Transaction_Data'[TotalSales]),filter(all('Dates'),Dates[Week Rank]=MAX('Dates'[Week Rank])-1))

Aggregate Average

Rolling 12 Weeks = ('Rolling Measures'[RollingAvg1]+'Rolling Measures'[RollingAvg2]+'Rolling Measures'[RollingAvg3]+'Rolling Measures'[RollingAvg4]+'Rolling Measures'[RollingAvg5]+'Rolling Measures'[RollingAvg6]+'Rolling Measures'[RollingAvg7]+'Rolling Measures'[RollingAvg8]+'Rolling Measures'[RollingAvg9]+'Rolling Measures'[RollingAvg10]+'Rolling Measures'[RollingAvg11]+'Rolling Measures'[RollingAvg12])/12

score 0 · Answer 1 · answered Apr 13 '22 at 08:53

Problem is that you are using a fixed number to divide.

Here my sample data:

Output:

    3MonthAVG = 
var selectedStore = SELECTEDVALUE(Sheet1[StoreID])
var selectedWEEK = SELECTEDVALUE(Sheet1[WeekID])

return
CALCULATE( AVERAGE(Sheet1[Sales]), FILTER(ALL(Sheet1), Sheet1[StoreID] = selectedStore && Sheet1[WeekID] <= selectedWEEK && Sheet1[WeekID] >= selectedWEEK-2))


3MonthWeeklyAVG = 
var selectedStore = SELECTEDVALUE(Sheet1[StoreID])
var selectedWEEK = SELECTEDVALUE(Sheet1[WeekID])
var WeekInScoope = CALCULATE( COUNTROWS(VALUES(Sheet1[WeekID])), FILTER(ALL(Sheet1), Sheet1[StoreID] = selectedStore && Sheet1[WeekID] <= selectedWEEK && Sheet1[WeekID] >= selectedWEEK-2))
var SumInScoop = CALCULATE(  sum(Sheet1[Sales]) , FILTER(ALL(Sheet1), Sheet1[StoreID] = selectedStore && Sheet1[WeekID] <= selectedWEEK && Sheet1[WeekID] >= selectedWEEK-2))
return
DIVIDE(SumInScoop, WeekInScoope)

3MonthWeeklyAVG here we verify how many weeks we have in our scope.

How do I create a 12 week moving average in Power BI that accounts for stores open less than 12 weeks?

1 Answers1