Next 3 months forecast

Question

Hi everyone,&nbsp;&nbsp;I need some help with putting the next 3 months forecast into a table. This works perfectly fine in a line chart over time however I think I need to make 3 seperate measures to put them as seperate columns in a table.&nbsp;&nbsp;I have one field for the date e.g "Calander[Date (MMM YY)] and a second field with my forecast data "Sales[Forecast Sales]".&nbsp;&nbsp;I need the next 3 months to be dynamic hence why I can't simply filter the sales by the correct month.&nbsp;&nbsp;ActualsForecast 1 (Next Month)Forecast 2 (Next Month +1)Forecast 3 (Next Month +2)£...£...£...£...&nbsp;Any help would be greatly appreciated!&nbsp;

ryan_mayu · Answer

s_mansell&nbsp;&nbsp;I am not sure what you raw data looks like.&nbsp;so I create three tables.&nbsp;ActualForecastAnd a calendar tablePlease try below measuresactual = 
VAR maxdate=max(ACT[date])
return CALCULATE(sum(ACT[actual]),FILTER(ACT,ACT[date]=maxdate))

FC1 = 
var maxdate=max('FC'[date])
return  CALCULATE(sum(FC[FC]),DATEADD(datetime[Date],1,MONTH))

FC2 = 
var maxdate=max('FC'[date])
return  CALCULATE(sum(FC[FC]),DATEADD(datetime[Date],2,MONTH))

FC3 = 
var maxdate=max('FC'[date])
return  CALCULATE(sum(FC[FC]),DATEADD(datetime[Date],3,MONTH))&nbsp;The result depends on the month you selected. Hope this is helpful.

s_mansell · Answer

Hi&nbsp;ryan_mayu&nbsp;,&nbsp;&nbsp;Unfortunatly I do not have acces to create any tables, all my data is coming from an azure database.&nbsp;&nbsp;I am only able to create measures using the existing data.&nbsp;&nbsp;I am using two tables which do have relationships between the two:&nbsp;1 tables containing the dates1 containing the forecast sales&nbsp;&nbsp;

s_mansell · Answer

Hi&nbsp;ryan_mayu&nbsp;,&nbsp;&nbsp;I have just tried your measures, I need them to be based off today's date.&nbsp;&nbsp;E.G if today is 23/06/2020 then I need the measure to show the forecast value for July, August and September.&nbsp;&nbsp;Apologies if I am not being clear!&nbsp;

ryan_mayu · Answer

s_mansell&nbsp;&nbsp;do you have a calendar table? will you add a date slicer or you just want to show the forcast based on the current date?

s_mansell · Answer

ryan_mayu&nbsp;I won't be adding a slicer, I need the measures to be based off the current date to make to table dynamic and automatically update when opened.&nbsp;

Forum Discussion

Next 3 months forecast

5 Replies

Related content

Forecasting with DAX

Next 3 month forecast by month

Forecast Future Months

Forecasting

Closing Forecasting for Year and Month

Actuals	Forecast 1 (Next Month)	Forecast 2 (Next Month +1)	Forecast 3 (Next Month +2)
£...	£...	£...	£...