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

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!