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 the last 20 years (ending on the DATE value of that particular row).

 

I tried replacing AVERAGEX in this formula with RANKX but without success.

 

Moving_Average_20_Years = CALCULATE (
    AVERAGEX ( ALLSELECTED('Session'), 'Session'[Sessions] ),
    DATESINPERIOD (
        'Session'[FullDate],
        LASTDATE ( 'Session'[FullDate] ),
        -20,
        YEAR
    )
)

What should I do?

 

Thank you very much!

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...

9 Replies

  • Shaurya's avatar
    Shaurya
    Memorable Member
    3 years ago

    Hi IvanG92,

     

    You can use:

     

    Rank = RANKX(FILTER(ALL('Session'), DATESINPERIOD('Session'[FullDate], MAX('Session'[FullDate]),-20,YEAR)),'Session'[Value],,ASC)

     

    Works for you? Mark this post as a solution if it does!
    Consider taking a look at my blog: Forecast Period - Previous Forecasts

    • IvanG92's avatar
      IvanG92
      Frequent Visitor
      3 years ago

      It throws an error: "A table of multiple values was supplied where a single value was expected"

    • 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?

  • mangaus1111's avatar
    mangaus1111
    Solution Sage
    3 years ago

    Hi IvanG92 ,

     

    try these 2 calculated columns:

     

    1) 

    Bi_Year =
    VAR  _Year = YEAR('S&P500 D_Yield'[DATE])
    RETURN
      IF(
        ISEVEN(_Year),
        _Year-1,
        _Year
    )
     
    2) 
    RANK =
    RANKX(
          FILTER('S&P500 D_Yield',
                 'S&P500 D_Yield'[Bi_Year] = EARLIER('S&P500 D_Yield'[Bi_Year])
                ),
          'S&P500 D_Yield'[S&P500 D_Yield (Abs)]
    )
     
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    • IvanG92's avatar
      IvanG92
      Frequent Visitor
      3 years ago

      This evaluates the rank against the current year, but I don't want the march value tu be evaluated against future values, only last x past values!

      • mangaus1111's avatar
        mangaus1111
        Solution Sage
        3 years ago

        Hi IvanG92 ,

         

        with my formulas you get the rank for example for the last 24 month from 31.12.1872

         

         

        Can you show me graphically how is your expected result?