Forum Discussion
Dynamic 4 week average based on selected week
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
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 š