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

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

Anonymous You are definitely a genius, no doubt, but I don't think I do an excellent job at explaining.
Your table shows the budget how I want it, but the budget needs to stay on the same table as the actual. when I drag actual to the table as there is no relationship, actual shows on every row because there is no relationship between datetable and fact table. Picture 1

However, when I connect the tables the measure doesn't work. Picture 2

I dont really have to use the datetable as long we can display both actual and budget on the same table when actual is in the past and budget starts from next month. 

I also changed you variable VAR _date to calculate today instead of from the start of the calendar. This is to allow the budget to be in the future. Date worked after change but I cant but actual and budget on the same table is the problem.

Sorry for being a pain but I think you are already there now and I probably just need to change one simple thing to make it right.

Thank you again.

Picture 1:

 


Picture 2: