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/
What you want to do sounds very much like simple linear regression. You should be able to do that in a DAX measure or combination of measures/columns. Can you give me some sample data?
Here is Linear Regression
https://www.easycalculation.com/statistics/learn-regression.php
Here is a DAX pattern for moving average that you might find useful
http://www.daxpatterns.com/statistical-patterns/
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.71Estimates (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?
