Calculate RANK and FILTER the ranks dynamically based on year.

[vkomarag]

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	q1	4
1	2017	q1	5
2	2017	q1	4
3	2017	q1	5

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

[Phil_Seamark · Accepted solution]

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]

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]

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]

Could you do the Indicator as a calculated measure instead of a calculated column?  

[Phil_Seamark]

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