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 Am...
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)))

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