forecast + actual data

Question

Hi All,&nbsp;I have calculated a forecast based on historic data for the last 6 years using the below and dividing it by total years. This works fine&nbsp;Forecasting - Cumulative Forecast = &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VAR StoressOpenedLY = CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Total No Of Storess], DATEADD('Stores Attributes Calender'[Date].[Date], -1, YEAR) )I now want to combine this with the actuals so the forecast data only begins where the actuals end and I am having problems with the following DAX&nbsp;Forecasting - No of Stores Opened &amp; Forecast Data Model =&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VAR LastopenedDate = CALCULATE(&nbsp;MAX('Sales Attributes'[date store opened].[Date]),&nbsp;REMOVEFILTERS())&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VAR ActualStoresOpened = COUNT('SalesAttributes'[Total Number Of Open Stores])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VAR ForecastTotal = CALCULATE([Forecasting - Cumulative Forecast], KEEPFILTERS( 'Store Attributes Calender'[Date].[Date] &gt; [Forecasting - Last Store Opening Date]))&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VAR Result = ForecastTotal + ActualStoresOpened&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;RETURN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Result&nbsp;I have run the result on the other variables and all work fine except forecase total. As soon as i use the FILTER logic it gives me a 'placeholder' error&nbsp;Can someone help please&nbsp;😥

amitchandak · Accepted Answer

PJHos , do not use .date. Mark you calendar table as&nbsp; date table, that option will go away 
&nbsp;
Forecasting - Cumulative Forecast =VAR StoressOpenedLY = CALCULATE([Total No Of Storess], DATEADD('Stores Attributes Calender'[Date], -1, YEAR) )
&nbsp;
&nbsp;
and
&nbsp;
VAR ForecastTotal = CALCULATE([Forecasting - Cumulative Forecast], KEEPFILTERS( 'Store Attributes Calender'[Date]&gt; [Forecasting - Last Store Opening Date]))
&nbsp;
&nbsp;
also can explain need of KEEPFILTERS( 'Store Attributes Calender'[Date]&gt; [Forecasting - Last Store Opening Date])

Forum Discussion

forecast + actual data

2 Replies

Related content

Forecasting with DAX

Forecasting

Calculate difference between versions of Forecast data

Live Weather Forecast Dashboard

Inventory Forecast