Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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()))
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.
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 |
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()))
That got it! Thank you!
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |