4 weeks moving average for Month-Week combination?

[ar-data]

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) and so we will get 5 data points based on week no filter. 

I only want the filter to select 4 data points based Month-week combination and then calculate the moving average for these 4 data points. How can we bring in the month into filter? 

Also, Fiscal year here is Apr-Mar. So Jan week 1 should ideally consider the past 3 weeks of Dec as they all fall in same fiscal year. 

Thanks a lot.

[ar-data · Accepted solution]

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.

[ar-data]

@v-angzheng-msft Hi, thanks for the response. Here are the details:

1. Here is the sample Data (https://docs.google.com/spreadsheets/d/1g1eGPfs8Q878nTPxZPIIk_GQmLIl0BOH/edit?usp=sharing&ouid=10966...). Here are few rows from sample data.

Available Data
Financial Year	Month	Week	Score
2021-22	6	W26	26
2021-22	6	W27	22
2021-22	7	W27	15
2021-22	7	W28	30
2021-22	7	W29	29
2021-22	7	W30	16
2021-22	7	W31	14
2021-22	8	W32	20
2021-22	8	W33	30
2021-22	8	W34	19
2021-22	8	W35	15
2021-22	8	W36	28
2021-22	9	W36	27
2021-22	9	W37	23
2021-22	9	W38	27
2021-22	9	W39	24
2021-22	9	W40	19
2021-22	10	W40	15
2021-22	10	W41	30
2021-22	10	W42	15

2. The source data has so many columns and I use various lookup tables and dax calculations to arrive at the scores. Then I use summarize function to get at the above table where scores are arranged FY-Month-Week wise. Form the above table, the expected output is as follows:

Expected Result
Month-Week	4 weeks moving average
6-W26
6-W27
7-W27
7-W28	23.25
7-W29	24
7-W30	22.5
7-W31	22.25
8-W32	19.75
8-W33	20
8-W34	20.75
8-W35	21
8-W36	23
9-W36	22.25
9-W37	23.25
9-W38	26.25
9-W39	25.25
9-W40	23.25
10-W40	21.25
10-W41	22

3. I concat the month and week, and then I have to calculate the 4 weeks moving average of the scores. The axis of the graph would be Month-Week and values would be 4 weeks moving average.

If I am using just the week numbers in the graph, then calculating 4 weeks avg is simple. But, we have to consider month also in the avg calculation. There are some week numbers that appear in 2 different months.

Week 36 ad 40 appear in 2 different months. So if I calculate avg based on week number and wherever the week number appears in 2 different months, the avg will be calculated using 5 data points due to month-week schema. How can I avoid this and just consider 4 data points with month-week schema.

Thank you.

[v-angzheng-msft · Accepted solution]

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. 

[ar-data]

Hi @v-angzheng-msft ,

Thanks for the response. If I add date column and use it for rank, for a week number there will be more than 1 date and so there will be more ranks for a week.

After some thought, I've added week start column and then assigned ranking based on week start date. This way every week number will only have 1 date and hence 1 week. Thanks for the guidance.

[v-angzheng-msft]

Hi, @ar-data 

Could you please consider sharing more details about it and posting expected result so it is clear on what needs to be implemented?  And It would be great if there is a sample file without any sesentive information here.

It makes it easier to give you a solution.

1. Sample (dummy dataset) data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

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]

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 · Accepted solution]

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.