Need to determine current date, subtract one month and calculate end of month in M

[Shelley]

Hello M Query experts, I'm looking for a little help please. I need to filter a column, based on the month end date of the prior month MINUS one year. So, for example, if today is Nov 14, 2019, I want to filter on the date Oct 31,2018. I tried the following, but am getting the error below:

= Table.SelectRows(#"Filtered Rows", each [Contract End Date] >=
(Date.AddYears(Date.EndOfMonth(Date.AddMonths(DateTime.LocalNow()), -1))), -1)

Expression.Error: 3 arguments were passed to a function which expects 2.
Details:
Pattern=
Arguments=[List]

I keep moving around the () and the comma, but still receive errors. What am I missing? Thanks for the help!

@Anonymous, @v-chuncz-msft

@KHorseman 

[v-juanli-msft]

Hi @Shelley 

It is easy with DAX in data model.

Create a calculated column

filter date =
DATE ( YEAR ( EOMONTH ( TODAY (), -1 ) ) - 1, MONTH ( EOMONTH ( TODAY (), -1 ) ), DAY ( EOMONTH ( TODAY (), -1 ) ) )

For Solution in Power Query, KHorseman has provide a useful solution, please check it.

If you have any problem, please feel free to let me know.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@Shelley,

What @KHorseman said. I just want to add that the right side of the argument will return DateTime format so you might need to encapsulate it within Date.From function to convert it into Date format for comparision, assuming your Contract End Date is in Date format. 

[KHorseman · Accepted solution]

I think you've just got some parentheses in the wrong places. I haven't tested this in a real query yet, but I think what you want is:

Table.SelectRows(#"Filtered Rows", each [Contract End Date] >=
(Date.AddYears(Date.EndOfMonth(Date.AddMonths(DateTime.LocalNow(), -1)), -1)))

[GrantC101]

This looks like something I need help with,

I am looking for something similar for Power Querry Editor but for everying before the previous month.

I have tried to make changes to this formula but being a novice I cant see to get it to work