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/
@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?
readyOH Try looking at the following video. It might help provide some context to the solution that Greg_Deckler Provided .
thank you guys
The first issue : the value
yep linear regression would be great (especially for seasonality), but I need something more "basic" and easy to do.
Average fits my need. That's what we already use in excel.
So we generate an average monthly sales of previous months and this value will be the same for the next months -for each months (I know that is not perfect way to forecast).
so the average sales will be? :
CALCULATE(AVERAGE('Table1'[Sales]);FILTER(ALL('Table1'[Month]);'Table1'[Month]<= MAX('Table1'[Month]))
The problem is that Filter in the formula is cumulative http://community.powerbi.com/t5/Desktop/DAX-Filter-issue/m-p/9368
and
CALCULATE(AVERAGE('Table1'[Sales]);'Table1'[Month]<= MAX('Table1'[Month])) generates an error. Using MAX is not possible in this formula.
The second issue : is the way I will show the value average montly sales for the next monts in a chart
The average is a measure.
And I do not have in my table set : october november, december
So how can i make a charts like this one.
Sorry if it is a stupid question, but for my previous sales, of course i have a row for each day of each months, but for next months i have nothing. In the video, it is easy for the who uses excel, he can add the rows of the next year and he has the value thanks to his formula.
Do you have a solution?
