Power Query Lastdate & 1 Year Back Filter

[Blackworms]

Hi everyone,

I have a set of data which includes my Sales Year/Month/Day. My 2017 or current data is not daily and can vary from today's date. For example, today is 23rd of May, however the last data date is 20th of May for 2017.  My 2016 data however, includes all the year till 31st of December, 2016.

My intention is, identifying the last data date on the date column, which is 05.20.2017; then filtering only the previous year's data from 01.01.2016 till 05.20.2016. So it will basically exclude the dates from 01.01.2016 till one year back of last data date., and do not touch the current year's data at all.

I applied between filter to my column manually, and that covers my needs; however, I will need to adjust the #date(2016. 5. 20) line everytime I recieve my new data since the date is static, not dynamic. Is there any way to adjust the below formula like I desire?

= Table.SelectRows(#"Changed Type1", each [Transaction YMD Desc] >= #date(2016, 1, 1) and [Transaction YMD Desc] <= #date(2016, 5, 20) or [Transaction YMD Desc] >= #date(2017, 1, 1) and [Transaction YMD Desc] <= #date(2017, 12, 31))

 My data is like below:

Transaction YMD Desc	Transaction Year	Transaction Month	Transaction Month #	Transaction Day
20.05.2017	2017	May	5	Fri
20.05.2017	2017	May	5	Fri
20.05.2017	2017	May	5	Fri
20.05.2017	2017	May	5	Fri
20.05.2017	2017	May	5	Fri
20.05.2017	2017	May	5	Fri
20.05.2017	2017	May	5	Fri
20.05.2017	2017	May	5	Fri
20.05.2017	2017	May	5	Fri
20.05.2017	2017	May	5	Fri
20.05.2017	2017	May	5	Fri
20.05.2017	2017	May	5	Fri
20.05.2017	2017	May	5	Fri
20.05.2017	2017	May	5	Fri
20.05.2017	2017	May	5	Fri
20.05.2017	2017	May	5	Fri
20.05.2017	2017	May	5	Fri
20.05.2017	2017	May	5	Fri
20.05.2017	2017	May	5	Fri
19.05.2017	2017	May	5	Thu
19.05.2017	2017	May	5	Thu
19.05.2017	2017	May	5	Thu
19.05.2017	2017	May	5	Thu
19.05.2017	2017	May	5	Thu
19.05.2017	2017	May	5	Thu
19.05.2017	2017	May	5	Thu

Thanks,

[MarcelBeug]

You can get the last date with:

List.Max(#"Changed Type1"[Transaction YMD Desc]])

In your example code you select data from 01.01.2016 tru 20.05.2016 and from 01.01,2017 thru 31.12.2017

I would expect a selection of anything > 20.05.2016.

You can get 1 year back with:

Date.AddYears(List.Max(#"Changed Type1"[Transaction YMD Desc]]),-1)