Office 365 Power Pivot Calculate 3 Mos and 3 Mos moving average

[meierli]

I would like to calculate 3 months and 3 month moving average on Sum of Total Paid excluding the current month.

For my data I have created a date table and a month_year table that has a realtionship to my data set.

[meierli]

Hi,

The formula did not work. Here si what I have. Unfortunately I can not upload anything.

[v-yohua-msft]

Hi, @meierli 

If you haven't created a measure yet, you'll need to create a measure to calculate the total amount paid. This can be done using the DAX formula:

Total Paid Sum = SUM(YourDataTable[TotalPaid])

Calculate the 3-month moving average (excluding the current month): You need to create a measure that calculates the moving average. Since you want to exclude the current month, a combination of DAX functions will be used to achieve this. The AND function can help filter for dates from the last 3 months (excluding the current month). Here's an example DAX formula:

3 Month Moving Avg Excl Current = 
CALCULATE(
    AVERAGEX(
        DATESINPERIOD(
            'DateTable'[Date], 
            EDATE(TODAY(), -1), 
            -3, 
            MONTH
        ), 
        [Total Paid Sum]
    ), 
    ALL('DateTable')
)

This formula calculates the average of the Total Payouts over the last 3 months (excluding the current month).

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

Yongkang Hua

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