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??