cancel
Showing results for
Did you mean:
Highlighted Helper III

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted Microsoft

## Re: Calculate RANK and FILTER the ranks dynamically based on year.

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

Proud to be a Datanaut!

5 REPLIES 5
Highlighted Microsoft

## Re: Calculate RANK and FILTER the ranks dynamically based on year.

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

Proud to be a Datanaut!

Highlighted Microsoft

## Re: Calculate RANK and FILTER the ranks dynamically based on year.

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

Proud to be a Datanaut!

Highlighted Helper III

## Re: Calculate RANK and FILTER the ranks dynamically based on year.

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

Highlighted Helper III

## Re: Calculate RANK and FILTER the ranks dynamically based on year.

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

Highlighted Microsoft

## Re: Calculate RANK and FILTER the ranks dynamically based on year.

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

Proud to be a Datanaut!

Announcements #### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members. #### Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start. #### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications #### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021 Top Solution Authors
Top Kudoed Authors
Users online (971)