How to caclulate Rolling 12 month and rolling 3 month average.

[DeepDive]

I have sales data (where day wise sales is there)linked to DimDate table, My requirement is to get 12 months and 3 months rolling average against respective date and also how to calculate for average for initial dates where 12 months and 3 months funda is not applicable.

I need to prepare both graph and Matrix.

For Ex (Calculation) :

Rolling Avg 12 Months of 7/1/2019 = Average of month Sales from ( 8/1/2018 to 7/1/2019)

Rolling Avg 3 Months of 7/1/2019 = Average of month Sales from ( 5/1/2019 to 7/1/2019)

Am using below formula for 3 months rolling avg -
"Rolling 3 Months =
CALCULATE([TotalSales], DATESINPERIOD('Date'[Date],LASTDATE('Date'[Date]),-3,MONTH))"

Experts Kindly help !!

[v-xuding-msft · Accepted solution]

Hi @DeepDive ,

I created a sample you can have a try.

• create a calendar table

Table 2 = CALENDARAUTO()

• create measures

3M = 
DIVIDE(CALCULATE(  
    SUM('Table'[Sum of Month Sales]) , 
   DATESINPERIOD( 'Table 2'[Date], MAX('Table 2'[Date]), -3, MONTH ) ),CALCULATE(  
    DISTINCTCOUNT('Table'[Date]) , 
   DATESINPERIOD( 'Table 2'[Date], MAX('Table 2'[Date]), -3, MONTH ) ))

12M = IF(MAX('Table'[Sum of Month Sales]) = BLANK(),BLANK(),
DIVIDE(CALCULATE(  
    SUM('Table'[Sum of Month Sales]) , 
   DATESINPERIOD( 'Table 2'[Date], MAX('Table 2'[Date]), -12, MONTH ) ),CALCULATE(  
    DISTINCTCOUNT('Table'[Date]) , 
   DATESINPERIOD( 'Table 2'[Date], MAX('Table 2'[Date]), -12, MONTH ) )))

Best Regards,

Xue

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.