Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
BGB
Helper II
Helper II

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 months is the budget figure divided by 9. 

This forecast needs to show on a table for the 18 months straight and not just in the total.

 

See the data example below and thanks in advance.

 

BGB_0-1646658339813.png

 

8 REPLIES 8
BGB
Helper II
Helper II

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


@BGB So like this?

Measure =
  VAR __Project = MAX('Table'[Project ID])
  VAR __Budget = SUMX(FILTER(ALL('Table'),[Project ID] = __Project),[Budget])
RETURN
  __Budget / 9

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

To help with this, my desire outcome is below.

BGB_0-1646694722962.png

 

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

yingyinr_0-1646905216089.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yiruan-msft  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

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

yingyinr_0-1646977747285.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yiruan-msft 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:

BGB_0-1647011379916.png

 


Picture 2:

BGB_1-1647011658940.png

 

Greg_Deckler
Super User
Super User

@BGB Maybe use simple linear regression? Simple Linear Regression - Microsoft Power BI Community


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors