Calculate 3 Months Moving Average

[meierli]

I would like to calculat 3 months moving average,

The month in my Power Pivot are across and not down.

In the data model I have created a date table.

Any suggestions would be great.

UNFORTUNATLY I can't upload/downlad anything from my work PC.

[meierli]

Asmentioned in the beginning I dont use Power BI nor can I upload and download anything.

I figured it out yesterday.

[v-jianpeng-msft]

Hi, @meierli 

Thank you very much for your reply. The above is a reference to the DAX expression for calculating the 3-month moving average. Have you solved the current problem? Can you share your solution so that others in the community can quickly find a solution if they encounter a similar problem? 

Best Regards

Jianpeng Li

[meierli]

Thanks for your reply, but I am not working with Power BI. I am working with Power Pivot in excel.

[v-jianpeng-msft]

Hi, @meierli 

Thanks for your reply. You can achieve the effect of a 3-month moving average using the following DAX expression:

3 Month average = 
AVERAGEX(
    DATESINPERIOD(
        'Calender'[Date],
        MAX('Calender'[Date]),
        -3,
        MONTH
    ),
    [Sales amount]
)
    

Here are the results:

The table on the left is the 3-month moving average that I replied to using the visualization in my previous reply, and the table on the right is the result of the DAX expression created above.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-jianpeng-msft]

Hi, @meierli 

According to your description, if you want to create a 3-month moving average, you can follow the steps below, here I take sales amount as an example, I have the following two tables, the relationship is shown in the figure below:

I created a sales amount metric using the following DAX expression, which I need to calculate the 3-month moving average.

Sales amount = SUM(financials[ Sales]) 

 I put the year, month, and sales amount metrics of the date table into the table is visual.

Right-click on the table visual and create a visual calculation:

Enter the following DAX expression:

average month line = MOVINGAVERAGE([Sales amount],3,FALSE)

Here are the results:

If you want to create this moving average on the line chart, this moving average is also a type step.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.