[dax] how can i use moving average for forecasting

[KanisleS]

hello all,

i am using the moving average method for forecasting.

i want to forecast the number of sales of say the 20th week of 2022 based on the 8 past weeks ( week12,week13,...,week19)

my try, i first compute the moving sum with a lag of 7 days : 

2 month moving sum = 
CALCULATE(sum(Sales[NbrSales]),
          DATESINPERIOD(Calendar[Date],
                        LASTDATE('Calendar'[Date])-7,-2, MONTH
                       )
         )

then i created another measure where i divided the moving sum by 8 to have the moving  average

But it doesn't seem to be working as intended when I look at the 53th or 1st week of 2022 ... 

could anyone help me and tell me where i made a mistake? how could i improve this model overall ?

Thanks for helping !

[SpartaBI]

@KanisleS are you using a fiscal calendar or a gregorian (standard) calendar?
Also, in general, time intelligence calculations will not work on a week base periods.
If you want to create something based on 8 weeks this is not the same as 2 months.
I suggest 1st thing go read the dax patterns for time based calculations (4 topics):
https://www.daxpatterns.com/time-patterns/

One last general sugguetion: don't use LASTDATE('Caledar'[Date]) to calculate scalar date result, instead use MAX('Caledar'[Date]). LASTDATE should only be used as a calculate table filter.

Showcase Report – Contoso By SpartaBI

     

[amitchandak]

@KanisleS , If you need by Week

else change to month

2 month moving sum =
CALCULATE(AverageX(Values(Calendar[Week] ), calculate( sum(Sales[NbrSales]))),
DATESINPERIOD(Calendar[Date],
LASTDATE('Calendar'[Date])-7,-49, DAY
)
)

or

2 month moving sum =
CALCULATE(AverageX(Values(Calendar[Week] ), calculate( sum(Sales[NbrSales]))),
DATESINPERIOD(Calendar[Date],
LASTDATE('Calendar'[Date]),-49, DAY
)
)

You can also consider week rank columns

Week Rank = RANKX(('Calendar'),'Calendar'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(('Calendar'),'Calendar'[Year Week],,ASC,Dense) //YYYYWW format
measures

Last 8 weeks = CALCULATE(AverageX(Values(Calendar[Week] ), calculate( sum(Sales[NbrSales]))), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Calendar'[Week Rank])-8 && 'Calendar'[Week Rank]<=max('Calendar'[Week Rank])))

[KanisleS]

Why -49 and not -56?