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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
aid928
Helper I
Helper I

Forecasting Rest of Year Actuals

Hi folks,

 

I've tried searching the forum for an answer to this question however I couldn't find an answer.

 

Basically, I'm trying to create a chart that presents month by month actual information, but also shows a forecast based on the YTD actual spend so far.

 

Most other posts have a Forecast column with balances, which would make things much easier. However I am hoping Power BI can calculate this forecast itself based on Actual spend through the year.

 

Could someone please advise on how this would be possible? I know there is a Forecast option in the Line Graph but this wouldn't work as since the Forecast Length always stays the same this would push out the length of time on the graph, we're only concerned with current year data.

 

Thanks,

 

Aidan

2 REPLIES 2
v-yiruan-msft
Community Support
Community Support

Hi @aid928 ,

You can refer the following links to get it:

Showing actuals and forecasts in the same chart with Power BI

Forecasting Logic in Power BI with DAX

Forecasting future months using previous months data on a fiscal period

 

Measure =
IF (
    ISBLANK ( SUM ( 'Actual Spend'[Corporate Card Spend] ) ),
    AVERAGEX (
        CALCULATETABLE (
            VALUES ( 'Calendar Dates'[Month/Year] ),
            ALLEXCEPT ( 'Calendar Dates', 'Calendar Dates'[Fiscal Year] )
        ),
        CALCULATE ( SUM ( 'Actual Spend'[Corporate Card Spend] ) )
    )
)

Forecasting with seasonality in Power BI

 

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

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.

Hi yingyinr,

 

Thanks very much for this, I don't think it 100% solves my problem but certainly is on the right track.

 

Here's an example of what my file looks like:

 

YearPeriodCost CentreAccount CodeActuals
202211234564547710000
202211234464876115000
202211233364877312000
202221234564547711000
20222123446487619000
20222123336487737500
202221222266325412000
202231234564547714000
202231234464876111000

 

Because my file doesn't have future periods/dates in place, I'm not sure whether your solution would work for me. Also the fact that we have multiple rows per period based on the Finance coding could cause problems.

 

Thanks for your initial message and hopefully this makes things more clear.

 

Aidan

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.