cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper II

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

2 ACCEPTED SOLUTIONS
Helper II

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.

Community Support

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.

6 REPLIES 6
Helper II

@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.

Community Support

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.

Helper II

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.

Community Support

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.

Helper II

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.

Helper II

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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors