Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
ar-data
Helper II
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. 

Screenshot 2022-02-09 224908.jpg

 

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

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.

View solution in original post

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:

vangzhengmsft_0-1644834305307.png

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. 

View solution in original post

6 REPLIES 6
ar-data
Helper II
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 YearMonthWeekScore
2021-226W2626
2021-226W2722
2021-227W2715
2021-227W2830
2021-227W2929
2021-227W3016
2021-227W3114
2021-228W3220
2021-228W3330
2021-228W3419
2021-228W3515
2021-228W3628
2021-229W3627
2021-229W3723
2021-229W3827
2021-229W3924
2021-229W4019
2021-2210W4015
2021-2210W4130
2021-2210W4215

 

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-Week4 weeks moving average
6-W26 
6-W27 
7-W27 
7-W2823.25
7-W2924
7-W3022.5
7-W3122.25
8-W3219.75
8-W3320
8-W3420.75
8-W3521
8-W3623
9-W3622.25
9-W3723.25
9-W3826.25
9-W3925.25
9-W4023.25
10-W4021.25
10-W4122

 

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.

ardata_0-1644818593224.png

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.

 

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:

vangzhengmsft_0-1644834305307.png

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. 

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
Community Support
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.

lance_6
Helper II
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. 

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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