Forum Discussion

vkomarag's avatar
vkomarag
Helper III
9 years ago
Solved

Calculate RANK and FILTER the ranks dynamically based on year.

Hi I have the below data   ID year question rating 1 2015 q1 1 2 2015 q1 3 3 2015 q1 4 4 2015 q1 2 1 2016 q1 1 2 2016 q1 2 3 2016 q1 5 4 2016 q...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    Hi vkomarag

     

    Do you only ever care about the most recent two years?

     

    You could try adding these measures to determine the values you need

     

    CURR_YEAR = MAX('Table1'[year])
    PREV_YEAR = MAX('Table1'[year]) - 1

    Then other measures could use these eg.

     

    Curr_Year Average = CALCULATE(
						AVERAGE('Table1'[rating]),
						FILTER(
							'Table1',
							'Table1'[year]=[CURR_YEAR]
							)
						)
Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

Hi vkomarag

 

Do you only ever care about the most recent two years?

 

You could try adding these measures to determine the values you need

 

CURR_YEAR = MAX('Table1'[year])
PREV_YEAR = MAX('Table1'[year]) - 1

Then other measures could use these eg.

 

Curr_Year Average = CALCULATE(
						AVERAGE('Table1'[rating]),
						FILTER(
							'Table1',
							'Table1'[year]=[CURR_YEAR]
							)
						)
Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

Otherwise here is how you might add your "Year Rank" column to your table and it will be dynamic

 

Year Rank = CALCULATE(
                    DISTINCTCOUNT('Table1'[year]),
                    FILTER(
                        ALL(Table1),
                        'Table1'[year] > EARLIER('Table1'[year])
                        )
                       )+1