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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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

 

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

@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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

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