cancel
Showing results for
Did you mean:
Frequent Visitor

## Moving average measure with sum

Hello

I am new to this forum and calculated formulas in general - so bear with me if the question is 'too easy' 🙂

I have a simple data set like this example "FRUIT":

 DATE ITEM COUNT 01-01-2017 Apple 10 01-01-2017 Pear 34 01-01-2017 Banana 20 02-01-2017 Apple 17 02-01-2017 Pear 5 02-01-2017 Banana 19

There a many more items and dates of course.

What I want to do, is make a measure that calculates the moving average of "COUNT", but disregarding the "ITEM". So a total moving average of the "DATE" only.

I found this helpful calculation for a measure to get the moving average, but it takes all the date rows into account, instead of the specific date in question .

```AVERAGE 1 MONTH =
CALCULATE (
AVERAGEX ('FRUIT';FRUIT[COUNT]);
DATESINPERIOD (FRUIT[DATE];
LASTDATE (FRUIT[DATE]);
-1;MONTH))```

Is there any way to put in a summarize of the "DATE" column, and the do the moving average?

Best regards

David

8 REPLIES 8
Member

## Re: Moving average measure with sum

Give it a try...

Moving Average = CALCULATE(AVERAGE(Table2[COUNT]),ALLEXCEPT(Table2,Table2[Date]))

Microsoft

## Re: Moving average measure with sum

Hi @DavidB,

Based on your measure, you use  DATESINPERIOD (FRUIT[DATE];LASTDATE (FRUIT[DATE]);-1;MONTH), which means it will calculate the the average during the past one month from your lastdate of FRUIT[DATE]. I am a little confusing about your calculation about specific, for example, you want to get (10+34+20)/3 for 2017-01-01, right? If it is, please use the formula to create a calculated column rather than measure.

`Average=CALCULATE(AVERAGE(FRUIT[COUNT]), ALLEXCEPT(FRUIT,FRUIT[DATE]))`

Best Regards,
Angelia

Frequent Visitor

## Re: Moving average measure with sum

Yes @v-huizhn-msft Angelica, you are correct about the assumption of what I want to get from the calculation.
But I also want the average of the last month (or 30 days) as a moving average.

I am trying to replicate the excel function in chart tools, where I can put in a "Two period moving average".
My data is too fluctuating to just make an average of the last few days - I need to follow it on a greater period.

Maybe I am going about it all wrong, but I (and my boss) love that function in Excel, and I would like to know how to put it in my Power BI dashboard 🙂

Again, I am new to making formulas/code to get what I want.

Thank you.

Microsoft

## Re: Moving average measure with sum

Hi @DavidB,

For 2017-01-01, you don't want to get (10+34+20)/3, you need to the past month's(2016/12/1-2016/12/31) average? But there is no past month's data. Could you please create some fake sample table, and list the desired result, so that we can post solution which is close to your needs. Thanks for understanding.

Best Regards,
Aneglia

Frequent Visitor

## Re: Moving average measure with sum

Hello @v-huizhn-msft

Thank you for taking your time to help. I understand what you mean, but there will always be a starting point in the dates, that do not have a previous month. What Excel does, is just not make a trendline before a full set of one month data is present, so it looks like this:

So if I have a dataset consisting over 6 months, the moving average will be present on the previous 5 months.

If you still want a fake sample table, I can certainly make that.

Frequent Visitor

## Re: Moving average measure with sum

@v-huizhn-msft

I have made an Excel with a sample dataset:

https://1drv.ms/x/s!AibXCp666x1T0wnjjVPzX_0LdwSt

Frequent Visitor

## Re: Moving average measure with sum

Bump
(don't know if this is allowed) 🙂

Edit: To sum it up. I wan't to make the moving average of the sum of the dates - disregarding the fruit (column B).

Microsoft

## Re: Moving average measure with sum

Hi @DavidB,

You'd better open a new thread if you have new problems. There is one problem in one case, thanks for understanding.

Best Regards,
Angelia

Announcements