How to calculate linear forecast cumulative if forecast end date is greater than budget end date

[Mihai_Iso]

Hi,

I need help,please see below:

1. based on actual cumulative values, I need to calculate forecast cumulative linear distribution,and forecast end date (is imposed 17/1/2023) is greater than budget end period (11/1/2023).

------------------------------------------------------------------------------------------------------------------------------------------

2. Another issue: what if I would like to have a paralell profile for forecast with the budget profile (with cumulative amount 500),and to get the forecast end date?

----------------------------

Thank you!

[Mihai_Iso · Accepted solution]

Hello,

I need to calculate forecast cumulative values (linear distribution), when forecast cumulative end date is greater than budget cumulative end date.

Kindly ask you to check the formula below.

---------------------------------------------------------------------------------------------------------------------------

Input:

1.Table

2.budget (periodic)

3.actual (periodic)

4.a new table called “ForecDate”,having as date column name “Forec cut-off”

5.” Sunday Date” is a column date having as last date of the week

6.” Week with  Actual” is a column in the Table, having dates for all cumulative actual values, rest of the dates are bank in the column

1. “ForecEndDate” (last forecast cumulative date) is composed by last budget cumulative end date + _last actual cumulative end date, period (days)

------------------------------------------------------------------------------------------------------------------------

Forecast cumulative =

VAR _budgetall =

    CALCULATE ( SUM ( 'Table'[budget] ), REMOVEFILTERS ( 'Table' ) )

VAR _actualall =

    CALCULATE ( SUM ( 'Table'[actual] ), REMOVEFILTERS ( 'Table' ))

VAR _diffamount = _budgetall - _actualall

VAR _lastbudgetdate =

    CALCULATE ( MAX ( 'Table'[Sunday Date] ), REMOVEFILTERS ( 'Table' ) )

VAR _lastactualdate =

    CALCULATE ( MAX ( 'Table'[Week with  Actual] ), REMOVEFILTERS ( 'Table' ))

VAR  _diffdates_A =

    DATEDIFF ( [lastactualdate], [lastbudgetdate], DAY )

VAR _ForecEndDate =

    CALCULATE ( MAX ( 'Table'[Sunday Date] )+(_lastbudgetdate-_lastactualdate) )

VAR _diffdates =

    DATEDIFF ( _lastactualdate, _ForecEndDate, DAY )

VAR _forecastcumulate =

    SWITCH (

        TRUE (),

        MAX ( 'ForecDate'[Forec cut-off]) = _lastactualdate, _actualall,

        MAX ( 'ForecDate'[Forec cut-off] ) > _lastactualdate

            && MAX ( 'ForecDate'[Forec cut-off] ) <= _ForecEndDate,

            _actualall

                + (

                    DIVIDE ( _diffamount, _diffdates)

                        * DATEDIFF ( _lastactualdate, MAX ( 'ForecDate'[Forec cut-off] ), DAY )

                )

    )

RETURN

    _forecastcumulate

[Padycosmos]

Hope this video helps:

https://www.youtube.com/watch?v=DKgF-5QHY68