how to Show the last 3 years in report

[Anonymous]

Hi,

I am tring to get the last 3 years in my report. it look like i have in my report years 2020 , 2019, 2018, 2017,2016,2015 

i wnat to show onaly 2020,2019,2018

also if the year change to 2021

it should update to

2021,2020,2019 and hide 2018, 2017,2016,2015 

thank you.

[amitchandak]

@Anonymous , if the year is coming from date. Use relative date slice

https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range

[nandic]

Hi @Anonymous ,

You need to filter data in Power Query.
As @AllisonKennedy  mentioned, you can use built in functions to calculate last N months, years, days. 
But if you want to make sure it calculates years, where last 3 years are 2018, 2019, 2020 no matter which current month it is, here is approach:
1) create new column for year based on data in table: Date.Year([Date])
2) create new column for current year: Date.Year(DateTime.Date(DateTime.LocalNow()))
3) create new column to calculate difference between current year and year in data: [Current Year]-[Year]

4) filter this colum to show only number 0,1,2 (it will filter only last 3 years)

Cheers,

Nemanja

[AllisonKennedy · Accepted solution]

Thanks @nandic
Interesting approach! 🙂
The built in date filters in Power Query behave the same regardless of current date, so when you filter for in the previous 1 years, it will give you all of 2019 right now. So to get 2018, 2019 and 2020 we can do an OR filter to get in the previous 2 years or in this year.

The below code will work assuming you replace
#"Changed Type1" with the name of your last step
[OrderDate] with the name of the date column:

= Table.SelectRows(#"Changed Type1", each Date.IsInPreviousNYears([OrderDate], 7) or Date.IsInCurrentYear([OrderDate]))

Otherwise just click the arrow filter on the date column and choose 'in the previous' and add the OR condition to also be in this year.

[Anonymous]

I have similar problem but i'm not able to decide how to put your solution into it. 

I just want that when i click at the slicer of year (same as the one in the question) I should see the graphs as it is but along with that in the other visual i want to see only the past 3 years of sales/profit in number. 

Can anyone please guide me. I did for the current year and  last year by using the SAMEASLASTYEAR formula, visualised on the card. However, i tried alot of things but failed to do for last 2 years or mainly N-1, N-2 and N-3 year

Thank you so much in advance !

[nandic]

Thanks @AllisonKennedy  for this time-saver 🙂

[AllisonKennedy]

I would suggest doing this filter in Power Query. See if this post can help https://blog.crossjoin.co.uk/2018/01/08/in-the-previous-date-filters-in-power-bi-gettransform-power-...