Forum Discussion

Marcus_S's avatar
Marcus_S
Frequent Visitor
5 years ago
Solved

10-week-moving average

Hello!

I´ve a Measur for a moving average

 

Moving_Average_10Week =

 

var theM = MAX('Date'[Week])

 

var theTable = SUMMARIZE(ALL('Date'[Week]), 'Date'[Week], "Valuee", SUMX('Date', [Weekly Amount]))

 

return

 

AVERAGEX (FILTER(theTable, 'Date'[Week] < theM && 'Date'[Week] >= theM-10), [Valuee])
 
My problem ist, that the dataset begins at week 14, but the average at week 15.
Thanks for help!
 
 
 
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    Marcus_S , In such case it always better to use rank on week year or week start date

    new columns

    Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
    Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
    Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)

     

    measure

    Last 15 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-15 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
    This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
    Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
    Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Marcus_S , In such case it always better to use rank on week year or week start date

    new columns

    Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
    Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
    Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)

     

    measure

    Last 15 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-15 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
    This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
    Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
    Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    HI Marcus_S,

    Since we not so sure how you data table structure, can you please share some dummy data to help us clarify the data structure and test coding formula?

    How to Get Your Question Answered Quickly 

    BTW, you can also try to calculate with the accurate date of ranges(as amitchandak said) instead of direct filter on week numbers.

    Regards,

    Xiaoxin Sheng