Rolling 3 Months Sales Forecast Snapshots

[Anonymous]

Hi All,

I have monthly snapshot of my CRM that captures all opportunities and key information:

Snapshot Date	Account	Forecast Date	Amount
2018-11-1	ABC	2019-1-22	1000
2018-11-1	XYZ	2018-12-22	2000
2018-12-1	ABC	2019-5-22	1500
2018-12-1	XYZ	2019-1-15	2000

I'm trying to create a measure from this table that sums the amount for any opportunity scheduled to close 3 months from the snapshot date. I've tried the following, 

Rolling = CALCULATE(SUM('OppHistory'[Amount]),FILTER('OppHistory','OppHistory'[Forecast Date]>='OppHistory'[Snapshot Date]),FILTER('OppHistory','OppHistory'[Forecast Date]<=DATEADD('OppHistory'[Snapshot Date],3,MONTH)))

but for some reason, when I put this into a chart where the axis is "snapshot date" it cuts off the last two months. Can anyone help me with this measure?

[Ashish_Mathur]

Hi,

The answer should be 5000.  This measure works

=SUMX(FILTER(SUMMARIZE(Data,[Snapshot Date],[Account],"ABCD",EDATE(MIN([Snapshot Date]),3),"EFGH",MIN([Forecast Date]),"IJKL",SUM(Data[Amount])),[EFGH]<=[ABCD]),[IJKL])

Hope this helps.