Forum Discussion
How to do a Forecast?
Try taking a look at the following links. Hope this helps.
Issue One:http://www.daxpatterns.com/cumulative-total/
Issue Two: http://blog.gbrueckl.at/2015/04/recursive-calculations-powerpivot-dax/
thank you Greg_Deckler I am not familiar with linear regression. I have have this sample data
| Month | Sales |
| January | 375,09 |
| February | 375,01 |
| March | 375 |
| April | 374,95 |
| May | 374,93 |
| June | 374,92 |
| July | 374,88 |
| August | 374,8 |
| September | 374,71 |
| October | ? |
| November | ? |
| December | ? |
What I want to do : a formula which calculates automatically the sales for the next three months, based on an average of the previous month. if we are in september, it generates a trend for oct/nov/dec based on the average. If we are in december, then jan/feb/march.
Can you help me?
Assuming that "," are "." in my geographic context, I plugged your numbers into my model and came up with:
Data:
X,Y
1,375.09
2,375.01
3,375.00
4,374.95
5,374.93
6,374.92
7,374.88
8,374.80
9,374.71
Estimates (from linear regression)
10,374.72
11,374.68
12,374.64
That's linear regression, if you want a rolling average, then you could use the pattern here:
http://www.daxpatterns.com/statistical-patterns/
For rolling average. However, I cannot tell from your information how many months that you want to include in your rolling average, the last 3 months? So September would take into account July, August and September to predict December, June, July and August to predict November and May, June, July to predict October, or do you want all available dates to be used in prediction such that [Start] through July to predict October, [Start] through August to predict November and [Start] through September to predict December? Or something else all together??
@smoupre wrote:For rolling average. However, I cannot tell from your information how many months that you want to include in your rolling average, the last 3 months? So September would take into account July, August and September to predict December, June, July and August to predict November and May, June, July to predict October, or do you want all available dates to be used in prediction such that [Start] through July to predict October, [Start] through August to predict November and [Start] through September to predict December? Or something else all together??
thank you.
Not based on the previous 3 months but just the previous months. in dax formula, there is "filter"
FILTER(ALL('Table1'[Month]);'Table1'[Month]<= MAX('Table1'[Month])) is it what I have to use?
So, if all you want is an average and it doesn't need to be rolling, just create a measure like:
Average = AVERAGE([Sales])
Put it on your canvas and you have your estimate. Am I missing something?
An average could be great but :
let say we are in october, i already have the sales for january to september. of course i have no data for next month october, november, december
but i want to make a chart or table with the sales of the year 2015 (Y) and month (X) january to december.
How i can make it because i have no row data for october, november, december?