Forum Discussion

HarsimranPWRBI's avatar
HarsimranPWRBI
Helper I
7 years ago

Problem with UNDERSTANDING Expression For Moving Average

By looking at Report below i can see its computing moving average. But can someone please help me interpret below code line by line. I'm using the "Chapter06_Static Moving Averages" , which can be downloaded from here

 

Prices[MovingAverage200] =
CALCULATE (
    AVERAGE ( Prices[Close] ),
    FILTER (
        ALL ( Prices[Date] ),
        AND (
            Prices[Date]
                >= LOOKUPVALUE (
                    Prices[Date],
                    Prices[Stock], EARLIER ( Prices[Stock] ),
                    Prices[DayNumber], EARLIER ( Prices[DayNumber] ) - 200
                ),
            Prices[Date] <= EARLIER ( Prices[Date] )
        )
    ),
    ALLEXCEPT ( Prices, Prices[Stock] )
)

The Data looks like below

 

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi there.

     

    I've got problems understanding this formula. For instance, I have no idea why I'd write something like:

     

    Prices[Date] <= EARLIER ( Prices[Date] ) -- (1)
    Prices[Stock], EARLIER ( Prices[Stock] ) -- (2)

    as in (1) there is no outer row context and in (2) there is nowhere to take the column Prices[Stock] since the iteration is performed on ALL( Prices[Date] ) only.

     

    Please go to this website rolling-12-months-average-in-dax. It might be helpful.

     

    Best

    Darek