Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Solved! Go to 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.
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.
@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.
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.
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.
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.
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.
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
96 | |
90 | |
79 | |
67 |
User | Count |
---|---|
153 | |
125 | |
114 | |
111 | |
95 |