Forum Discussion
M-Code for Dynamic Date
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))
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
2 Replies
- mahoneypatMicrosoft Employee
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
- Userpath77Helper II
Thanks Pat. It worked perfect!