Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Blackworms
Frequent Visitor

Power Query Lastdate & 1 Year Back Filter

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 DescTransaction YearTransaction MonthTransaction Month #Transaction Day
20.05.20172017May5Fri
20.05.20172017May5Fri
20.05.20172017May5Fri
20.05.20172017May5Fri
20.05.20172017May5Fri
20.05.20172017May5Fri
20.05.20172017May5Fri
20.05.20172017May5Fri
20.05.20172017May5Fri
20.05.20172017May5Fri
20.05.20172017May5Fri
20.05.20172017May5Fri
20.05.20172017May5Fri
20.05.20172017May5Fri
20.05.20172017May5Fri
20.05.20172017May5Fri
20.05.20172017May5Fri
20.05.20172017May5Fri
20.05.20172017May5Fri
19.05.20172017May5Thu
19.05.20172017May5Thu
19.05.20172017May5Thu
19.05.20172017May5Thu
19.05.20172017May5Thu
19.05.20172017May5Thu
19.05.20172017May5Thu
     

 

Thanks,

1 REPLY 1
MarcelBeug
Community Champion
Community Champion

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)
Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.