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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
ElliotP
Post Prodigy
Post Prodigy

Moving Average

Hi,

 

I've just started using PowerBI and I'm a massive fan of the tool. I've been trying to calculate a simple moving average for Months vs Sessions.

 

I've tried quite a lot of things, googling, etc for a few hours right now and I've read and tried to apply this link (http://community.powerbi.com/t5/Developer/How-to-calculate-Moving-Average-based-on-a-Rolling-10-hour... but everytime I input and edit to my means I receive the errors;

 -Can't resolve a valid name.

-Something like "can't find row being referred to".

 

I've tried creating it as a measure, as a column, decomposing the equation into multiple columns/functions, etc. I am at a bit of a wall and I would greatly apprecaite any help.

 

It's google analytics data so it looks roughly like this:

Month of the YearSessions
1100
2150
3200
4250
5300
6350
7400
8450
9500
10550
11600
12650

I'm quite comfortable transforming and shaping the data in excel, but I would really like to be able to shape my data in powerbi as it seems I've just misunderstood how to apply a dax expression as opposed to do all my data transformation in excel and then simply importing.

 

Any help would be much appreciated.

1 ACCEPTED SOLUTION

@ElliotP

 

Firstly you need a column of date with full date format. Then you can use calculated measure to get the expected result. Please refer to following steps.

 

  1. Create a calculated column for the date.
    FullDate =
    DATE ( 2016, 'Session'[Month of the Year], 1 )
    
  2. Create a measure for 3 months moving average. You can change the number of months if you want.
    Moving_Average_3_Months = 
    CALCULATE (
        AVERAGEX ( 'Session', 'Session'[Sessions] ),
        DATESINPERIOD (
            'Session'[FullDate],
            LASTDATE ( 'Session'[FullDate] ),
            -3,
            MONTH
        )
    )
    
  3. Drag the Line Chart into your canvas as below.
    125.jpg

View solution in original post

30 REPLIES 30
Riyukh
New Member

Thanks bro. It worked

ElliotP
Post Prodigy
Post Prodigy

Hey,

 

I really appreciate the responeses especially so quickly.

 

I'm not looking to do anything with Pivottable tbh, I might be missing something; but I was hoping for some direction in terms of DAX transforming data within PowerBI.

 

The links are in part useful in that they allow me understand a little bit and different approach to filtering the data.

 

I grabbed dates from analytics, set them as dates, but so I can try the datesbetween approach, but I'm still not sure and I'm unable to get past the error values.

 

I'm going to spend some time tonight reading upon on dax and creating formulas. Any help would be greatly appreciated!

@ElliotP

 

Firstly you need a column of date with full date format. Then you can use calculated measure to get the expected result. Please refer to following steps.

 

  1. Create a calculated column for the date.
    FullDate =
    DATE ( 2016, 'Session'[Month of the Year], 1 )
    
  2. Create a measure for 3 months moving average. You can change the number of months if you want.
    Moving_Average_3_Months = 
    CALCULATE (
        AVERAGEX ( 'Session', 'Session'[Sessions] ),
        DATESINPERIOD (
            'Session'[FullDate],
            LASTDATE ( 'Session'[FullDate] ),
            -3,
            MONTH
        )
    )
    
  3. Drag the Line Chart into your canvas as below.
    125.jpg

Champion, this helped heaps 😄

Anonymous
Not applicable

Hi @v-sihou-msft

 

I'm a new user as well and have hit a snag while calculating moving average for weeks. Firstly, It's a great formula that you've put up for calculating the moving average of -3 months!

 

But, I'm trying to do the same thing with weeks. I have my raw data in daily entries. I'm trying to get the average per week and then take the moving average among weeks. My moving average interval would be -2 and +2 weeks. How do I do this? (as the datesinperiod function doesn't have the option to mention intervals in weeks!). 

 

Thanks in advance and much appreciated!

 

Regards,

sshroffm

Anonymous
Not applicable

Hello, @v-sihou-msft 

 

I exactly followed yoru approach but my moving averages are the same as the nomal averages for each month. It looks like other people are having the same issues. Any insights?

 

Thanks!

For anyone having issues where the MA is the same as the regular averages, check what date column you have in the "datesinperiod" formula. Make sure the date column you reference here is exactly the date column you have pulled into your visual/Table. Common error here is to reference a diffferent datecolumn.

Maybe this will help.

Anonymous
Not applicable

Trailing_Average_3_Months = CALCULATE(AVERAGEX('Active List Table', 'Active List Table'[Sales_Pric]),DATESINPERIOD('Active List Table'[List_Date],LASTDATE('Active List Table'[List_Date]), -3, MONTH))

 

In my case this is not work. 

Is it possible to remove the first two months? @v-sihou-msft 1/1/2016 and 2/1/2016 average 100 and 125 are not the numbers I want to include in my report.

Hi did someone ever replied with a solution for this? I'm also interested in showing just the part with the correct values and not the rump to it

I have the same question. How can you remove the initial values, as they're not truly averages.

Thank you so much for the responses.

 

@v-sihou-msft Thank you so much.

 

@sdjensen I took your advice and looked into creating its own date table, extremely useful!

 

I used this code to create a 10 day Simple Moving average:

Moving_Average_3_Months = 
CALCULATE (
    AVERAGEX ( 'All Web Site Data', 'All Web Site Data'[Sessions] ),
    DATESINPERIOD (
        'Table123'[DateKey],
        LASTDATE ( 'Table123'[DateKey] ),
        -10,
        DAY
    )
)

Link: https://gyazo.com/bb02f6541c6f0e99c4477f6b4ead42cd

 

The drill down feature doesn't seem to want to work for me. Should I simple create my own hierarchy for this?

Hi,

 

I am trying to calculate moving 3 month average and came across this post. I followed the steps exactly as mentioned in the post but no luck so far. My formula looks like this...

 

MA_3M = CALCULATE(AVERAGEX('Session', 'Session'[Sessions]), DATESINPERIOD('Session'[FullDate], LASTDATE('Session'[FullDate]),-3,MONTH))

 

While seeing the results, the Sessions and the Moving average show the same numbers....what am I missing???

 

Pleae help!

Did you find the solution? Facing the same problem

Hello,

 

I followed the steps above to create the moving average however I get an error stating that there are duplicates in my DATESINPERIOD column (this is because there are and there are supposed to be...). I was hoping it would add all the values for one day together and then average these - do you have any suggestions?

 

Thanks 🙂

 

Max

 

 

Hi @arunksri, @Anonymous,

 

I use a similar formula and am not able to get the moving average.

Were you guys able to figure out what the problem was?

Maybe I am having the same problem as you guys.

 

Thanks,
Preetish

Anonymous
Not applicable

via below logic, you can find the Trailing average of the 12 months. 

 

 

[Trailing 12 Month Average ] = DIVIDE(
CALCULATE(
SUM(Avg_sales[Sales_Count]),
DATESBETWEEN(
Avg_sales[List_Date],
FIRSTDATE(DATEADD(Avg_sales[List_Date],-12,MONTH)),
LASTDATE('Avg_sales'[List_Date])
)
),12)

 

 

In my application this approach collected an extra month's worth of data. The corrected forumula which worked for me is below. I tested this with manual calculations in Excel.

 

[Trailing 12 Month Average ] = DIVIDE(
CALCULATE(
SUM(Avg_sales[Sales_Count]),
DATESBETWEEN(
Avg_sales[List_Date],
FIRSTDATE(DATEADD(Avg_sales[List_Date],-11,MONTH)),
LASTDATE('Avg_sales'[List_Date])
)
),12)

In my application this approach collected an extra month's worth of data. The corrected forumula which worked for me is below. I tested this with manual calculations in Excel.

 

[Trailing 12 Month Average ] = DIVIDE(
CALCULATE(
SUM(Avg_sales[Sales_Count]),
DATESBETWEEN(
Avg_sales[List_Date],
FIRSTDATE(DATEADD(Avg_sales[List_Date],-11,MONTH)),
LASTDATE('Avg_sales'[List_Date])
)
),12)

Thank you @Anonymous . I just got it working with a similar DAX expression.  

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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