topic Re: how to Show the last 3 years in report in DAX Commands and Tips

how to Show the last 3 years in report

Anonymous — Tue, 21 Jul 2020 10:38:30 GMT

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.

Re: how to Show the last 3 years in report

AllisonKennedy — Tue, 21 Jul 2020 11:05:43 GMT

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-query/

Re: how to Show the last 3 years in report

nandic — Tue, 21 Jul 2020 11:16:13 GMT

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

Re: how to Show the last 3 years in report

AllisonKennedy — Tue, 21 Jul 2020 11:29:14 GMT

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.

Re: how to Show the last 3 years in report

amitchandak — Tue, 21 Jul 2020 11:45:41 GMT

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

Re: how to Show the last 3 years in report

nandic — Tue, 21 Jul 2020 11:52:23 GMT

Thanks AllisonKennedy for this time-saver 🙂

Re: how to Show the last 3 years in report

Anonymous — Fri, 15 Apr 2022 10:45:36 GMT

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 !