cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
sv11 Regular Visitor
Regular Visitor

Moving Average Calculation

Hi,

I am trying to calculate moving Average for the below data:

MA.PNG

Could someone help me with the DAX code for this calculation?

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Moving Average Calculation

@sv12 ,

Yes and no. Since you are asking about 50 and 200 day moving averages, I'm going to assume you are talking about stock data.  Since stocks dont trade everyday, we cannot use simple calculations like DatesInPeriod because that will look at all the dates in the period even weekends and such. So a 50 day moving average might really be just, say, 45.  so we need to use something else besides the date.

 

I loaded some sample S&P data into Power Query, sorted oldest to newest, and added an new Index column.  This will be what we use for our start and end parameters.  Much easier to see in the file. If it asks when you open, I just grabbed some data from yahoo. 

 

Anyhow, with all that loaded, our table looks like:

Table with Index.png

 

Then we can write this measure:

50 Day Average:=
Var __Length= 50
Var __CurrentIndex = MAX ( FactDailyData[Index] )
Var __PrevIndex= __CurrentIndex - __Length

RETURN
 
If (__CurrentIndex >= __Length,  //this ensures we only get an average after enough data points
AVERAGEX(
    FILTER( 
        ALL ( FactDailyData),
        __CurrentIndex>= FactDailyData[Index] 
        && 
        __PrevIndex< FactDailyData[Index]
    ), 
    FactDailyData[Close*])
 )

This works as is since there's only one stock/index.  If there was more would have to add in something to the filters likes __CurrentStock = FactDailyData[Stock].  Just something to keep in mind.  

 

Not a huge fan of filtering this table, so this maybe something better as a calculated column (done in power query or dax) or a dimension table with index and stock, or something along those lines.  But this method works:

 

Final Table:

Final Table.png

Here's the file:

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

View solution in original post

7 REPLIES 7
Super User
Super User

Re: Moving Average Calculation

Do you have a dedicated Date/Calendar Table?  If so, does it contain some sort of WeekNumber?

sv12 Regular Visitor
Regular Visitor

Re: Moving Average Calculation

@Nick_M 

I had some issue logging in to my old profile, hence had to create a new one.

 

To answer your question, No i donot have a dedicated Date/Calendar Table.

Super User
Super User

Re: Moving Average Calculation

Here's the final table, will explain more below. Excel file is also attached

Final Table.png

 

First thing was to create a Date table, which then would have a field for the cumulative week number of the calender since the average could cross years, so couldnt use just a simple week number.  We relate that to the fact table and will use columns from the DimCalendar table for our filters:

Data Model.png

 

Then from there it is using that column to build our measure:

Avg Amount:=AVERAGE( Table1[Amount] )


10 Week Average:=

//this ensures we dont get a figure till there is a enough data 
VAR __FirstCumulativeWeek=
CALCULATE( 
    MIN( DimCalendar[CumulativeWeekNum]), 
        CALCULATETABLE(Table1,ALL(DimCalendar) )
)
RETURN

IF ( 
    NOT ( 
        ISBLANK( [Avg Amount])
    ),  
    CALCULATE( [Avg Amount],
    	FILTER( 
		ALL ( DimCalendar ), 
			MAX( DimCalendar[CumulativeWeekNum]) >= DimCalendar[CumulativeWeekNum]
			 && MAX( DimCalendar[CumulativeWeekNum]) -10 < DimCalendar[CumulativeWeekNum]
			 && MAX( DimCalendar[CumulativeWeekNum])  >= __FirstCumulativeWeek +9
        )
    )
)

This all assumes there is just weekly data. If there is daily daily, we would need to sum that up to get the weekly total and then take an average of that. But that gets a little more involved.

 

Hope this helps

 

Here's the excel file:

https://1drv.ms/x/s!Amqd8ArUSwDS0QnQC_-ZAY5HwEoY

smpa01 Senior Member
Senior Member

Re: Moving Average Calculation

sv12 Regular Visitor
Regular Visitor

Re: Moving Average Calculation

@Nick_M  Thanks for the detailed response. This really helps.

 

I am trying to calculate a 50 day and 200 day Moving average on similar data with daily values. Is there a easy modification to your provided solution to achieve this?

Super User
Super User

Re: Moving Average Calculation

@sv12 ,

Yes and no. Since you are asking about 50 and 200 day moving averages, I'm going to assume you are talking about stock data.  Since stocks dont trade everyday, we cannot use simple calculations like DatesInPeriod because that will look at all the dates in the period even weekends and such. So a 50 day moving average might really be just, say, 45.  so we need to use something else besides the date.

 

I loaded some sample S&P data into Power Query, sorted oldest to newest, and added an new Index column.  This will be what we use for our start and end parameters.  Much easier to see in the file. If it asks when you open, I just grabbed some data from yahoo. 

 

Anyhow, with all that loaded, our table looks like:

Table with Index.png

 

Then we can write this measure:

50 Day Average:=
Var __Length= 50
Var __CurrentIndex = MAX ( FactDailyData[Index] )
Var __PrevIndex= __CurrentIndex - __Length

RETURN
 
If (__CurrentIndex >= __Length,  //this ensures we only get an average after enough data points
AVERAGEX(
    FILTER( 
        ALL ( FactDailyData),
        __CurrentIndex>= FactDailyData[Index] 
        && 
        __PrevIndex< FactDailyData[Index]
    ), 
    FactDailyData[Close*])
 )

This works as is since there's only one stock/index.  If there was more would have to add in something to the filters likes __CurrentStock = FactDailyData[Stock].  Just something to keep in mind.  

 

Not a huge fan of filtering this table, so this maybe something better as a calculated column (done in power query or dax) or a dimension table with index and stock, or something along those lines.  But this method works:

 

Final Table:

Final Table.png

Here's the file:

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

View solution in original post

narejo Visitor
Visitor

Re: Moving Average Calculation

Worked for me with slight modification

original

===========

Var __CurrentIndex = MAX ( FactDailyData[Index] )

revised

Var __CurrentIndex = FactDailyData[Index]

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 278 members 2,431 guests
Please welcome our newest community members: