Forum Discussion

IvanG92's avatar
IvanG92
Frequent Visitor
3 years ago
Solved

Moving RANK in a calculated column

Hi everyone,   I have a table with 2 columns: DATE and VALUE I need a calculated column with the value of the column VALUE ranked against the other values of the column VALUE that are released in ...
Need Help
  • IvanG92's avatar
    IvanG92
    3 years ago

    I think I found the solution:

     

    SP D_Yield (Abs) NORM. = rankx(CALCULATETABLE('SP D_Yield',DATESINPERIOD('SP D_Yield'[DATE],LASTDATE('SP D_Yield'[DATE]),-2,YEAR)),'SP D_Yield'[SP D_Yield (Abs)])
     
    I still don't know what it works with CALCULATETABLE and not with FILTER...
mangaus1111's avatar
mangaus1111
Solution Sage
3 years ago

Hi IvanG92 ,

 

can you please send us a sample table and your expected result?

IvanG92's avatar
IvanG92
Frequent Visitor
3 years ago

Sure! Here's the file: https://we.tl/t-tBWmKmgpcs

 

I need to rank (in the table D_YIELD) the column "S&P500 D_Yield (Abs)" against the other values, but not against the entire column, just against the last x years, month, or days. Using a RANKX againt the whole table will return values from 1 to 1800 since the column is made of 1800 rows. With the correct expression I expect values only from 1 to say 24 if I choose to evaluate the rank against the last 2 years (each row correspond to a month). Is that clear enough?