Forum Discussion
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 ...
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...
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!
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?
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".
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...