forecast + actual data

[PJHos]

Hi All,

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

Forecasting - Cumulative Forecast =

                    VAR StoressOpenedLY = CALCULATE(

                                                [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

Forecasting - No of Stores Opened & Forecast Data Model =
                            VAR LastopenedDate = CALCULATE( MAX('Sales Attributes'[date store opened].[Date]), REMOVEFILTERS())

                            VAR ActualStoresOpened = COUNT('SalesAttributes'[Total Number Of Open Stores])

                            VAR ForecastTotal = CALCULATE([Forecasting - Cumulative Forecast], KEEPFILTERS( 'Store Attributes Calender'[Date].[Date] > [Forecasting - Last Store Opening Date]))

                            VAR Result = ForecastTotal + ActualStoresOpened

                                                   RETURN

                                                    Result

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

Can someone help please 😥

[amitchandak · Accepted solution]

@PJHos , do not use .date. Mark you calendar table as  date table, that option will go away

Forecasting - Cumulative Forecast =
VAR StoressOpenedLY = CALCULATE(
[Total No Of Storess], DATEADD('Stores Attributes Calender'[Date], -1, YEAR) )

and

VAR ForecastTotal = CALCULATE([Forecasting - Cumulative Forecast], KEEPFILTERS( 'Store Attributes Calender'[Date]> [Forecasting - Last Store Opening Date]))

also can explain need of KEEPFILTERS( 'Store Attributes Calender'[Date]> [Forecasting - Last Store Opening Date])

[PJHos]

Hi Amit

Thank you very much!! It worked

also can explain need of KEEPFILTERS( 'Store Attributes Calender'[Date]> [Forecasting - Last Store Opening Date])

This was so i can have actuals and forecast totals in 1 column

Thank you again, marked as answered😀