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

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

      Anonymous  Thanks so much for this. This gives me a lot of hope that this is possible within Power BI.

      Is this possible to use a DateTable instead of a hardcoded future date? The reason is that I have more than 100,000 Project ID and I can not hardcode the future date as you have done in your example. Also, the future date will be more dynamic. I'm guessing this will be more of a measure calculation but I'm happy to use columns if we can make that dynamic too.

      Really appreciate your effort on this. your result already blew my mind

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi BGB ,

        I updated my sample pbix file, please check whether that is what you want.

        1. Create a date dimension table

        2. Create a measure as below to get the forecast budget

        Measure = 
VAR _seldate =
    SELECTEDVALUE ( 'Date'[Date] )
VAR _selproject =
    SELECTEDVALUE ( 'Table'[Project ID] )
VAR _budget =
    CALCULATE (
        FIRSTNONBLANK ( 'Table'[Budget], 'Table'[Budget] ),
        FILTER ( 'Table', 'Table'[Project ID] = _selproject )
    )
VAR _date =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Project ID] = _selproject
                && NOT ( ISBLANK ( 'Table'[Budget] ) )
        )
    )
RETURN
    IF (
        _seldate = _date,
        _budget,
        IF (
            DATEDIFF ( _date, _seldate, MONTH ) >= 0
                && DATEDIFF ( _date, _seldate, MONTH ) <= 18,
            CALCULATE (
                DIVIDE ( _budget, 9 ),
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    'Table'[Status Closed] = "Active"
                        && ISBLANK ( 'Table'[Actual Cost] )
                )
            ),
            BLANK ()
        )
    )

        3. Create a table visual as below screenshot

        Best Regards