Forum Discussion
Dynamic 4 week average based on selected week
Hi,
Please refer to the screenshot below. I have a static Run Rate DAX measure based on RelativeWeeks between -2 and -5 (where current week = 0), but I would like a dynamic Run Rate measure based on a Week Index. When a Fiscal Week is selected, it looks back at the previous 4 weeks and calculates the average across that period.
E.g. Run Rate Visits where Week Index = 135 would be the sum of visits between Week Indexes 131 and 134)
= SUM(1679030+1536445+1499992+1341368) / 4 = 1,514,209 visits
Thanks,
Adam
AdamRobC , Assuming week index is same as week rank column
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)// Always incremental
you can try measures like
Last 4 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-4 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
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 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/1051123amitchandak Thanks, I adapted your DAX to my data model and with a few edits got it working:
Run Rate Dynamic = CALCULATE([Visits],FILTER(ALL('Calendar'),'Calendar'[Week Index]>=MAX('Calendar'[Week Index])-4 && 'Calendar'[Week Index]<=MAX('Calendar'[Week Index])-1))/4
6 Replies
AdamRobC
Please try the following measure:Run Rate Dynamic = CALCULATE( AVERAGE(Table[Visits]), FILTER( ALL(Table), Table[RelativeWeek] < Max(Table[RelativeWeek]) && Table[RelativeWeek] >= Max(Table[RelativeWeek]) - 4 ) )________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
AdamRobC , Assuming week index is same as week rank column
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)// Always incremental
you can try measures like
Last 4 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-4 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
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 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/1051123amitchandak Thanks, I adapted your DAX to my data model and with a few edits got it working:
Run Rate Dynamic = CALCULATE([Visits],FILTER(ALL('Calendar'),'Calendar'[Week Index]>=MAX('Calendar'[Week Index])-4 && 'Calendar'[Week Index]<=MAX('Calendar'[Week Index])-1))/4
Hello AdamRobC
You may try the following measure:
Last 4 weeks Avg = VAR _CurrentWeek = MAX(dtTable[Week Index]) VAR _Filter = FILTER( ALL(dtTable[Week Index]), dtTable[Week Index] >= _CurrentWeek-4 && dtTable[Week Index] <= _CurrentWeek-1 ) VAR _Average = CALCULATE(AVERAGE(dtTable[Value]),_Filter) RETURN _AverageCheers!
Vivek
If it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)
Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on TwitterAdamRobC , missed placed parentheses
Run Rate Dynamic = CALCULATE(SUM([Visits]),FILTER(ALL('Calendar'),'Calendar'[Week Index]>=MAX('Calendar'[Week Index])-4 && 'Calendar'[Week Index]<=MAX('Calendar'[Week Index])))
