cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

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

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

5 REPLIES 5
Highlighted
Microsoft
Microsoft

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

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Highlighted
Microsoft
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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Highlighted
Helper III
Helper III

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

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

Highlighted
Helper III
Helper III

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

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

Highlighted
Microsoft
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?  


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Helpful resources

Announcements
Community Blog

Community Blog

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

Using the Community

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

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

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors