How to force my table only display last 5 year data by modify the REVENUE AMT expression ?

[Anonymous]

Hi All

I have a table it will display for last 12 year data , i only need to make the table display last 5 year. I aware that i can make use of year filter to force the table display last 5 year. can some one share with me how to add year filter into my expression ?

My PBI file :-

https://www.dropbox.com/s/9enfu4863y0piws/COMPETITOR_V009.pbix?dl=0

[Ashish_Mathur]

Hi,

You may refer to my solution in this Blog article - Flex a Pivot Table to show data for x months ended a certain user defined month.

Hope this helps.

[Anonymous]

Hi ASH

Wow you are excel expert  , you write so many blog.

Paul

[Ashish_Mathur]

Thank you for yoru kind words.

[sevenhills · Accepted solution]

If it is me, I will add another column to the Date table and use it as filter to your report. I avoid measure for date tables, my personal preference.

Column

Is Last 5 Yr = IF ('Date'[Year] >= Year(TODAY()) - 5, 1, 0)

Filter

Is Last 5 Yr = 1

[Anonymous]

Hi Steven

Can you share with me how to create the new column ? as i get error :-

[sevenhills]

Hi 

Go to the Data tab, Click "Date" on the right and click new column ... 

PS: Based on your image, you are trying to add calculate column on a different table and not on DATE table. 

- SV

[Anonymous]

Hi Steven

Now the table is what i need , for below i set the last 2 year , may i know how to make year label display 2020 and 2019 ? now display as 1 and 0

[sevenhills]

Hi

Since you are power user, I thought you will do and did not tell all the steps...

Select the grid and add the filter (or) you can set as "Filters on this page"

I  noticed the formula is giving data for 2015 and above. Change the formula ">=" to ">" if you dont want 2015.

PS: My name is NOT Steven. I am glad to get a new name. 

Thanks

SV

[Anonymous]

Hi Sevenhills , So sorry that i add one more T after S.

Okay now i understanding your approach. will stick to this approach.

Paul

[sevenhills]

No need for sorry. Typos happen. (atleast I got a chance to listen new name for me 😉 )

Hope you got the solution. 

Regards