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

 

IDyearquestionrating
12015q11
22015q13
32015q14
42015q12
12016q11
22016q12
32016q15
42016q14
12017q15
22017q14
32017q15

 

I need to calculate average of the column "rating"  for last two years separately and store it in 2 variable.(CURR_YEAR,PREV_YEAR)

i.e. 2017 year data stored in CURR_YEAR and 2016 year data stored in PREV_YEAR

 

Here the challenge for me is every year the data and year changes. Eg:  next year the CURR_YEAR is 2018 and PREV_YEAR is 2017

Based on the data and year,it has to automatically change the values in the variables.

 

My idea is to calculate rank on YEAR column and use rank=1 for CURR_YEAR and rank=2 for PREV_YEAR. I know to calculate RANK and store it in a separate table.I dont want to do that. I want to calculate RANK on the fly and store values in the variable.

 

Appreciate any help.

 

Thanks

KVB

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]
							)
						)

5 Replies

  • 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]
							)
						)
    • vkomarag's avatar
      vkomarag
      Helper III
      9 years ago

      Thanks for your solution.

       

      Is there anyway that we can do the same thing without using calculate. The reason is.

       

      I am calculating current year and previous year average as measure and i am trying to calculate a column based on these two columns,

       

      Indicator=IF(curr_year>prev_year,"Up","Down");

       

      When I am trying to do the above calculation, I am facing circular dependency error and unable to create the indicator field.When i went through a blog reading about circular dependency i came to know that this is because of CALCULATE function.

       

      Any help on this appreciated

       

      Thanks

      KVB

      • vkomarag's avatar
        vkomarag
        Helper III
        9 years ago

        Hi Phil_Seamark

         

        Thanks for your solution.

         

        Is there anyway that we can do the same thing without using calculate. The reason is.

         

        I am calculating current year and previous year average as measure and i am trying to calculate a column based on these two columns,

         

        Indicator=IF(curr_year>prev_year,"Up","Down");

         

        When I am trying to do the above calculation, I am facing circular dependency error and unable to create the indicator field.When i went through a blog reading about circular dependency i came to know that this is because of CALCULATE function.

         

        Any help on this appreciated

         

        Thanks

        KVB

    • 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