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 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 YearSalesType
March 2022100000Actual
April 2022110000Forecast
May 2022121000Forecast
June 2022133100Forecast

 

For all future dates in my date table.

 

How do I do this? Thanks!

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.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.

    • DataAnalyzer's avatar
      DataAnalyzer
      New Member
      4 years ago

      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'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