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
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
Employee
Employee

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

7 REPLIES 7
Phil_Seamark
Employee
Employee

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!

Thanks for the suggestion! How do I resolve the same question, using moving standart deviation? I tried to substitute AVERAGEX for STDEVX.P, but the result was not correct.

I believe I've found my mistake. I had to fill the blank values with 0 and use STDEVX.S

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