Forum Discussion
Date is NOT within previous N months
Hi everyone
Im using power query to manipulate some data and i wand to date filer where a date is NOT within the previous N months. There is and option to filter if it IS within previous months but it isnt clear how to request the opposite
Any help would be very much appreciated
Thank you
DataFormat.Error - The dates were for USA (mdy) format. I think your date format is different is dmy, that is why this error as there is no 21 month. However, let's forget this.
Right syntax is
each not Date.IsInPreviousNYears([DATE COLUMN], 2))
Note that Date.IsInPreviousNYears excludes current year. So if current year is 2022, it will exclude only 2021 and 2020. But will include 2019, 2018 and ALSO 2022. And also if future year dates are there, they will be included.
perfect, thank you very much
one last question if i may?
What is the correct syntax to identify dates that are eaxctly 2 years prior to todays date ?
Im assuming to make it dynamic you use the DateTime.LocalNow()) syntax but how can i use the date filters to extract only the date exactly 2 years from today
See this for 2 years before date
Date.AddYears(Date.From(DateTime.FixedLocalNow()),-2)
Hence
each [DATE COLUMN] = Date.AddYears(Date.From(DateTime.FixedLocalNow()),-2)
5 Replies
- Vijay_A_Verma
Most Valuable Professional
Opp is in the next and choose months from drop down
Sample code generated is given below. See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VcvBDcAgDATBXvyOdPERYqjFov82+MH5OdJupv2ggy9p60nrEHzwXnTR4FQxrkLDQBMNFLCKKt3m2dYG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Date.IsInNextNMonths([Date], 2)) in #"Filtered Rows" Firstly your code provided generates the follwoing error DataFormat.Error: We couldn't parse the input provided as a Date value.
Details:
6/21/2022However this doesnt answer my question. I need to know how to filter if the date is NOT in the previous 2 years
for example code supplied is
each Date.IsInPreviousNYears([DATE COLUMN], 2))
I want it look soemthing like this
each Date.Is NOT InPreviousNYears([DATE COLUMN], 2))
- Vijay_A_Verma
DataFormat.Error - The dates were for USA (mdy) format. I think your date format is different is dmy, that is why this error as there is no 21 month. However, let's forget this.
Right syntax is
each not Date.IsInPreviousNYears([DATE COLUMN], 2))
Note that Date.IsInPreviousNYears excludes current year. So if current year is 2022, it will exclude only 2021 and 2020. But will include 2019, 2018 and ALSO 2022. And also if future year dates are there, they will be included.
perfect, thank you very much
one last question if i may?
What is the correct syntax to identify dates that are eaxctly 2 years prior to todays date ?
Im assuming to make it dynamic you use the DateTime.LocalNow()) syntax but how can i use the date filters to extract only the date exactly 2 years from today