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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DavidB
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
v-huizhn-msft
Employee
Employee

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

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.

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

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

@v-huizhn-msft

I have made an Excel with a sample dataset:

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

DavidB
Frequent Visitor

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

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

Anonymous
Not applicable

Give it a try...

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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