Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
meierli
Helper I
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.

 

meierli_0-1713904701599.pngmeierli_1-1713904751007.png

meierli_2-1713904784478.pngmeierli_3-1713904830911.png

 

 

 

 

2 REPLIES 2
meierli
Helper I
Helper I

Hi,

 

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

 

meierli_0-1713993637323.pngmeierli_1-1713993657343.pngmeierli_2-1713993690205.png

 

 

 

 

v-yohua-msft
Community Support
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).

 

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.