cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

4 week moving average of a measure

I have created a measure that calculates 1st pass yield rate.  I am trying to report a 4 week moving average of this 1st pass yield rate.  I'm fairly new to this.  I've figured out how to report the 1st pass yield % by week, but cannot figure out how to calcuate the 4 week moving average using the measure.  My "1st pass yield" measure is calculated from two other calculated measures (passed / total tested).  

3 REPLIES 3
Super User IV
Super User IV

@alittle1 , Have a separate week /date table and create a rank based on YYYYWW or week start date.

Have these columns in the week/date table

 

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)

 

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

// Use Average or you can divide by distinct weeks
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...

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

OK, so what is 'order'[Qty] referring to? 

Should that be the data I'm trying to capture the rolling average for?  I get an error if I try to plug in my calculated measure:  '_dbMeasures'[AT_New_1stPass%].  This 1stPass% is what I'm trying to get a 4 week rolling average for.

Thank you!  I will give this a try.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.