CODEWITHSUNDEEP
powerbidax
moh12
moh12

Reputation: 55

Lookupvalue in Range

I am new in Power BI. I am just going through an article which calculates Moving Average. I would like to know what is happening in LOOKUPVALUE function.

Anyone could tell us EARLIER ( Prices[DayNumber] ) – 8. What it is exactly? It subtract the value EARLIER ( Prices[DayNumber] ) - 8 or show the range from Prices[DayNumber] to 8. And what in the case when EARLIER ( Prices[DayNumber] ) = 2? What it is returned?

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] ) – 8
                ),
            Prices[Date] <= EARLIER ( Prices[Date] )
        )
    ),
    ALLEXCEPT ( Prices, Prices[Stock] )
)

Upvotes: 0

Views: 902

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

The LOOKUPVALUE is searching through the values in the column Prices[Date] looking row by row through the table Prices where the Stock value in that row matches the value in the current row, that is, EARLIER( Prices[Stock] ), and where the DayNumber in that row matches the value in the current row minus 8 days, that is, EARLIER( Prices[DayNumber]) - 8.

Note that the EARLIER function has nothing to do with time. It refers to the earlier row context. Since you are within an iterator function FILTER and then also inside LOOKUPVALUE, you need the EARLIER function to specify that you are talking about the column within the context of the outer function instead of the inner one.

What this measure does overall is average the Close value for all dates between Date - 8 days and Date (inclusive) and for all rows matching the current Stock.

Upvotes: 1

Related Questions