cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Moving Average without dates

Hi,

 

I need to calculate simple moving average for certain dates that do not come into succession, just an average of previous 3 figures, then for next figures, etc.. With date formatted column DAX inserts all the missing days and calculations get messed up.

Here is an example from Excel. 

Can you please help me replicate this result in DAX?

Thank you!

 

Date Sales 3PeriodMovingAverage

06/21/1816 
06/24/189 
06/26/1810                                          11.67
07/01/189                                             9.33
07/02/187                                             8.67
07/05/1813                                             9.67
07/08/1817                                          12.33
07/12/1811                                          13.67
07/13/189                                          12.33
07/18/1811                                          10.33
07/20/1813                                          11.00
07/21/1812                                          12.00
07/26/188                                          11.00
07/29/1818                                          12.67
08/02/1819                                          15.00
08/03/1815                                          17.33
08/04/1814                                          16.00
08/08/1817                                          15.33
08/10/1811                                          14.00
08/15/1819                                          15.67
08/19/1817                                          15.67
08/21/1814                                          16.67
1 ACCEPTED SOLUTION
Phil_Seamark
Microsoft
Microsoft

HI @Anonymous

 

One approach is to add an Index column and then use that to generate your "last three items" average.  I have attached a PBIX file (you can hide the Index col if you like).

 

The index calculated column can be added like this

 

Index = RANKX('Table1','Table1'[Date],,ASC) 

With the moving average column using it in this way

 

Moving Average = 
VAR MyIndex =  Table1[Index]
VAR myResult = 
    AVERAGEX(
        FILTER(
            'Table1',
            'Table1'[Index] > MyIndex-3  && 
            'Table1'[Index] <= MyIndex
           ),'Table1'[Value]
           )
RETURN FIXED(myResult,2)     

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

5 REPLIES 5
Phil_Seamark
Microsoft
Microsoft

HI @Anonymous

 

One approach is to add an Index column and then use that to generate your "last three items" average.  I have attached a PBIX file (you can hide the Index col if you like).

 

The index calculated column can be added like this

 

Index = RANKX('Table1','Table1'[Date],,ASC) 

With the moving average column using it in this way

 

Moving Average = 
VAR MyIndex =  Table1[Index]
VAR myResult = 
    AVERAGEX(
        FILTER(
            'Table1',
            'Table1'[Index] > MyIndex-3  && 
            'Table1'[Index] <= MyIndex
           ),'Table1'[Value]
           )
RETURN FIXED(myResult,2)     

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

mkr
Frequent Visitor

Hi!

 

Very useful post but I have another question. How can I calculate moving average without creating calculated columns and using only measures? I'm connected to SQL analysis server and I cannot create calculated columns. 

 

I'd like to calculate average duration of the last 3 Name_IDs.  My goal is to plot Name_IDs on x axis and Moving average as value. 

Input:
input.jpg

 

 My current attempt:

 

Moving average = 

VAR index = RANKX (
    ALLSELECTED ( NAME ),
    CALCULATE(MAX(Table1[EndDate])), 
    ,
    ASC
)

Return
    AVERAGEX(
        FILTER(
            Table1,
            indexName >index-3  && -- indexName is another measure that is equal to VAR index
            indexName <= index

           ),Table1[Duration]
           )

 

 Thank you!

Anonymous
Not applicable

@Phil_Seamark you, sir, are a HERO.

 

I was trying to create moving averages using dates, but every time I filtered the date to show only the last 12 months (and I have 5 years of data, so it needs filtering) the measure would crash. Using an index solved the problem, I would never think of this.

 

Thanks for sharing.

Anonymous
Not applicable

 

 

Hello @Phil_Seamark once again 🙂

 

I am still stuck with the task. I need a average for prices that are collected on a weekly basis, but often the days are omitted, for that I have created an index that reset after change. It has 4 levels - Region, Municipality, Product Category, Product Name - after we move to next municipality with different category and product, index is reset.

When I use your formula on this file, I get huge numbers, I think it is summing everything beneath without taking into consideration index.

Thanks in advance!

 

Best,

Levan

 

pbMovingAverageHelp.png

Anonymous
Not applicable

Phil, thank you so much!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors