Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Return Value from an Index Column?

Maybe I am overthinking this and there is an easier way to accomplish what I'm looking for.

 

I have a historical list of stocks and their close price by date as my data source.

 

I have a table with the dates from my data. The dates are not sequential as there is only a close price when the market is open (no holidays, weekends). What I am trying to return in the table is the close price for the date 60 closes in the future. 

 

I didn't think I could use time intelligence since my dates are not sequential. So what I did was create an index column related to the dates. So the earliest date is 1 with the most recent date all the way up to 825. I pretty much want to do a lookup, but my attempt failed. So for the earliest date I would like the close price of 60 closes in the future to appear. I think my lookup didn't work since I have multiple stocks in my data source so there was multiple instances of say the number 60 in the index column. 

 

Any ideas?

Need Help

7 Replies

  • BKirsch12's avatar
    BKirsch12
    Resolver II
    8 years ago

    Here is a measure to get the value from 60 days prior.

     

    60SMA = 
Var abc = CALCULATE (
    COUNT ( Table1[Index] ),
    FILTER ( ALLSELECTED ( Table1 ), Table1[Index] <= MAX ( Table1[Index] ) )
) - 60
return
LOOKUPVALUE(Table1[Amount],Table1[Index],abc)
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      BKirsch12 Thanks for the reply. I tried your solution and I am still getting an error "A table of multiple values was supplied where a single value was expected"

      • BKirsch12's avatar
        BKirsch12
        Resolver II
        8 years ago

        Oh, I missed the bottom part where you mentioned there were multiple instances of each date. LOOKUPVALUE runs just like a vlookup, only it will return an error MSDN:

         

         

        Return Value

        The value of result_column at the row where all pairs of search_column and search_value have a match.

        If there is no match that satisfies all the search values, a BLANK is returned. In other words, the function will not return a lookup value if only some of the criteria match.

        If multiple rows match the search values and in all cases result_column values are identical then that value is returned. However, if result_column returns different values an error is returned.

         

         

        Let me see if there is a way around this.