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
Anonymous
Not applicable

6 Week average Measure

Hello, I am trying to get the 6-week average but need to remove the current date, looking at the past 6 Wednesdays and that would change as the week progresses. What is the best way to remove the current date? 

 

AVG = AVERAGEX(values(Dates[Date].[Date]),[Total])
1 ACCEPTED SOLUTION

Check if these can help

AVG Total Loads = AVERAGEX(filter(values(Dates[Date].[Date]),Dates[Date]<Today()),[Total Loads])

AVG Total Loads = calculate(AVERAGEX(values(Dates[Date].[Date])),[Total Loads],filter(all(Dates),Dates[Date]<Today()))

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

can you explain with example

Anonymous
Not applicable

sure sorry about that. I have 6 weeks of data and it also includes data from today. I don't want to show today's information in the average. So right now this formula captures the average of all the weeks including today. 

 

AVG Total Loads = AVERAGEX(values(Dates[Date].[Date]),[Total Loads])

 

So with that formula, it gives me 9991.86 when it should be 9453.5 over the past 6 weeks. 

 

DatesTotal 
25-Dec508
1-Jan1842
8-Jan13483
15-Jan13699
22-Jan13046
29-Jan14143
5-Feb13222

 

 

Check if these can help

AVG Total Loads = AVERAGEX(filter(values(Dates[Date].[Date]),Dates[Date]<Today()),[Total Loads])

AVG Total Loads = calculate(AVERAGEX(values(Dates[Date].[Date])),[Total Loads],filter(all(Dates),Dates[Date]<Today()))
Anonymous
Not applicable

That got it! Thank you!

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.