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
alittle1
Frequent Visitor

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
amitchandak
Super User
Super User

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

 

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

Top Solution Authors