cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mona01 Frequent Visitor
Frequent Visitor

Nested Calculate to compute Simple moving Average

Hi, I am new in Dax. I am just going through an article which compute simple moving average.
As per my understanding, it is a calculated column So there is a row context. So the VALUES(SMoving[DayNumber]) have a value of current row.


That query will give me a filter rows on which sum have been calculated.

Savrage= CALCULATE (
sum ( SMoving[Close]),
SMoving[DayNumber] >= VALUES ( SMoving[DayNumber] ) - 3,
SMoving[DayNumber] <= VALUES ( SMoving[Date] )
)

But it is not working. the correct query is given below. Can anyone tell what is happning here.

Savrage= CALCULATE

(    

CALCULATE (        AVERAGE ( Prices[Close] ),        Prices[DayNumber] >= VALUES ( Prices[DayNumber] ) - 3,        Prices[DayNumber] <= VALUES ( Prices[DayNumber] )   ),   ALLEXCEPT ( Prices, Prices[Stock], Prices[DayNumber] ))

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Nested Calculate to compute Simple moving Average

Hi @Mona01

 

VALUES( ) is not be affected by row context, only by filter context. If you use VALUES( Table1[Column1] ) directly in a calculated column, you will get a one-column table with the unique values in Table1[Column1], since there is no filter context. 

 

In the second example that you show, the outermost CALCULATE however converts the row context into a filter context through context transition. In this way, when VALUES( ) is evaluated, there is a filter context that actually filters the current row (and its duplicates, if there are any) and therefore VALUES ( Prices[DayNumber] ) will return the value of Prices[DayNumber] in the current row.      

 

You might want to take a look at this and this articles by the Italian gurus. 

1 REPLY 1
Highlighted
Super User
Super User

Re: Nested Calculate to compute Simple moving Average

Hi @Mona01

 

VALUES( ) is not be affected by row context, only by filter context. If you use VALUES( Table1[Column1] ) directly in a calculated column, you will get a one-column table with the unique values in Table1[Column1], since there is no filter context. 

 

In the second example that you show, the outermost CALCULATE however converts the row context into a filter context through context transition. In this way, when VALUES( ) is evaluated, there is a filter context that actually filters the current row (and its duplicates, if there are any) and therefore VALUES ( Prices[DayNumber] ) will return the value of Prices[DayNumber] in the current row.      

 

You might want to take a look at this and this articles by the Italian gurus.