topic Re: Dynamic 4 week average based on selected week in DAX Commands and Tips

Dynamic 4 week average based on selected week

AdamRobC — Thu, 12 Nov 2020 12:51:09 GMT

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

Re: Dynamic 4 week average based on selected week

Fowmy — Thu, 12 Nov 2020 13:18:23 GMT

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 🙂

YouTube LinkedIn

Re: Dynamic 4 week average based on selected week

amitchandak — Thu, 12 Nov 2020 13:22:03 GMT

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])-1

Power 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

Re: Dynamic 4 week average based on selected week

vivran22 — Thu, 12 Nov 2020 13:22:22 GMT

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 _Average

Cheers!
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 Twitter

Re: Dynamic 4 week average based on selected week

AdamRobC — Thu, 12 Nov 2020 13:49:55 GMT

Fowmy Thanks, but relative date is calculated based on the current week, I need the measure to be dynamic based on a user-selected week, so using Week Index instead.

Re: Dynamic 4 week average based on selected week

AdamRobC — Thu, 12 Nov 2020 14:02:17 GMT

amitchandak 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

Re: Dynamic 4 week average based on selected week

amitchandak — Thu, 12 Nov 2020 14:01:35 GMT

AdamRobC , 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])))