Forecast future 18 months

Question

Hi All,&nbsp;I am trying to calculate the forecast for this data for the next 18 months. The 18 months need to be dynamic and only when the status is "Active".&nbsp;The forecast figure for the next 18 months is the budget figure divided by 9.&nbsp;This forecast needs to show on a table for the 18 months straight and not just in the total.&nbsp;See the data example below and thanks in advance.&nbsp;&nbsp;

greg_deckler · Answer

BGB&nbsp;Maybe use simple linear regression?&nbsp;Simple Linear Regression - Microsoft Power BI Community

bgb · Answer

Greg_Deckler&nbsp;There is no need for prediction in this one. The forecast just needs to have the total budget divided by 9 for months greater than this month.

greg_deckler · Answer

BGB&nbsp;So like this?
Measure =
  VAR __Project = MAX('Table'[Project ID])
  VAR __Budget = SUMX(FILTER(ALL('Table'),[Project ID] = __Project),[Budget])
RETURN
  __Budget / 9

bgb · Answer

To help with this, my desire outcome is below.&nbsp;

anonymous · Answer

Hi&nbsp;BGB&nbsp;,
You can create a calculated column as below to get it, please find the details in the attachment:
Forecast Budget = 
VAR _budget =
    CALCULATE (
        FIRSTNONBLANK ( 'Table'[Budget], 'Table'[Budget] ),
        FILTER ( 'Table', 'Table'[Project ID] = EARLIER ( 'Table'[Project ID] ) )
    )
VAR _date =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            'Table',
            'Table'[Project ID] = EARLIER ( 'Table'[Project ID] )
                &amp;&amp; NOT ( ISBLANK ( 'Table'[Budget] ) )
        )
    )
RETURN
    IF (
        'Table'[Date] = _date,
        'Table'[Budget],
        IF (
            DATEDIFF ( _date, 'Table'[Date], MONTH ) &lt;= 18
                &amp;&amp; ISBLANK ( 'Table'[Actual Cost] )
                &amp;&amp; 'Table'[Status Closed]="Active",
            DIVIDE ( _budget, 9 ),
            BLANK ()
        )
    )

Best Regards

Forum Discussion

Forecast future 18 months

8 Replies

Related content

Forecast Future Months

Min Max Tooltip & future months

Datamart future

Forecast future vacancies

Forecasting future months using YTD Average