6 Week average Measure

[Anonymous]

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

[amitchandak]

can you explain with example

[Anonymous]

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. 

Dates	Total
25-Dec	508
1-Jan	1842
8-Jan	13483
15-Jan	13699
22-Jan	13046
29-Jan	14143
5-Feb	13222

[amitchandak · 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()))

[Anonymous]

That got it! Thank you!