cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DavidB Frequent Visitor
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": 

 

DATEITEMCOUNT
01-01-2017Apple10
01-01-2017Pear34
01-01-2017Banana20
02-01-2017Apple17
02-01-2017Pear5
02-01-2017Banana19

 

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

Re: Moving average measure with sum

Give it a try...

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

Microsoft v-huizhn-msft
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

DavidB Frequent Visitor
Frequent Visitor

Re: Moving average measure with sum

Thank you both for replying.

 

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 v-huizhn-msft
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

DavidB Frequent Visitor
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:

 

Capture.PNG

 

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.

DavidB Frequent Visitor
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

DavidB Frequent Visitor
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 v-huizhn-msft
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

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)