M-Code for Dynamic Date

Userpath77 — Mon, 23 Aug 2021 18:08:57 GMT

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))

Re: M-Code for Dynamic Date

mahoneypat — Mon, 23 Aug 2021 23:40:00 GMT

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

Re: M-Code for Dynamic Date

Userpath77 — Tue, 24 Aug 2021 19:14:46 GMT

Thanks Pat. It worked perfect!

topic M-Code for Dynamic Date in DAX Commands and Tips

M-Code for Dynamic Date

Re: M-Code for Dynamic Date

Re: M-Code for Dynamic Date