Dynamic 4 week average based on selected week

[AdamRobC]

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

[amitchandak · Accepted solution]

@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-La...

[amitchandak]

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

[vivran22]

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

[amitchandak · Accepted solution]

@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-La...

[AdamRobC · Accepted solution]

@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

[Fowmy]

@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

[AdamRobC]

@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.