Filter by Same Month Last Year in Power Query

[Anonymous]

Hello,

I'm having trouble filtering a table in the power query editor to keep only the data from the same current month last year. 

For example: It is currently August 2020 and I want to only show the values from August 2019.

I'd like the filter to be dynamic so it updates every month. Next month it would show September 2019 etc.

[mahoneypat · Accepted solution]

You can get that with the following approach

1.  Add another query called datefilter.  Add a blank query and put this formula as the source line in the Formula bar

= Date.StartOfMonth(Date.From(Date.AddMonths(DateTime.LocalNow(), -12)))

2. In your original query, add any date filter to your date column (you will replace it so pick any value).

3.  Look at the new filter step in the Formula Bar and replace the hard coded date with "datefilter'

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[v-xicai · Accepted solution]

Hi @Anonymous ,

You may go to Query Editor, create a new blank query, rename it as "SameMonthLastYear", copy and paste the codes below into the step "Source", 

= Date.AddDays (Date.From (DateTime.LocalNow ()) , -366)

Then go to your fact table query, change the data type of DateKey column to "Date", set it date filter as "After" a date value , such as "4/23/2013".

Finally, change the date related codes "4/23/2013" with query name "SameMonthLastYear" like picture below.

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Use the formula date.isinpreviousmonth for last month and then date.isincurrentmonth for current month. 

that should do it 

[mahoneypat · Accepted solution]

You can get that with the following approach

1.  Add another query called datefilter.  Add a blank query and put this formula as the source line in the Formula bar

= Date.StartOfMonth(Date.From(Date.AddMonths(DateTime.LocalNow(), -12)))

2. In your original query, add any date filter to your date column (you will replace it so pick any value).

3.  Look at the new filter step in the Formula Bar and replace the hard coded date with "datefilter'

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat