Forum Discussion
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])
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()))
4 Replies
- amitchandak
Super User
can you explain with example
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
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()))