Forecasting Rest of Year Actuals

[aid928]

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

[v-yiruan-msft]

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

[aid928]

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:

Year	Period	Cost Centre	Account Code	Actuals
2022	1	12345	645477	10000
2022	1	12344	648761	15000
2022	1	12333	648773	12000
2022	2	12345	645477	11000
2022	2	12344	648761	9000
2022	2	12333	648773	7500
2022	2	12222	663254	12000
2022	3	12345	645477	14000
2022	3	12344	648761	11000

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