M-Code for Dynamic Date

[Userpath77]

Hello All

I need to modify this M-Code so that it is only keeping data for the last 5 years beginning on or after 1 Oct of that 5th year. As an example, right now in 2021 I would want data all the way back to 1 Oct 16. However, I need this to be dynamic so that it always just calculates 5 years back and to 1 Oct of the 5th year----no matter what year we are currently in. Another example: In 2023, I'll want it to look at data >= 1 Oct of 2018.

When using Power Query it wants a "year" for the 1 Oct date. How do I modify this so that it does not require a year on the 1 Oct date?

= Table.SelectRows(#"Changed Type", each Date.IsInPreviousNYears([Date Arrived Personnel Office], 5) and [Date Arrived Personnel Office] >= #date(2016, 10, 1))

[mahoneypat · Accepted solution]

Please try using this filter step instead

= Table.SelectRows(#"Changed Type", each [Date Arrived Personnel Office] >= #date(Date.Year(DateTime.LocalNow())-5, 10, 1))

Pat

[Userpath77]

Thanks Pat. It worked perfect!