Filter for BEFORE today last year ...

[adambc]

I want to turn this ...

= Table.SelectRows(#"Changed Type", each [LastCompletedDate] < DateTime.LocalNow() or [LastCompletedDate] = null)

... to select rows < TODAY LAST YEAR or = null ...

(ie DateTime.LocalLastYear()!!!)

I've found a few ideas in this forum (and others) but can't seem to get any of them to work!

Can anyone help please?

Many thanks ...

 

[adambc]

I've worked it out!!!

= Table.SelectRows(#"Filtered Rows", each [LastCompletedDate] < Date.AddDays(DateTime.LocalNow(),-365) or [LastCompletedDate] = null)

... unless I've missed something?

 

[maabadi]

For Leap Years you have Problems Use this.

= Table.SelectRows(#"Filtered Rows", each [LastCompletedDate] < Date.AddMonths(DateTime.LocalNow(),-12) or [LastCompletedDate] = null)

 

[adambc]

Good point - thanks ...

 

[maabadi]

You're welcome & Thanks for Feedback.

 

[adambc]

@maabadi

Hopefully no-one will object to me asking a very closely linked follow up question here?

It now appears that the REAL requirement (!) is a fixed date cut off, not a fixed period! - if I wanted to amend ...

[LastCompletedDate] < Date.AddMonths(DateTime.LocalNow(),-12)

... to ...

[LastCompletedDate] < {[I]a fixed date that has been entered into a named cell in the Workbook[/I]}

... how would I code that?

I know what the syntax looks like if I use a Power Query date filter ie (for 1/4/2020) ...

[LastCompletedDate] < #datetime(2020, 4, 1, 0, 0, 0)

... but how would I turn eg 1/4/2020 entered into the named cell in the Workbook into the correct syntax ie (2020, 4, 1, 0, 0, 0)?

Thanks ...

 

[maabadi]

Please start new thread And ask there. I don't familiar with power query formulas completely.