Forum Discussion

BGB's avatar
BGB
Icon for Helper II rankHelper II
4 years ago

Forecast future 18 months

Hi All,   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".   The forecast figure for the next 18 ...
budget
DAX
forecast
Forecasting
BGB's avatar
BGB
Icon for Helper II rankHelper II
4 years ago

Greg_Deckler 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's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    4 years ago

    BGB So like this?

    Measure =
  VAR __Project = MAX('Table'[Project ID])
  VAR __Budget = SUMX(FILTER(ALL('Table'),[Project ID] = __Project),[Budget])
RETURN
  __Budget / 9
    • BGB's avatar
      BGB
      Icon for Helper II rankHelper II
      4 years ago

      To help with this, my desire outcome is below.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi BGB ,

        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] )
                && NOT ( ISBLANK ( 'Table'[Budget] ) )
        )
    )
RETURN
    IF (
        'Table'[Date] = _date,
        'Table'[Budget],
        IF (
            DATEDIFF ( _date, 'Table'[Date], MONTH ) <= 18
                && ISBLANK ( 'Table'[Actual Cost] )
                && 'Table'[Status Closed]="Active",
            DIVIDE ( _budget, 9 ),
            BLANK ()
        )
    )

        Best Regards