Forum Discussion

DataAnalyzer's avatar
DataAnalyzer
New Member
4 years ago
Solved

Forecast Future Months

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 foll...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
DataAnalyzer
New Member
4 years ago

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