Date is NOT within previous N months

[Zedders12345]

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 

[Vijay_A_Verma · Accepted solution]

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.

[Zedders12345 · Accepted solution]

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

[Zedders12345]

Firstly your code provided generates the follwoing error DataFormat.Error: We couldn't parse the input provided as a Date value.
Details:
6/21/2022

However 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 · Accepted solution]

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.

[Zedders12345 · Accepted solution]

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

[Vijay_A_Verma · Accepted solution]

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)

[Vijay_A_Verma]

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"