12 Month Average

Hello,danextian ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
Hi,ShaileshKT .I am glad to help you.

I agree with danextian's suggestion.

You can try to use the DATEADD function to implement a date offset for data prediction (provided you have a date column in your data table)
You need to have a calendar table with all the dates (and you also need to include the date and time you need to predict).
I noticed that you only have text columns [Year] and [Month Name] in your real data, which is not good for anticipation calculations (at least include a date column to make it easier to calculate the time in the dax) Otherwise you need an index column that can calculate the date offset position.
For data prediction in power bi. The following conditions need to be met

Limitations of DAX calculated columns: in DAX, calculated columns are calculated once and cannot be iterated in a loop. Therefore, if you need to make further predictions based on the predicted values, create multiple calculated columns, each dependent on the result of the previous one. (Easy to create circular dependency problem)
This was also suggested by danextian.

Use of Measure: Measure dynamically calculates results, which can be contextualized in reports, but they also can't be iterated in a loop.Measure calculations must be based on data that already exists in the model.
Real Data Dependency: Both Calculate Columns and Measure, predictive calculations need to depend on real data that already exists in the model. You cannot perform predictive calculations without real data.
Therefore you cannot implement a new forecast value based on a forecast value that has already been calculated, this is not allowed in Power BI DAX. dax calculations must be based on columns, performing the same calculation logic for an entire column at the same time.
Date Table : In order to perform a forecast calculation, the forecast date values (month of year) must be real in the date table in the model. This is because DAX needs these date values to perform time intelligence calculations.

Below is my test:

I have calculated only the expected sales for the next month, if real data exists for the next month to the current month, the real data is displayed, otherwise an average is calculated based on the sales of the previous three months in which the current row month is located (sum / 3)

If you want the results to be affected by the slicer, create a measure instead of a calculate column.
Because the calculation of calculate column & calculate table is done when the model is loaded with data (or the data itself changes, i.e. triggers a data refresh), they really exist in the model and will not be affected by slicer/filter datas.

I hope you find the following issues helpful:

Solved: Dashboard of the future development of the invento... - Microsoft Fabric Community
Solved: looping using DAX - Microsoft Fabric Community
If possible, do the work of forecasting future data in the data source itself (e.g. excel) and import the processed data into Power BI to produce reports, rather than performing iterative forecasting calculations in Power BI.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.