cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

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

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.

2 REPLIES 2
Helper I

Hi,

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

Community Support

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).

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.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors