Forum Discussion

ar-data's avatar
ar-data
Helper II
4 years ago
Solved

4 weeks moving average for Month-Week combination?

Hi experts,   I have calculated 4 weeks moving average based on week no. But I want show the averages against Month-Week combination. But some weeks spill into 2 months (W27 & W36 as shown below) a...
Need Help
Tips and Tricks
  • ar-data's avatar
    ar-data
    4 years ago

    Hi,

    Thanks for the response. Actually the table I'm working on is created with Dax (summarize), so I cannot do anything on it in Power Query.

    Also, as per the solution you had posted, I'm not sure if that will add month variable along with 4 weeks variable to calculate moving average.

  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    Hi, ar-data 

    If I understand correctly, you need to average 4 consecutive items in the table according to the order in the table above. If there is an index column here, then calculate the average of 4 consecutive index items.
    But since you can't create an index column using PowerQuery, then we can create a ranking column based on the date.

    Remove the comment and create a ranking column with your date column.

    Rank = 
RANKX('Table',[Index],,ASC,Dense)
// RANKX('Table',[yourDateColumn],,ASC,Dense)

    Then the Avg measure:

    AVG = 
var _t=    
FILTER(
        ALL('Table'),
        'Table'[Rank]<=MAX('Table'[Rank])&&
        'Table'[Rank]>MAX('Table'[Rank])-4
        )
var _avg=IF(COUNTROWS(_t)=4,AVERAGEX(_t,[Score]))

return _avg


    Result:

    Please refer to the attachment below for details.

    Hope this helps.

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng

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

lance_6's avatar
lance_6
Helper II
4 years ago

I would do something like this in the PQ Editor:

#"Inserted Text After Delimiter" = Table.AddColumn(Source, "WeekNo", each Text.AfterDelimiter([Column1], "-W"), type text),
#"Removed Duplicates" = Table.Distinct(#"Inserted Text After Delimiter", {"WeekNo"}),
#"Inserted Text Before Delimiter" = Table.AddColumn(#"Removed Duplicates", "Text Before Delimiter", each Text.BeforeDelimiter([Column1], "-W"), type text)

So you create a custome column based on the week. Remove the duplicate weeks (based on your data it looks like it defaults the sort to week month, you may want to make sure it does or it won't remove the correct weeks).

 

You can then create a measure to get your average pretty easily based on the month. 

ar-data's avatar
ar-data
Helper II
4 years ago

Hi,

Thanks for the response. Actually the table I'm working on is created with Dax (summarize), so I cannot do anything on it in Power Query.

Also, as per the solution you had posted, I'm not sure if that will add month variable along with 4 weeks variable to calculate moving average.