Forum Discussion

a__miller's avatar
a__miller
Frequent Visitor
4 years ago
Solved

Calculate Rolling 3 Month Forecast

Hi, I would like to calculate the next 3 month's forecast, but I'm having trouble with the last month for each category.   Examples: January 3 Month Future Rolling forecast = February Forecast+Mar...
Need Help
  • a__miller's avatar
    a__miller
    4 years ago

    Thanks for the responses. 

     

    After correcting the relationship with the date table, this formula worked

     

    (CALCULATE('Table'[Forecast],datesinperiod('Calendar'[Month],nextmonth(Calendar'[Month]),2,month)))
Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
4 years ago

Hi,

You must have a Calendar Table with a relationship from the Date column of your Data Table to the Date column of the Calendar Table.  In the Calendar Table, write calculated column formulas for Year, Month name and Month number.  Sort the Month name by the Month number.  To your visual, drag Year and Month name from the Calendar Table.  Assuming Forecast is already a measure that you have written, write this measure

3 Month Future Rolling Forecast = calculate([forecast],datesbetween(calendar[date],min(calendar[date]),edate(max(calendar[date]),3)))

Hope this helps.

a__miller's avatar
a__miller
Frequent Visitor
4 years ago

Thanks so much for helping define the relationship better! I was able to correct the dax.

 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    You are welcome.