cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Rolling 3 Months Sales Forecast Snapshots

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?

Highlighted
Super User

## Re: Rolling 3 Months Sales Forecast Snapshots

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.

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 337 members 3,588 guests
Recent signins: