Forum Discussion
Rolling Sales Forecast
Dear all,
I would like to calculate the sales forecast of future months based on the historical previous 6-month sales data, like this:
Can you advise? Thanks
| Month | Product | Sales Amount | Month | Product | Sales Forecast | methodology | |
| Jan | Apple | 100 | Jul | Apple | 232 | = (total of previous 6-month sales) / 6 | |
| Jan | Orange | 200 | Jul | Orange | 258 | ||
| Jan | Mango | 350 | Jul | Mango | 367 | ||
| Feb | Apple | 540 | Aug | Apple | 254 | ||
| Feb | Orange | 200 | Aug | Orange | 267 | ||
| Feb | Mango | 320 | Aug | Mango | 370 | ||
| Mar | Apple | 300 | |||||
| Mar | Orange | 455 | |||||
| Mar | Mango | 500 | |||||
| Apr | Apple | 150 | |||||
| Apr | Orange | 290 | |||||
| Apr | Mango | 333 | |||||
| May | Apple | 200 | |||||
| May | Orange | 200 | |||||
| May | Mango | 350 | |||||
| Jun | Apple | 100 | |||||
| Jun | Orange | 200 | |||||
| Jun | Mango | 350 |
Anonymous , hope you have date, and or create a date using month year
with help from date table
Rolling 6 till this month
Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-6,MONTH))
Rolling 6till last month
Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),-1) ,-6,MONTH))
for month
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
3 Replies
- amitchandak
Super User
Anonymous , hope you have date, and or create a date using month year
with help from date table
Rolling 6 till this month
Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-6,MONTH))
Rolling 6till last month
Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),-1) ,-6,MONTH))
for month
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.