Calculate 3 Months Moving Average

Question

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.

anonymous · Answer

Hi,&nbsp;meierli&nbsp;
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]) 
&nbsp;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)
&nbsp;

Here are the results:

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

&nbsp;
&nbsp;
&nbsp;
&nbsp;
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&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.
&nbsp;

meierli · Answer

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

anonymous · Answer

Hi,&nbsp;meierli&nbsp;
Thanks for your reply.&nbsp;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.
&nbsp;
&nbsp;
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&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

meierli · Answer

Asmentioned in the beginning I dont use Power BI nor can I upload and download anything.&nbsp;I figured it out yesterday.

anonymous · Answer

Hi,&nbsp;meierli&nbsp;
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?&nbsp;
&nbsp;
Best Regards
Jianpeng Li