Forum Discussion

Cosmo01's avatar
Cosmo01
Frequent Visitor
4 years ago
Solved

Calculate 26 weeks moving average

Hello folks,

 

i need to make a 26week rolling average measure. i need to show this on a line chart along with weekly sales.

 

i tried with a measure like this, but i'm only getting the week's actual sales and not the average

AVG 26 week =
var _max1 = Max('Date'[Date])
var _min1 = _max1-182
return
CALCULATE(AverageX(Date, Calculate(Sum(Sales) ) ) , FILTER('Date','Date'[Date] >=_min1 && 'Date'[Date] <= _max1)).
 
also tried using dateadd, datesinperiod function but was unsuccessful.
 
Following are snippets of sales and date tables. i've defined the weeks.
 

Sales Table

 

Date Table

 

thanks! 

amitchandak tamerj1 truptis 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    It is quite hard to write a measure without seeing the pbix file, but please try the below.

     

    AVG 26 week =
VAR _max1 =
    MAX ( 'Date'[Date] )
VAR _min1 = _max1 - 182
RETURN
    AVERAGEX (
        ADDCOLUMNS (
            SUMMARIZE (
                FILTER ( ALL ( 'Date' ), 'Date'[Date] >= _min1 && 'Date'[Date] <= _max1 ),
                'Date'[Date_WK_Ending]
            ),
            "@weeklysales", [YourSalesMeasure]
        ),
        [@weeklysales]
    )

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    It is quite hard to write a measure without seeing the pbix file, but please try the below.

     

    AVG 26 week =
VAR _max1 =
    MAX ( 'Date'[Date] )
VAR _min1 = _max1 - 182
RETURN
    AVERAGEX (
        ADDCOLUMNS (
            SUMMARIZE (
                FILTER ( ALL ( 'Date' ), 'Date'[Date] >= _min1 && 'Date'[Date] <= _max1 ),
                'Date'[Date_WK_Ending]
            ),
            "@weeklysales", [YourSalesMeasure]
        ),
        [@weeklysales]
    )