Forecast Future Months

[DataAnalyzer]

I'm trying to work out how to calculate the following and I can't figure it out.

I have a measure that calculates past months Sales.

I want to write a measure or calculated table that does the following:

April 2022 Sales = Past months sales * 1.1 = $110,000

May 2022 Sales = April 2022 Sales * 1.1 = $121,000

June 2022 Sales = May 2022 Sales * 1.1 = $133,100

Month Year	Sales	Type
March 2022	100000	Actual
April 2022	110000	Forecast
May 2022	121000	Forecast
June 2022	133100	Forecast

For all future dates in my date table.

How do I do this? Thanks!

[v-eqin-msft · Accepted solution]

Hi @DataAnalyzer ,

I have built a data sample by adding the Date column :

So as you mentioned, the start date of the new table is the lastest value =March 2002 from the original table, and let's assume you want to forecast the next 3 months' sales:

New Table = 
var _last=MAX('Original Table'[Date])
return  ADDCOLUMNS( FILTER(CALENDAR(_last,EOMONTH(_last,3)),DAY([Date])=1),"Month Year", FORMAT([Date],"mmmm yyyy")) 

On my side, March 2022 has Sales =5000 in original table, dates later should use the sum of sales (2000+3000+5000)

Sales = 
var _lastDate=MAXX(ALL('Original Table'),[Date])
var _lastValue=LOOKUPVALUE('Original Table'[Sales],'Original Table'[Month Year],[Month Year])
var _monthDiff= DATEDIFF(_lastDate,[Date],MONTH)
return IF(_lastValue=BLANK(), POWER(1.1,_monthDiff) *SUM('Original Table'[Sales]), _lastValue)

Type = 
var _lastDate=MAXX(ALL('Original Table'),[Date])
return SWITCH(TRUE(),[Date]>_lastDate ,"Foreast",[Date]=_lastDate,"Actual")

Output:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataAnalyzer]

Hi Eyelyn9,

Thank you for providing this solution. I actually came up with something very similar - the piece of the puzzle I was originally missing was Power(,) which I ended up figuring out. As I already had a date table with Month Offset it was very easy to fix the previous month sales to a specific offset and change the offset and forecast percentage based on the results.
Thank you!

[Anonymous]

I believe this could be accomplished with the following measure:

Measure = CALCULATE (

SUM(table[Sales]) * 1.1

)


That said you could always make this more dynamic and complex by replacing "1.1" with a different measure calculating a trend, forecast to actual value, etc. Just some ideas. 

[DataAnalyzer]

Thanks for the response.

I need this measure to be dynamic and calculate the first future month based on the last complete month.
This part is easy.
After this I then need to calculate the next future month based on the last forecasted month and continue doing this based on the filtered result of the date table.
This is where I am struggling to work it out.

In excel this would be:

Month Year	Sales	Type
March 2022	100,000	Actual
April 2022	=$B2*1.1	Forecast
May 2022	=$B3*1.1	Forecast

Thanks