Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jnrezk
Helper III
Helper III

4 week rolling average

Hi, I need to be able to create a 4 week rolling average off of calendar weeks from Monday start to Sunday end.

 

So if I opened the report on a Wed I need to make sure the calculation cuts off on Sunday before.

 

This is an example of what I would like to acheive - the resulting average line. It would be great to make the whole calculation in one measure so I don't have to make 4 and average the 4 after - would be less measures to make:

 

 

Week StartWeek EndSales
27/12/212/1/2022100
3/1/20229/1/2022121
10/1/202216/1/2022123
17/1/202223/1/2022122
AVERAGE 116.5
8 REPLIES 8
v-yangliu-msft
Community Support
Community Support

Hi  @jnrezk ,

I created some data:

vyangliumsft_0-1643353545024.png

Here are the steps you can follow:

1. Create calculated column.

 

week = WEEKNUM('Table'[date],2)
weekday = WEEKDAY('Table'[date],2)
week_column =
var _lastweek=CALCULATE(MAX('Table'[week]),FILTER(ALL('Table'),YEAR('Table'[date])=YEAR(EARLIER('Table'[date]))))
return
IF([week]=_lastweek&&WEEKDAY(DATE(YEAR([date]),12,31),2)<>7,1,[week])

 

vyangliumsft_1-1643353545028.png

2. Create measure.

 

avg = CALCULATE(AVERAGE('Table'[value]),FILTER(ALL('Table'),'Table'[week_column]=MAX('Table'[week_column])&&YEAR('Table'[date])=YEAR(MAX('Table'[date]))))
min =
var _todayweek=WEEKNUM(TODAY(),1)
return
CALCULATE(MIN('Table'[date]),FILTER(ALL('Table'),'Table'[week_column]=MAX('Table'[week_column])&&'Table'[week_column]>=_todayweek-4&&'Table'[week_column]<=_todayweek))
Flag =
IF(
    MAX('Table'[date])=[min],1,0)

 

3. Place [Flag]in Filters, set is=1, apply filter.

vyangliumsft_2-1643353545030.png

4. Result:

vyangliumsft_3-1643353545030.png

 

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

@v-yangliu-msft  this seems to be a average daily but I am looking for total weeks

amitchandak
Super User
Super User

@jnrezk , Create a date table or week table with rank on week start date

 

new columns
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)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

 

Then try measures like

Last 4 weeks = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-5 && '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...
https://www.youtube.com/watch?v=pnAesWxYgJ8

 

@amitchandak Thanks for this. I have created a few new columns to calendar including Week Year and Last 4 Weeks as you said - the numbers don't seem to be correct ... i attached an example and hoped you could please alter it or let me know what I did wrong? Thanks!!https://drive.google.com/file/d/1pFO1IL-Pt9UWqlvd5j2qmLuNW4Q-Vzlz/view?usp=sharing


@jnrezk , You need to create measures for rolling

 

Avg = CALCULATE(AverageX(values('Date'[Week Rank]), calculate(sum('Table'[Qty])) ), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-5 && 'Date'[Week Rank]<=max('Date'[Week Rank]) -1 ))

 

a new column will be like

CALCULATE(sum('Table'[Qty]), FILTER(('Table'),'Table'[Week Rank]>=earlier('Table'[Week Rank])-5 && 'Table'[Week Rank]<=earlier('Table'[Week Rank]) -1 ))

 

 

@amitchandak This is what I already did - can you check my file I linked and let me know what adjustments I should make?

@jnrezk , Please provide access

Hi, @amitchandak I gave access - do you have it now?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.