Forum Discussion

PJHos's avatar
PJHos
Advocate I
3 years ago
Solved

forecast + actual data

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 =        ...
Need Help
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    3 years ago

    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])

amitchandak's avatar
amitchandak
Super User
3 years ago

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's avatar
    PJHos
    Advocate I
    3 years ago

    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😀