Trend calculation that ignores date slicer


#1

Hi - I have the following formula that calculates rolling sales based on the latest three months - I then use this to calculate trends:

Trend Test = CALCULATE([Total Sales.],(DATESBETWEEN(Dates[Date],MAX('Sales Data'[Date Value])-84,MAX('Sales Data'[Date Value]))))

I have a date slicer attached to the table for various other calcs in the same table and want to keep this. My problem is that when I move my dates into the future to look at forecast I can no longer see the latest rolling sales because I am in a date period for which we have no sales - I need a way in the above formula of saying ‘ignore the date slicer, just look at the latest three months in sales data table’

I don’t want to edit interaction because it causes issues in other parts of my table - hoping there is some way of using ALL or ALLEXCEPT to counteract the slicer in the above formula??


#2

Is there any reason why you couldn’t just use time intelligence to project these results forward.

Here’s an exact example of how to do this.

It feels like this would work?