Calculate Rolling 3 Month Forecast

[a__miller]

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+March Forecast+April Forecast

April 3 Month Future Rolling forecast = May Forecast+June Forecast

The value for June should be 0, but it is showing the total forecast, and I can't figure out why. What am I missing? Also, I'm not having any luck using DATESINPERIOD with this formula. 

3 Month Future Forecast = calculate(SUM('Table'[Forecast]),datesbetween(Table'[Month Reference],nextmonth('Table'[Month Reference]),dateadd('Table'[Month Reference],3,month)),filter('Table','Table'[Category]=earlier('Table'[Category])))

Based on the DAX above, this is what's being displayed

Category	Month Reference	Forecast	3 Month Future Rolling Forecast
A	Jan-22	50	200
A	Feb-22	50	250
A	Mar-22	75	325
A	Apr-22	75	250
A	May-22	100	150
A	Jun-22	150	500
B	Jan-22	200	500
B	Feb-22	200	400
B	Mar-22	200	300
B	Apr-22	100	200
B	May-22	100	150
B	Jun-22	100	900
C	Jan-22	500	1700
C	Feb-22	500	1900
C	Mar-22	600	2000
C	Apr-22	600	1400
C	May-22	700	150
C	Jun-22	700	3600

This is what I'm looking for - the last month of each category should be 0.

Category	Month Reference	Forecast	3 Month Future Rolling Forecast
A	Jan-22	50	200
A	Feb-22	50	250
A	Mar-22	75	325
A	Apr-22	75	250
A	May-22	100	150
A	Jun-22	150	0
B	Jan-22	200	500
B	Feb-22	200	400
B	Mar-22	200	300
B	Apr-22	100	200
B	May-22	100	150
B	Jun-22	100	0
C	Jan-22	500	1700
C	Feb-22	500	1900
C	Mar-22	600	2000
C	Apr-22	600	1400
C	May-22	700	150
C	Jun-22	700	0

[a__miller · Accepted solution]

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]

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]

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

[Ashish_Mathur]

You are welcome.

[lbendlin]

NextThreeMonths = 
var cm = [Date]
return 0+CALCULATE(sum('Table'[Forecast]),ALLEXCEPT('Table','Table'[Category]),'Table'[Date]>cm,'Table'[Date]<EDATE(cm,4))

see attached.