Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter by Same Month Last Year in Power Query

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.

Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    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's avatar
    v-xicai
    6 years ago

    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.

     

3 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

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

    that should do it 

  • v-xicai's avatar
    v-xicai
    Icon for Community Support rankCommunity Support
    6 years ago

    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.