Average of Last 18 months projected forward?

[Anonymous]

Hey All,

I'm currently having trouble with a rolling 18 month average. Since I'm a new member and don't yet have the ability to upload a file, a picutre of the data Im using is here: 

Ideally, the measure in PowerBI would take an average of the past 18 months for every month after the given data set, as shown here:

Currently, the code I have for a measure in PowerBI is here:

The issue here is that it ignores the vales already present, and instead uses averages for the months I already haave values for:

Anything you could do to help would be greatly appreciated, and thanks in advance.

[v-yalanwu-msft]

Hi, @Anonymous ;

I test it by my simple data ,and you could try it:

AVE 18 =
AVERAGEX (
    FILTER (
        ALL ( 'Date' ),
        [Date] <= EOMONTH ( MAX ( [Date] ), -1 )
            && [Date] > EOMONTH ( MAX ( [Date] ), -19 )
    ),
    [Total]
)

The final output is shown below:

If not right,can you tell me the result you want to output in my simple data?Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

So the issue I'm having with this solution is that the averages dont take in the new averages:

The solution would look more like this.

[PhilipTreacy]

Hi @Anonymous 

I'm not following and without seeing your source data it's hard to understand the problem.  All I can see is a summary table, I can't tell what's wrong in it.

Can you please supply some raw data and show where the issue is.

Thanks

Phil

[Anonymous]

Instead of using the actual values, this average results in the values for April 2021 - October 2021 to be overwritten with the average, the result I'm looking for should look like this:

Where the last 18 months are averaged after the last month in the data and used as the value for Nov 2021 and so on

[PhilipTreacy]

Hi @Anonymous 

It's still not clear.

You say Instead of using the actual values - what actual values?

Where the last 18 months are averaged after the last month in the data  - do you mean the last month is Dec 2022?

Unfortuantely, this still has the same issue, where the averaging begins before the actual values, - what do you mean?  Please show how averaging begins before the actual values.  What values?

You say the result you're looking for is in that screenshot.  I can see that the value for Nov 2021 is the average of the months preceeding it.  But why are all the value from Dec 2021 to Oct 2022 the same?  And how are the value sin Nov and Dec 2022 calculated?

Phil

[Anonymous]

The actual values are highlighted in this photo, as in they are actual values taken from a dataset that are not calculations.

Values for the months after these, Nov 2021 onward, need to be an average of the 18 months prior.

[PhilipTreacy]

Hi @Anonymous 

Download example PBIX file

I've tried to recreate your data in that file but it's probably not exactly correct.

However you can use this measure to get the previous 18m rolling average

Avg18M = 
   
VAR _Period = DATESINPERIOD( 'DateTable'[Date], MAX( 'DateTable'[Date] ), - 18, MONTH )

VAR _Result = 
        CALCULATE (
            AVERAGEX (
                'DataTable',
                [Avg]
                    ),
            _Period
    )

RETURN _Result

Regards

Phil

[Anonymous]

Unfortuantely, this still has the same issue, where the averaging begins before the actual values, so the values are altere for the dates that have data. 

DAX used: