Forum Discussion
Rolling average over week-year combination
Hello,
I'd like to calculate a rolling average over a variable that combines the year and the week (e.g., 2020-51). I created a calculated column that ranks year-week, and I can calculate a rolling average over rank - for example, like this:
Rolling 4 Weeks Sales =
VAR _table2 =
CALCULATETABLE (
VALUES ( Summary[Rank] ),
FILTER (
ALLSELECTED ( Summary[Rank] ),
Summary[Rank]
>= MAX ( Summary[Rank] ) - 3
&& Summary[Rank]<= MAX ( Summary[Rank] )
)
)
RETURN
IF(COUNTAX(_table2,[Rank])=4,AVERAGEX ( _table2, CALCULATE ( SUM ( Summary[sum_total_sales] ) ) ))
but then when I go to plot sales vs. year-week, I can't overplot this rolling average because it's calculated over rank, not year-week, so it does not show up in the plot. It does show up if I plot sales vs. rank, of course, but I want the plot to show year-week, so the end user would understand it better.
Could someone please help? Is there any way to either calculate the rolling average over year-week directly, or plot sales vs. rank but label the x-axis with year-week and not rank?
Hi hedgy123 ,
According to your description, I create this data：
Here are the steps you can follow：
1. Create column.
WeekDay Number = WEEKNUM('Table'[date],1) Month & Year = 'Table'[year]&"-"&'Table'[WeekDay Number]Result：
2. Create measure to get the rolling average
Expected Output (Average) = var _1= MAX('Table'[Month & Year]) return AVERAGEX( FILTER( SUMMARIZE( ALLSELECTED('Table'), [Month & Year], "Avg Value",AVERAGE('Table'[amount]) ), [Month & Year]<=_1 ), [Avg Value] )3. Use Line chart
You can downloaded PBIX file from here.
If my answer is not what you need, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- amitchandak
Super User
hedgy123 , Create a separate Week / date table with Week Rank
New column
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)Try measures like
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))
Last 8 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-8 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
last two weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]<=max('Date'[Week Rank])-1Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8 Hi hedgy123 ,
According to your description, I create this data：
Here are the steps you can follow：
1. Create column.
WeekDay Number = WEEKNUM('Table'[date],1) Month & Year = 'Table'[year]&"-"&'Table'[WeekDay Number]Result：
2. Create measure to get the rolling average
Expected Output (Average) = var _1= MAX('Table'[Month & Year]) return AVERAGEX( FILTER( SUMMARIZE( ALLSELECTED('Table'), [Month & Year], "Avg Value",AVERAGE('Table'[amount]) ), [Month & Year]<=_1 ), [Avg Value] )3. Use Line chart
You can downloaded PBIX file from here.
If my answer is not what you need, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
