Forum Discussion
5-Day Moving Average
ripstaur - I like Fowmy 's solution, seems like it should work. Nice and simple. There is a Quick Measure Gallery submission that should work as well.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Rolling-Average/m-p/160720#M3
Could also be done without the time intelligence functions if necessary (in an RLS or Direct Query situation), in theory:
Moving 5 Day Average =
VAR __MaxDay = MAX('Table'[Date])
RETURN
AVERAGEX(FILTER(ALL('Table'),[Date]<=__MaxDay && [Date]>=__MaxDay-5),[Value])
You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000
Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008
But, ultimately, actual sample data would be tremendously helpful to be very specific with a solution.
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
- daxer-almighty6 years ago
Solution Sage
And I don't like Fowmy's solution as it breaks down when we are too close to the left edge of the calendar (Dates).- amitchandak6 years ago
Super User
daxer-almighty , How about taking a sum and divide by days in Fowmy solution.
As mesures
Rolling 5 = Divide(CALCULATE(Sum('Table'[Daily Cases]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-5,Day)) ,CALCULATE(distinctcount('Table'[Date]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-5,Day)))
or
Rolling 5 = CALCULATE(Divide(Sum('Table'[Daily Cases]),distinctcount('Table'[Date])),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-5,Day))
With a date table.
- daxer-almighty6 years ago
Solution Sage
amitchandak
You can't calculate 5D average if you don't have 5 days (too close to the left edge of the calendar), so you're targeting a completely different area that has nothing to do with the problem I'm talking about.