Overcoming A Date Filter

[NeilL]

Hello,
I have been trying to find a solution on how to overcome a date filter on a report for a specific scenario.
I am connected to my Dynamics CRM and imported my opportunities. Based on their close date and estimated duration I create a calculated table and generate a daily revenue number.

Revenue That Exists In Calculated Table In Prior Year

My issue is that often times an opportunity will span across years, as is the case with this example. My page filter is summing up the revenue in the current year using relative date filtering and excluding those values in the prior year as seen in the above image.

For reference I have included a screenshot of my relationships.

Current Relationships

Below is the current formula as written, even though it is incorrect.

softsalesRT = CALCULATE(SUM(Forecast[Revenue]),ALL(Forecast[Date]))

The value of softsalesRT should be 1,999,000 for this opportunity.

Revenue Currently Filtered

I need to be able to sum all the revenues for an opportunity (irrespective of date filters) as a scalar value then subtract out the filtered revenue sums on a monthly basis to generate a backlog.

I'm sure that my request is simple, but I'm stumped.
I certainly appreciate any suggestions and help on this.

[amitchandak]

Not Sure I got it completely.

But if you want a complete forecast for the opportunity. Try like, use opportunity and disable time join.

softsalesRT = CALCULATE(SUM(Forecast[Revenue]), userelation (Opputinity[Opputinity],forecast(Opputinity)),crossrelation(date[Date],forcast[Date],none))