Forum Discussion
Rolling Average for Future Months
- Anonymous9 years ago
Firstly, here is a method of calculating an X Month average. I've assumed you have a selection table and made a measure called "YourSelection" that handles how many months you are considering for this average. I'll explain the selection table next.
YourMeasure X Mth Avg = CALCULATE( if( countrows(values('Dim - Date Table'[YearMonth])) = 1, [YourMeasure], AVERAGEX( values('Dim - Date Table'[YearMonth]), [YourMeasure] ) ), DATESINPERIOD( 'Dim - Date Table'[Date], LASTDATE('Dim - Date Table'[Date]), [YourSelection], MONTH ) )
Now the selection table could be anything, but the purpose is create a slicer that links to this table and allows the user to pick. This could be a list of numbers from 1 to 12, or a list of words like "Last Quarter", "Last Month" with corresponding numbers.
Then this selection is transated using:YourSelection = MIN('Selection Table'[NoofMonths])
So now you have an X Monthly average. Do something likeNextMonthPredict = ([YourMeasure X Mth Avg] / [Targeted]) * [NextMonthTarget]
Here i assume you have a measure that knows what you targeted each/current month as and that you can calculate next months target.
You will need a measure that will calculate a target for each month (we'll call it [Future Target]. Then you need a measure that is: Your Avg / [Target] * [Future Target]
Thanks. Not much help.
I'm attempting to create a future table by yearMonth based on the YTD average to target average,
J
- Anonymous9 years agoNot applicable
Why would this need to be a future table?
The approach i've mentioned above could be tweaked as a calculated column to achieve something similar, however on the face of it, it would seem to be an inefficient route to take. You would also hamper your ability to be more dynamic with your filtering on the fly.
- jdugas9 years ago
Advocate I
Thanks.
I'll review, thoroughly what you posted and if I have any other questions I'll repost.
J