Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I need help with a Moving Average. I have read through many of the other messages, but I can't seem to get this one to work. I need to know the moving average of the Count by Steam,Target pairing using the WeekStart to advance the average.
His is a Sample of the data I am working with.
Stream | Target | WeekStart | Count |
Market | afi | 4/10/2016 | 5 |
Market | afi | 5/15/2016 | 415 |
Market | afi | 5/22/2016 | 2255 |
Market | afi | 6/12/2016 | 494 |
Market | afi | 7/10/2016 | 8 |
Market | ami | 4/10/2016 | 5 |
Market | ami | 5/15/2016 | 367 |
Market | ami | 5/22/2016 | 2256 |
Market | ami | 6/12/2016 | 494 |
Market | ami | 7/10/2016 | 8 |
Market | ari | 4/3/2016 | 128 |
Market | ari | 4/10/2016 | 132 |
Market | ari | 4/17/2016 | 114 |
Market | ari | 4/24/2016 | 2085 |
Market | ari | 5/1/2016 | 565 |
Market | ari | 5/8/2016 | 460 |
Market | ari | 5/15/2016 | 31 |
Market | ari | 5/22/2016 | 8 |
Market | ari | 5/29/2016 | 174 |
Market | ari | 6/5/2016 | 175 |
Market | ari | 6/12/2016 | 67 |
Market | ari | 6/19/2016 | 53 |
Market | ari | 6/26/2016 | 8 |
Market | ari | 7/3/2016 | 382 |
Market | ari | 7/10/2016 | 213 |
COMM | afi | 4/10/2016 | 5 |
COMM | afi | 5/15/2016 | 415 |
COMM | afi | 5/22/2016 | 2255 |
COMM | afi | 6/12/2016 | 494 |
COMM | afi | 7/10/2016 | 8 |
COMM | ami | 4/10/2016 | 5 |
COMM | ami | 5/15/2016 | 367 |
COMM | ami | 5/22/2016 | 2256 |
COMM | ami | 6/12/2016 | 494 |
COMM | ami | 7/10/2016 | 8 |
COMM | ari | 4/3/2016 | 128 |
COMM | ari | 4/10/2016 | 132 |
COMM | ari | 4/17/2016 | 114 |
COMM | ari | 4/24/2016 | 2085 |
COMM | ari | 5/1/2016 | 565 |
COMM | ari | 5/8/2016 | 460 |
COMM | ari | 5/15/2016 | 31 |
COMM | ari | 5/22/2016 | 8 |
COMM | ari | 5/29/2016 | 174 |
COMM | ari | 6/5/2016 | 175 |
COMM | ari | 6/12/2016 | 67 |
COMM | ari | 6/19/2016 | 53 |
COMM | ari | 6/26/2016 | 8 |
COMM | ari | 7/3/2016 | 382 |
COMM | ari | 7/10/2016 | 213 |
Solved! Go to Solution.
Try this measure. I've assumed your table is called 'Stream' and this calculates for 3 weeks (the -21 days)
Moving Average = AVERAGEX( DATESINPERIOD( 'Stream'[WeekStart], LASTDATE('Stream'[WeekStart]) , -21 , DAY), CALCULATE(sum('Stream'[Count])) )
That worked perfectly thank you so much!
Hi @EvanWright,
Great to hear the problem got resolved! Could you accept the corresponding reply as solution to help others who may have similar issue easily find the answer and close this thread?
Regards
Hi @EvanWright,
Have you tried the formula provided by Phil_Seamark? It should work in your scenario. If you still have any issue, feel free to post it out here.
Regards
Try this measure. I've assumed your table is called 'Stream' and this calculates for 3 weeks (the -21 days)
Moving Average = AVERAGEX( DATESINPERIOD( 'Stream'[WeekStart], LASTDATE('Stream'[WeekStart]) , -21 , DAY), CALCULATE(sum('Stream'[Count])) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |