Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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 DateAccountForecast DateAmount
2018-11-1ABC2019-1-221000
2018-11-1XYZ2018-12-222000
2018-12-1ABC2019-5-221500
2018-12-1XYZ2019-1-152000

 

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?

1 REPLY 1
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.