Calculating future forecast months using previously calculated months

[JateenK]

Hi all

I have a custom forecast model required that uses the last 3 months of sales as part of the forecast formula.

This is required to start from the current month. IE: March is currently in progress so forecast would start from 1st March.

Forecasting March is straightforward - as prior 3 months are actuals - but how to forecast April is the issue - as it would use actuals from Jan and Feb but then also need to also use the forecasted March value.

To illustrate the calculations required, i've uploaded the excel formula's:

Also uploaded is a simple powerbi model showing the calculations and values used - with the problem being the measure "

Sales Value Prior 3Mth" which only looks back at last 3 actual months and then trails off very quickly when attempting to go into future months.

Excel file

PowerBI File

PS: This will be implemented on a Premium model but cannot use direct query.

Thanks 

Jateen

[Anonymous]

Hi @JateenK ,
We can understand your needs based on your description, but we can't open the file you provided. You can follow the guidelines at the following link to provide usable data.
How to Get Your Question Answered Quickly - Microsoft Fabric Community
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best regards,

Albert He

[JateenK]

Hi @Anonymous 

Sorry about the file links.

I've shared on OneDrive and corrected the link.

Thanks