Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

    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.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Dear,

     

    I encounter the following error, do you know how to fix it?

     

    A function 'DATESINPERIOD' has been used in a True/False expression that is used as a table filter expression. This is not allowed.