Forecasting

[Anonymous]

Hi!
I have calculated cumulative forecast and cumulative recorded sales.
I need help with a DAX-expression to alter my forecast based on the results of the recorded sales:

Example:
Original forecast says 10$ per month, cumulative this will give january 10$, february 20$, march 30$ etc... Resulting in cumulative 120$ by the end of the year.

I want to alter that forecast. If sales for january turns out to be only 5$, i want my cumulative forecast by the end of the year to be 115$.

What I want to do is that for every (date) with a recorded sale, the forecast for the year should be the historicly recorded sales + remaining original forecast for the remaining (dates)

Thank you!

[Anonymous · Accepted solution]

I solved it myself!

The three steps are measures and works for me. My "Prognosis allocation" in step 2 is a calculated measure which contains a monthly budget distributed over every calender date.

1. Accumulate Sales (where [Sales] is a sum of sales from my sales column.)

Acc. Sales =
   VAR CurrentDate =
      CALCULATE(
         LASTNONBLANK('Data Sales'[Date]; [Sales]);
      ALL('Data Sales'[Date])
)
RETURN
   CALCULATE([Sales];
      FILTER( ALLSELECTED('Calender'[Date]);
         'Calender'[Date]<=CurrentDate))

2. Calculate remaining forcast based on the last date of your sales

Remaining forcast =
   VAR CurrentDate =
      CALCULATE(
         LASTNONBLANK('Calender'[Date]; 'Calculations Accumulations'[Acc. Sales]);
      ALL('Calender'[Date])
)
RETURN
   CALCULATE(
      [Prognosis allocation];
         FILTER(VALUES('Calender'[Date]);'Calender'[Date]>CurrentDate))

3.

Final forcast =

   CALCULATE(

      'Calculations Accumulations'[Acc. Sales]+[Remaining forcast];
         FILTER( ALLSELECTED('Calender');
            'Calender'[Date]<=MAX('Calender'[Date])))

[Anonymous]

Hi!
I have calculated cumulative forecast and cumulative recorded sales.
I need help with a DAX-expression to alter my forecast based on the results of the recorded sales:

Example:
Original forecast says 10$ per month, cumulative this will give january 10$, february 20$, march 30$ etc... Resulting in cumulative 120$ by the end of the year.

I want to alter that forecast. If sales for january turns out to be only 5$, i want my cumulative forecast by the end of the year to be 115$.

What I want to do is that for every (date) with a recorded sale, the forecast for the year should be the historicly recorded sales + remaining original forecast for the remaining (dates)

Thank you!