12 Month Average

[ShaileshKT]

Hi Everyone,

I wanted to calculate the average of last 12 month to forecast sales value. I have below table:

Here I wanted to refer forecasting average value as actual to calculate next month average

for example: If I wanted to get 12 month average for Jan'2025 then calculated Average value of Dec'2024 is to considered as Actual for Dec'24.

Thank You,

Shailesh Kanse

[Anonymous]

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.

[danextian]

I dont think that's what the user wants to achieve. You can see in his previous reply that his logic is recursive which is fairly easy in Excel but a complex one in Power BO

[Anonymous]

Hi,@danextian .Thank you for your reply.
I agree with you that Power BI itself does not support recursive computation.
DAX may do similar recursive effect need to rely on the index column / date column to mark the data, in essence, not the same as the loop recursive.

[ShaileshKT]

Hi @danextian,

Is there any way to achieve this using calculated column?

Thank You

[danextian]

Calculated columns still use DAX and you're looking into creating several calculated columns with the later one referencing the results of the previous ones.

[danextian]

Hi @ShaileshKT 

First, you will need a separate dates table that is related to your fact in a one-to-many single direction relationship. Then, create either of these measures

Sales L12M Excluding Current Month Avg = 
DIVIDE (
    CALCULATE (
        [Sales],
        DATESINPERIOD (
            'Date'[Date],
            --previous end of month date
            EOMONTH ( EDATE ( MAX ( 'Date'[Date] ), -1 ), 0 ),
            -12,
            MONTH
        ),
        REMOVEFILTERS ( 'Date' )
    ),
    12
)


Average L12M Priot to Current = 
CALCULATE (
    AVERAGEX (
        ADDCOLUMNS (
            SUMMARIZE ( 'Date', 'Date'[Calendar Year], 'Date'[Month] ),
            "@sales", [Sales]
        ),
        [@sales]
    ),
    DATESINPERIOD (
        'Date'[Date],
        --previous end of month date
        EOMONTH ( EDATE ( MAX ( 'Date'[Date] ), -1 ), 0 ),
        -12,
        MONTH
    ),
    REMOVEFILTERS ( 'Date' )
)

The second measure takes into consideration that there may not be complete 12 months and so the average is just based on how many months have a value.

Please see attached sample pbix.

[ShaileshKT]

Hi @danextian,

Thank you for the help. But I am expecting below result.

As per the above image, Average for 2020Aug= (Sales from 2019sep to 2020 Jun+ calculated Average of 2020jul and 2020Aug)/12

Is there any way to use forecasted average values use as actual value to calculate next month average.

Thank you,

Shailesh

[danextian]

What you're trying to achieve wihch DAX isn't.

[ShaileshKT]

Hi @danextian ,

Is there any way to achieve this using calculated column?

Thank you