Forum Discussion
Rolling Average for Future Months
Does anyone have suggestions on how to average the YTD months to a target, and reforecast the remaining months based on that percentage?
For example,
If my first 5 month average sales was $40.00 but I targeted $50, I would take 40/50 and multiple that by next months target, say it was $48. The value would come out to 4/5*48 or 38.4. How would I forecast the rest of the months for the year. Would I need to create a calculated table?
Any guidance is much appreciated.
Thanks
Jeremy
- 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.
6 Replies
- AnonymousNot applicable
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.
- jdugas
Advocate I
Sorry. WAY too much info for me to swallow. I'm no DAX expert.
I can calculate YTD average, but that's about it. How to I show a forecasted line based on last percentage to target?
- AnonymousNot applicable
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]