I currently have two separate tables in my report that I am struggling to reflect in the same table due to date issues. One shows me my future forecast trend as a % compared to the same period last year. The time period is selected using a slicer. The calculation is:
Fcst vs Sales LY % = DIVIDE([S&OP TY],[Sales LY],0)
My other table is shows me the sales trend for the most recent 3 month period on the same item. The formula is:
3 month YoY % Change = [Rolling 3 Month Sales trend (%).]-1
The rolling three month sales trend formula is:
Rolling 3 month Sales Qty (units) = CALCULATE([Sales Qty (units)],DATESBETWEEN(Dates[Date],MAX(Dates[Date])-90,MAX(Dates[Date])))
Last Years rolling three months is:
Rolling 3 month Sales Qty (units) LY = CALCULATE([Rolling 3 month Sales Qty (units)],DATEADD(Dates[Date],-1,YEAR))
Now if I try to reflect my most recent sales trend in the same table as my forecast vs sales LY % it doesn’t work - presumably because the MAX date part of my rolling forecast is looking into the future as opposed to today.
How can I best adapt my formulas so the rolling 3 month trend is based off today?
Where I am trying to get to is a table that shows my current three month trend and the trend I am forecasting in the future compared to sales for the same period last year. This way I can see anomalies where I am forecasting a 10% increase year on year even though the current trend is showing a decline for instance.