Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Forecasting

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 for...
  • Anonymous's avatar
    Anonymous
    8 years ago

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