Moving RANK in a calculated column

[IvanG92]

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!

[mangaus1111]

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]

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]

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?

[IvanG92 · Accepted solution]

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

[IvanG92]

Let's use 12 months for better visual clarity:

I need each current row to be evaluated against ONLY PAST VALUES. Your fomula evaluate february 2020 for example against the entire 2020 and 2019. Instead it should evaluate the rank against feb18 - feb20. It's exactly like a "moving average", but is a "moving rank".

[mangaus1111]

Hi @IvanG92 ,

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

[IvanG92]

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?

[Shaurya]

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]

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